Different execution plan on literals/constant VS variables

  • 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

     

    Attachments:
    You must be logged in to view attached files.
  • We can't even properly see your query! How is anyone supposed to know what code is in that function?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

     

     

     

  • 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

  • (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) A socialist is someone who will give you the shirt off *someone else's* back.

  • I just updated all the stats for this able and still the same behavior.

  • 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);

  • 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.

  • 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

  • DesNorton wrote:

    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