January 17, 2018 at 2:22 pm
I would like to share my experience recently, in the hope that it helps someone else or that others can suggest better solution options.
I had a view that returns table-information within 10ms if I had a literal value on the WHERE clause. If I query the view like this it runs in less than 10ms.SELECT * FROM view WHERE UtilAcctId = 12345
However if I query the view like this (using a variable) it takes over 10 seconds.DECLARE @UtilAcctId INT = 12345;
SELECT * FROM view WHERE UtilAcctId = @UtilAcctId
The execution plan for the 1st view showed it using 30K and < 100 rows from the table in question, and the execution plan from the 2nd view showed it using 3Gb and > 750K rows. I checked a lot of posts that suggested I put in optimizer hints like OPTION(OPTIMIZE FOR UNKNOWN) or OPTION(RECOMPILE) .
Other posts suggested that the problem was the statistics on the table, or even a lack of indexes on the table. I tried making indexes that related to the query-logic and I updated the statistics, but it really didn't help the performance.
Then it occurred to me to convert the view into a user-table-function where I could make the parameter the literal value needed for the WHERE clause.-- Create the user-table-function
CREATE FUNCTION [dbo].[ufnView] ( @UtilAcctId INT )
RETURNS @OppTransValue TABLE
( OpportunitySqlId INT
, OpportunitySfdcId VARCHAR(18)
, UtilAcctId INT
, TransValue DECIMAL(15,10)
)
BEGIN
...
INSERT INTO @OppTransValue
<Same logic as previous view except using ... WHERE ua.UtilAcctId=@UtilAcctId>
...
RETURN
END
GO
-- Test It
DECLARE @UtilAcctId INT = 12345; SELECT * FROM [dbo].[ufnView] ( @UtilAcctId)
GO
Now it runs in under 10ms again, even when I removed the indexes and tried it on a copy of the database that didn't have the updated statistics done.
It is also interesting that the WHERE clause is nested 3 levels deep in the query logic and it still worked properly.
Eventually I changed the parameter to be a TYPE that is a table of UtilAcctId(s) (int). Then I can limit the WHERE clause to a list from the table. WHERE UtilAcctId = [parameter-List].UtilAcctId. This works even better. I think the user-table-functions are pre-compiled.
I hope this helps, and let me know if there are better solution options. I have SQL-Server-2014 on a private Windows server on Azure.
January 17, 2018 at 2:39 pm
Table-valued functions are not pre-compiled
The problem is a lack of parameter sniffing. The optimiser can sniff a parameter or literal value, not a variable. It's not due to indexes or statistics.
The usual solutions are option (optimise for... ) hint (not UNKNOWN though, since unknown is the same as a variable), recompile as a last ditch, or moving what uses the variable to a lower scope so that the value can be passed as a parameter and hence sniffed.
Old blog post: https://www.sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2018 at 2:56 pm
GilaMonster - Wednesday, January 17, 2018 2:39 PMTable-valued functions are not pre-compiledThe problem is a lack of parameter sniffing. The optimiser can sniff a parameter or literal value, not a variable. It's not due to indexes or statistics.
The usual solutions are option (optimise for... ) hint (not UNKNOWN though, since unknown is the same as a variable), recompile as a last ditch, or moving what uses the variable to a lower scope so that the value can be passed as a parameter and hence sniffed.Old blog post: https://www.sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
Gail, I appreciate your answer.
Now I have it straight that table-valued-functions are not pre-compiled. I couldn't use OPTIMIZE For XXXX because we have over a million UtilAcctIds.
In your opinion was the user-table-valued-function a reasonable solution? It will be called by 3 other programs for different purposes.
Thanks, Michael Barash
January 17, 2018 at 3:06 pm
Optimise for XX doesn't have to be the exact value that the query will be called for, just one with around the same number of rows.
And if you look at the blog post, your solution is one I mentioned (though in the form of stored procedure, not function).
Multi-statement functions have nasty performance problems, so I don't like them much.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2018 at 10:46 pm
Mathew McGiffen's blog post Avoid “Constants” in T-SQL shows how the use of a local variable results in a scan, and excessive estimations.
January 18, 2018 at 4:18 am
DesNorton - Wednesday, January 17, 2018 10:46 PMMathew McGiffen's blog post Avoid “Constants†in T-SQL shows how the use of a local variable results in a scan, and excessive estimations.
In his example yes, but if you change the select list so that the ordinary index is covering, then you get an index seek, for both values of the variable. You can also force the covering index like so
DECLARE @Blackhole TINYINT, @OpenTask tinyint = 1;
SELECT * --@Blackhole = TaskStatus
FROM #Task WITH (INDEX (IX_Task_TaskStatus))
WHERE TaskStatus = @OpenTask;
and this query has the same plan as the original - an index seek with a key lookup.
In each case, the estimated number of rows is the average number of rows per TaskStatus. You can test this by introducing a third value for TaskStatus into the sample set:
INSERT INTO #Task (UserId,TaskType,Payload,TaskStatus)
SELECT TOP 500000 1,1,'This Shizzle Is Done',1
FROM sys.objects a, sys.objects b, sys.objects c;
INSERT INTO #Task (UserId,TaskType,Payload,TaskStatus)
SELECT TOP 500000 1,1,'This Shizzle Is Done',2
FROM sys.objects a, sys.objects b, sys.objects c;
INSERT INTO #Task (UserId,TaskType,Payload,TaskStatus)
SELECT 1,1,'Do This Shizzle',0;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy