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.
OPTION
at all for a performance issue, and especially not theFAST
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.