I have a Item Service that is Called from an Asp.Net Core API Controller. The query gets all items in a particular category for display on an eCommerce web site (reactjs). The controller only returns json:
public async Task<IEnumerable<EcommerceItemDto>> GetAllItemsAsync(string customerNumber, string category = "All", int page = 0, int pageSize = 9999)
{
IQueryable<Category> categories;
if (category == "All")
{
categories = _context.Categories
.Include(c => c.Children)
.Include(p => p.Parent)
.AsNoTrackingWithIdentityResolution();
}
else
{
categories = _context.Categories
.Where(n => n.Name == category)
.Include(c => c.Children)
.Include(p => p.Parent)
.AsNoTrackingWithIdentityResolution();
}
var items = await _context.EcommerceItems
.FromSqlInterpolated($"SELECT * FROM [cp].[GetEcommerceItemsView] WHERE [CustomerNumber] = {customerNumber}")
.Include(x => x.Category)
.Include(i => i.Images.OrderByDescending(d => d.Default))
.OrderBy(i => i.ItemNumber)
.Where(c => categories.Any(x => x.Children.Contains(c.Category)) || categories.Contains(c.Category))
.Skip(page * pageSize)
.Take(pageSize)
.AsNoTracking()
.ToListAsync();
var dto = _mapper.Map<IEnumerable<EcommerceItemDto>>(items);
return dto;
}
I can show my Controller Action
, [cp].[GetEcommerceItemsView]
View, EcommerceItem
Class, EcommerceItemDto
Class, Category
Class and Images
Class if anyone thinks that will help. I have run Tuning
on my Sql Database and applied the recommendations. I have bench-marked this using _mapper.Map
as well as .ProjectTo
and mapper.Map is faster by a hair. In my test Database, I only have about 65 Items with Images. But this request is taking about 1000 to 1500 ms.
UPDATE
As Per requested by @iSR5...
public class EcommerceItem : INotifyPropertyChanged
{
[Key]
public string ItemNumber { get; set; }
public string ItemDescription { get; set; }
[Column(TypeName = "text")]
public string ExtendedDesc { get; set; }
public bool Featured { get; set; }
public string CategoryName { get; set; }
public Category Category { get; set; }
[Column(TypeName = "numeric(19, 5)")]
public decimal Price { get; set; }
[Column(TypeName = "numeric(19, 5)")]
public decimal QtyOnHand { get; set; }
public bool? Metadata1Active { get; set; }
[StringLength(50)]
public string Metadata1Name { get; set; }
[StringLength(50)]
public string Metadata1 { get; set; }
... // I have Twenty Matadata Groups
public bool? Metadata20Active { get; set; }
[StringLength(50)]
public string Metadata20Name { get; set; }
[StringLength(50)]
public string Metadata20 { get; set; }
public ICollection<EcommerceItemImages> Images { get; set; }
public event PropertyChangedEventHandler PropertyChanged;
[NotifyPropertyChangedInvocator]
protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
{
PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
}
}
My EcommerceItemDto:
public class EcommerceItemDto
{
[Key]
public string ItemNumber { get; set; }
public string ItemDescription { get; set; }
[Column(TypeName = "text")]
public string ExtendedDesc { get; set; }
public bool Featured { get; set; }
public string CategoryName { get; set; }
[Column(TypeName = "numeric(19, 5)")]
public decimal Price { get; set; }
[Column(TypeName = "numeric(19, 5)")]
public decimal QtyOnHand { get; set; }
public bool? Metadata1Active { get; set; }
[StringLength(50)]
public string Metadata1Name { get; set; }
[StringLength(50)]
public string Metadata1 { get; set; }
... // Same as EcommerceItem I have twenty Metadata groups
public bool? Metadata20Active { get; set; }
[StringLength(50)]
public string Metadata20Name { get; set; }
[StringLength(50)]
public string Metadata20 { get; set; }
public ICollection<EcommerceItemImagesDto> Images { get; set; }
}
My Category Model:
[Table("bma_ec_categories")]
public class Category : INotifyPropertyChanged
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Column("category_id")]
public int CategoryId { get; set; }
[Column("parent_category_id")]
public int? ParentId { get; set; }
[Required]
[Column("category_name")]
[StringLength(50)]
public string Name { get; set; }
public Category Parent { get; set; }
public ICollection<Category> Children { get; set; }
[Column("title")]
[StringLength(150)]
public string Title { get; set; }
[Column("description")]
[StringLength(250)]
public string Description { get; set; }
[Column("keywords")]
[StringLength(250)]
public string Keywords { get; set; }
[Column("page_content", TypeName = "text")]
public string PageContent { get; set; }
[Column("banner_group_id")]
public int? BannerGroupId { get; set; }
[Column("inactive")]
public byte? Inactive { get; set; }
[Column("issitecategory")]
public byte Issitecategory { get; set; }
[Column("metadata1_active")]
public byte Metadata1Active { get; set; }
[Column("metadata1_name")]
[StringLength(50)]
public string Metadata1Name { get; set; }
...
[Column("metadata20_active")]
public byte? Metadata20Active { get; set; }
[Column("metadata20_name")]
[StringLength(50)]
public string Metadata20Name { get; set; }
[Column("sort_order")]
public int? SortOrder { get; set; }
public ICollection<EcommerceItem> Items { get; set; }
public event PropertyChangedEventHandler PropertyChanged;
[NotifyPropertyChangedInvocator]
protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
{
PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
}
}
Just to be complete, my View
, it gets the calculated Price for the logged in user and flattens the category and item Metadata:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [cp].[GetEcommerceItemsView]
AS
SELECT
RTRIM(LTRIM(IV.ITEMNMBR)) AS ItemNumber
,RTRIM(LTRIM(IM.ITEMDESC)) AS ItemDescription
,ecItems.[extended_desc] AS ExtendedDesc
,CAST(ecItems.Featured AS BIT) AS Featured
,cat.category_name AS CategoryName
,CASE IM.PRICMTHD
WHEN 1 THEN IV.UOMPRICE
WHEN 2 THEN IV.UOMPRICE * IC.LISTPRCE / 100
WHEN 3 THEN (IM.CURRCOST) * (1 + (IV.UOMPRICE / 100))
WHEN 4 THEN (IM.STNDCOST) * (1 + (IV.UOMPRICE / 100))
WHEN 5 THEN (IM.CURRCOST) / (1 - (IV.UOMPRICE / 100))
WHEN 6 THEN (IM.STNDCOST) / (1 - (IV.UOMPRICE / 100))
ELSE 0
END AS Price
,IQ.QTYONHND AS QtyOnHand
,CAST(cat.[metadata1_active] AS BIT) AS [Metadata1Active]
,cat.[metadata1_name] AS [Metadata1Name]
,ecItems.[metadata1] AS [Metadata1]
...
,CAST(cat.[metadata20_active] AS BIT) AS [Metadata20Active]
,cat.[metadata20_name] AS [Metadata20Name]
,ecItems.[Metadata20] AS [Metadata20]
,C.CUSTNMBR AS CustomerNumber
FROM dbo.RM00101 AS C
LEFT OUTER JOIN dbo.IV00108 AS IV
ON C.PRCLEVEL = IV.PRCLEVEL
LEFT OUTER JOIN dbo.IV00101 AS IM
ON IM.ITEMNMBR = IV.ITEMNMBR
LEFT OUTER JOIN dbo.IV00102 AS IQ
ON IQ.ITEMNMBR = IV.ITEMNMBR
AND IQ.RCRDTYPE = 1
LEFT OUTER JOIN dbo.IV00105 AS IC
ON IC.ITEMNMBR = IV.ITEMNMBR
AND IV.CURNCYID = IC.CURNCYID
LEFT OUTER JOIN dbo.bma_ec_items AS ecItems
ON ecItems.ITEMNMBR = IV.ITEMNMBR
LEFT OUTER JOIN dbo.bma_ec_item_category AS icat
ON icat.ITEMNMBR = IV.ITEMNMBR
LEFT OUTER JOIN dbo.bma_ec_categories AS cat
ON cat.category_id = icat.category_id
WHERE (IM.ITEMNMBR IN (SELECT
ITEMNMBR
FROM dbo.bma_ec_items
WHERE (display_on_ecommerce = 1))
)
GO
Special Note: I am using Newtonsoft Json Serializer in my controller. I know There are faster Serializers out there.
UPDATE 2
As requested, Test Results follows:
BenchmarkDotNet=v0.13.0, OS=Windows 10.0.19043.1110 (21H1/May2021Update) Intel Core i7-7700 CPU 3.60GHz (Kaby Lake), 1 CPU, 8 logical and 4 physical cores .NET SDK=5.0.302 [Host] : .NET 5.0.8 (5.0.821.31504), X64 RyuJIT [AttachedDebugger] DefaultJob : .NET 5.0.8 (5.0.821.31504), X64 RyuJIT
Method | IterationCount | Mean | Error | StdDev | Min | Max | Ratio | RatioSD |
---|---|---|---|---|---|---|---|---|
GetItems | 50 | 228.2 ms | 8.15 ms | 23.65 ms | 119.7 ms | 265.6 ms | 1.00 | 0.00 |
GetItemsWithChanges | 50 | 231.0 ms | 4.60 ms | 10.66 ms | 211.3 ms | 253.1 ms | 1.04 | 0.22 |
GetItems | 100 | 455.8 ms | 9.04 ms | 24.29 ms | 414.9 ms | 522.0 ms | 1.00 | 0.00 |
GetItemsWithChanges | 100 | 453.8 ms | 9.00 ms | 20.32 ms | 421.0 ms | 502.2 ms | 1.00 | 0.08 |
UPDATE 3
Test without categories:
Method | IterationCount | Mean | Error | StdDev | Min | Max | Ratio | RatioSD |
---|---|---|---|---|---|---|---|---|
GetItems | 50 | 231.1 ms | 6.06 ms | 17.88 ms | 140.4 ms | 259.7 ms | 1.00 | 0.00 |
GetItemsWithChangesWithoutCategories | 50 | 230.9 ms | 4.61 ms | 10.69 ms | 214.0 ms | 255.4 ms | 1.01 | 0.15 |
GetItems | 100 | 450.2 ms | 8.99 ms | 22.40 ms | 416.6 ms | 510.1 ms | 1.00 | 0.00 |
GetItemsWithChangesWithoutCategories | 100 | 452.5 ms | 9.01 ms | 23.10 ms | 417.3 ms | 515.9 ms | 1.01 | 0.08 |
Category
andEcommerceItem
models along withEcommerceItemDto
. We need to know how the relationships works. Also, did you try to query the table and not theView
and see how it performs ? \$\endgroup\$View
get the custom Price for the logged-in user. \$\endgroup\$items
, comment out.Include(x => x.Category)
and.OrderBy(i => i.ItemNumber)
and in the raw sql addTOP {pageSize} * FROM
and alsoORDER BY ItemNumber ASC;
test it, and let me know the performance difference. \$\endgroup\$