0
\$\begingroup\$

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
Improve this question
\$\endgroup\$
8
  • \$\begingroup\$ Please follow our title guidance: codereview.stackexchange.com/help/how-to-ask \$\endgroup\$
    – BCdotWEB
    Jul 30, 2021 at 19:30
  • \$\begingroup\$ please provide the Category and EcommerceItem models along with EcommerceItemDto. We need to know how the relationships works. Also, did you try to query the table and not the View and see how it performs ? \$\endgroup\$
    – iSR5
    Jul 31, 2021 at 2:33
  • \$\begingroup\$ @iSR5 Done+. My View get the custom Price for the logged-in user. \$\endgroup\$
    – Randy
    Jul 31, 2021 at 13:02
  • \$\begingroup\$ @Randy on items, comment out .Include(x => x.Category) and .OrderBy(i => i.ItemNumber) and in the raw sql add TOP {pageSize} * FROM and also ORDER BY ItemNumber ASC; test it, and let me know the performance difference. \$\endgroup\$
    – iSR5
    Jul 31, 2021 at 15:43
  • \$\begingroup\$ @iSR5 Test results posted \$\endgroup\$
    – Randy
    Jul 31, 2021 at 16:46

2 Answers 2

Reset to default
2
\$\begingroup\$

Just an idea... I'd consider turning this query into a stored-procedure taking the CustomerNumber as a parameter and working directly with the data without the view. I'm pretty sure it's slow becuase it cannot take the advantage of CustomerNumber being probably indexed and it has to perform a full-scan of the view.

Improve this answer
\$\endgroup\$
1
  • 1
    \$\begingroup\$ You could add indexes for that Stored Procedures.. and you get a +1 just to mention it (just because everybody is still so ORM it just hurts me) \$\endgroup\$
    – riffnl
    Jan 4, 2022 at 1:53
0
\$\begingroup\$

I followed all the recommendations from RedGate's Sql Prompt (Modified my view to the one recommended change):

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 
    AND ecItems.display_on_ecommerce = 1
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
GO

Re-Ran the the tuning advisor and applied those Index creations. Then I switched to System.Text.Json from Newtonsoft.Json as my serializer in the Asp.net Core project. Re-Ran my Bench-Mark test:

Method IterationCount Mean Error StdDev Min Max Ratio RatioSD
GetItems 50 225.5 ms 4.89 ms 14.26 ms 134.77 ms 250.5 ms 1.00 0.00
GetItemsWithChangesWithoutCategories 50 225.4 ms 6.86 ms 20.23 ms 98.31 ms 254.2 ms 1.01 0.13
GetItems 100 434.6 ms 7.65 ms 11.21 ms 415.40 ms 460.2 ms 1.00 0.00
GetItemsWithChangesWithoutCategories 100 442.1 ms 8.78 ms 16.50 ms 418.91 ms 478.6 ms 1.02 0.05

I guess this is optimized as it gets, unless I switch to Dapper.

UPDATE

To be fair to @iSR5, I discovered I had a typo in my username I was using to get the Authorization Token. I corrected it and re-ran the test:

Method IterationCount Mean Error StdDev Min Max Ratio
GetItems 50 6.260 s 0.1213 s 0.1490 s 6.012 s 6.598 s 1.00
GetItemsWithChangesWithoutCategories 50 2.036 s 0.0407 s 0.0543 s 1.948 s 2.162 s 0.32
GetItems 100 12.521 s 0.2439 s 0.2281 s 12.048 s 12.932 s 1.00
GetItemsWithChangesWithoutCategories 100 4.135 s 0.0790 s 0.0739 s 4.031 s 4.242 s 0.33
Improve this answer
\$\endgroup\$
1
  • 2
    \$\begingroup\$ I don't think that dapper will solve the issue. You need to re-evaluate the view outside your code (using SSMS or any DBMS IDE), then apply the same query on it while Execution Plan is on. then review the execution plan, see what you can improve. And about the updated benchmark (with corrected username), it shows that it's even getting slower than before. (probably because of getting more data since you've corrected the username). \$\endgroup\$
    – iSR5
    Aug 2, 2021 at 1:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.