December 19, 2023 at 4:51 pm
Can you please explain why am i getting different plans here? When I actually put the actual literals, I get better plan. But when I put the variable in the where clause, I'm getting table scan. Even though predicate column is an int and I'm also converting variables to an int.
In my query, ididat is an int defined in the table.
And if you can advise how can I get my query to use indexes, that would be great.
Best Regards,
SQL Surfer
December 19, 2023 at 5:11 pm
We can't even properly see your query! How is anyone supposed to know what code is in that function?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 19, 2023 at 5:19 pm
IDIDAT is declared as INT in the table
DECLARE @StartDate DATE = '9/5/2023'
DECLARE @EndDate DATE = '9/5/2023'
DECLARE
@IntStartDate INT = dbo.fnDate2int(@StartDate)
,@IntEndDate INT = dbo.fnDate2int(@EndDate)
query 1
SELECT
FROM table il
WHERE
il.IDIDAT between @IntStartDate AND @IntStartDate
query 2
SELECT
FROM table il
WHERE
il.IDIDAT between 20230905 AND 20230905
query 2 uses proper indexes
query 1 does the table scan.
Please let me know if this works of if you need further details.
I need to be able to use the variables and not do the table scan.
December 19, 2023 at 5:23 pm
code inside the function
CREATE FUNCTION [dbo].[fnDate2int] ( @Date DATE )
RETURNS INT
AS
BEGIN
DECLARE @IntDate INT = CAST(CONVERT(VARCHAR(8),@Date,112) AS INT)
RETURN @IntDate
December 19, 2023 at 5:27 pm
(1) Make sure any relevant statistics on the table are up to date.
(2) Easiest might be to add:
OPTION(RECOMPILE)
to the end of the SQL and see if it helps; iirc, SQL will then use the actual values from the variables rather than its normal "guestimate" for generating a query plan.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 19, 2023 at 5:50 pm
I just updated all the stats for this able and still the same behavior.
December 19, 2023 at 5:54 pm
If you are sure you always want it to seek the values you can give the query a hint:
SELECT *
INTO #SROISDPL
FROM IBS.IBS.SROISDPL_X il WITH (FORCESEEK, INDEX(IX_SROISDPL_X_IDIDAT))
WHERE il.IDIDAT BETWEEN @IntStartDate AND @IntEndDate
;
but this would always perform a seek even if it is not the most efficient way of doing the query.
Other things to try:
OPTION (RECOMPILE); (as Scott suggested)
or
OPTION (OPTIMIZE FOR UNKNOWN);
December 20, 2023 at 6:48 am
As far as I understand it, SQL compiles the plan before it executes.
When you use a variable, SQL compiles a plan for an unknown value.
When you use a fixed value, SQL compiles a plan for that exact value.
December 20, 2023 at 8:11 am
Statistics !
Knowing the actual value you are looking for versus "well ... there will be a value overhere, but at this time I cannot say which" makes a ton of a difference for the engine because it wants to evaluate the column's statistics to figure out a good plan of best plan to complete your query.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 20, 2023 at 9:15 am
it's called parameter sniffing https://blog.sqlauthority.com/2019/12/19/sql-server-parameter-sniffing-simplest-example/
December 20, 2023 at 1:26 pm
As far as I understand it, SQL compiles the plan before it executes.
When you use a variable, SQL compiles a plan for an unknown value.
When you use a fixed value, SQL compiles a plan for that exact value.
It's this.
There are three ways you can pass things to a query. You can hard code values, in which case the optimizer uses those specific values against the statistics, specifically the histogram, to arrive at row estimates which drive the decisions of the optimizer. You can use a local variable, in which case the optimizer has no idea what the values are, so it uses an estimated value based on an average of the values in the statistics, not the histogram, to arrive at row estimates which drive the decisions of the optimizer. You can use parameters within a stored procedure or prepared statement, in which case the optimizer knows the values and will use them, similar to hard coded values, to look up data in the histogram to arrive at row estimates, yada yada yada. Finally, when a statement level recompile occurs, the optimizer can then see the values in local variables and will treat them the same way as hard-coded or parameterized values to yada yada yada.
None of these processes or choices is wrong. They're all different. So tuning frequently comes down to deciding which thing is best in a given situation. Further, even if it's best, is it best all the time? You have to decide if, for example, a generic average value, such as from a local variable, is better, or if specific values are better. Are specific values better always, or are there a more limited set of specific values? In that case, query hints such as OPTIMIZE FOR a value or FOR UNKNOWN may solve the problems you see. Or, a RECOMPILE hint (on the query please, not the procedure, usually, YMMV, some assembly required) may get you better plans. Finally, introducing Query Store, you can force plans to go in a direction. Oh, and there are plan guides, but they're a ginormous pain the bottom and frequently don't work well.
TLDR: It depends.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply