-1

We have this dilemma -- below query runs against one of our VIEWs (which is made up of VIEWs and other TABLES) and takes forever to run (10+ minutes!):

SELECT STORE_CD, ITEM_CD, DATE
  FROM [dbo].[V_DROPSHIP_SYM_REPLENISHMENT]
 WHERE DATE = GETDATE()

However, if I change the query to this:

SELECT STORE_CD, ITEM_CD, DATE
  FROM [dbo].[V_DROPSHIP_SYM_REPLENISHMENT]
 WHERE DATE = GETDATE()
OPTION (FORCE ORDER, FAST 20)

It runs immediately and returns 3,754 rows! The problem is, most of our applications use LINQ, like this:

var results = (
    from s in db.V_DROPSHIP_SYM_REPLENISHMENT
        .Where(x => x.DATE == somePassedInDate)
    select new
    {
        s.STORE_CD,
        s.ITEM_CD,
        s.DATE
    }).ToList();

I don't believe there's any way to add "OPTION" to a LINQ statement and apparently you can't use "OPTION" in VIEWs, either, so are we just out of luck? This is running on SQL2019.

5
  • 4
    You really shouldn't be using OPTION at all for a performance issue, and especially not the FAST option which ironically can result in less than optimal execution plans for the entire dataset (it's somewhat coincidental / lucky it works for this particular example). Rather you should look at the execution plan to troubleshoot the performance bottleneck. You can upload the actual plan to Paste The Plan and link it in your post if you want further assistance with performance tuning.
    – J.D.
    Aug 4 at 21:49
  • Adding that queries involving nested views are often a challenge to optimize.
    – Dan Guzman
    Aug 5 at 1:54
  • 2
  • 1
    Note that there's a preview feature (Azure SQL DB and SQL Server 2022) to add hints via the Query Store: docs.microsoft.com/en-us/sql/relational-databases/performance/… Aug 6 at 0:07
  • 1
    ”The problem is, most of our applications use LINQ,” ayup.
    – Hannah Vernon
    Aug 6 at 17:02

0

Browse other questions tagged or ask your own question.