Post reply

Execution plan Help

  • February 19, 2015 at 8:55 am

    #398043

    Hi,

    I am trying to get previous 6 month data and am able to achieve it. Below is may sample query and the reason am inserting into temp variable was, i need to use this data in couple of places in my procedure.

    DECLARE @regdate DATETIME = '2014-01-01'

    ,@datediff INT

    DECLARE @Dates TABLE (

    Month VARCHAR(10)

    ,startdate DATETIME

    ,enddate DATETIME

    );

    SELECT @datediff = DATEDIFF(MONTH, @regdate, GETDATE());

    INSERT INTO @Dates (

    Month

    ,startdate

    ,enddate

    )

    SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month

    ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate

    ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate

    FROM (

    VALUES (1)

    ,(2)

    ,(3)

    ,(4)

    ,(5)

    ,(6)

    ) x(N)

    WHERE N <= @datediff;

    select * from @Dates;

    the output is perfect for my requirement. but is there any better way to improve the execution plan? please advice me. If yes any sample please.

  • February 19, 2015 at 9:16 am

    #1778674

    There is nothing wrong with the plan. What is the issue here? Your query returns 6 rows nearly instantly.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 19, 2015 at 9:22 am

    #1778679

    Hi Sean,

    thanks for reply and if at all if there is any improvement on the query in which part of execution plan will intimate me. Any suggestion. Can i assume that the way i am trying is good to go

  • February 19, 2015 at 9:56 am

    #1778692

    KGJ-Dev (2/19/2015)

    Hi Sean,

    thanks for reply and if at all if there is any improvement on the query in which part of execution plan will intimate me. Any suggestion. Can i assume that the way i am trying is good to go

    Not sure I understand your concern about the query plan. There seems to be quite a bit of extra work in your code but the performance is fine. Why bother with a table variable, just do the select. Also, there really is no need to store the datediff, just put it in the where predicate.

    DECLARE @regdate DATETIME = '2014-01-01'

    SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month

    ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate

    ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate

    FROM (

    VALUES (1)

    ,(2)

    ,(3)

    ,(4)

    ,(5)

    ,(6)

    ) x(N)

    WHERE N <= DATEDIFF(MONTH, @regdate, GETDATE());

    The above really isn't any more efficient but it is a lot less code and eliminates several variables that aren't needed. The original query ran in barely a microsecond so concerning yourself with performance of this is not needed.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 19, 2015 at 10:54 am

    #1778719

    Great and thanks for the confirmation that there is no performance impact on this query.

  • February 19, 2015 at 11:18 pm

    #1778856

    KGJ-Dev (2/19/2015)

    Great and thanks for the confirmation that there is no performance impact on this query.

    Quick thought, if the code is called very frequently then there will be a huge difference in terms of load on the server, the table variable method can easely cause congestion in tempdb.

    😎

  • February 22, 2015 at 3:14 pm

    #1779164

    thanks eric and in that case what i supposed to use? temp table?

  • February 22, 2015 at 11:03 pm

    #1779179

    Go with Sean's method, no need to "materialize" the set and better chances of benefiting from the cache.

    😎

  • February 23, 2015 at 7:53 am

    #1779265

    Hi Erik,

    Here is my confusion, if i use the Sean's suggestion i am in the position to use the same query for two times as i need that output for another purpose. that's why i wanted to have it on temp table or temp variable. as you and sean suggested, if i use that query twice inside procedure, will not it impact the performance?

    Please advice me.

  • February 23, 2015 at 8:04 am

    #1779268

    KGJ-Dev (2/23/2015)

    Hi Erik,

    Here is my confusion, if i use the Sean's suggestion i am in the position to use the same query for two times as i need that output for another purpose. that's why i wanted to have it on temp table or temp variable. as you and sean suggested, if i use that query twice inside procedure, will not it impact the performance?

    Please advice me.

    You have spent far more time worrying it then you are going to benefit. It is 6 rows. If you are concerned about performance the absolutely ONLY way to know for sure is to test it in your system. Try it out with a temp table, table variable and without. See which of the three is faster in your system. Honestly I doubt there is going to be any measurable performance differences. You could probably use this code 50 times in your procedure and it would not have any measurable affect.

    I copied your select statement 200 times in a single batch and it runs in less than 1 second.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 23, 2015 at 8:31 am

    #1779281

    KGJ-Dev (2/23/2015)

    Hi Erik,

    Here is my confusion, if i use the Sean's suggestion i am in the position to use the same query for two times as i need that output for another purpose. that's why i wanted to have it on temp table or temp variable. as you and sean suggested, if i use that query twice inside procedure, will not it impact the performance?

    Please advice me.

    My thought is that the cost of explicitly/implicitly creating a temp table and populating it by far outweighs the cost of running the query twice (or few times), if the system is in a healthy state the subsequent queries will only touch the cache.

    Tempdb is a shared resource across all databases on the server, I've seen tens of thousands active temp tables in tempdb accumulate in matter of seconds, on a busy server, when using this kind of code.

    😎

  • February 23, 2015 at 8:47 am

    #1779290

    Hi Sean,

    thanks for your reply and i tested all three scenarios and i don't see performance difference.

  • February 23, 2015 at 8:52 am

    #1779292

    Thanks Eirik for the reply.

    My thought is that the cost of explicitly/implicitly creating a temp table and populating it by far outweighs the cost of running the query twice (or few times), if the system is in a healthy state the subsequent queries will only touch the cache.

    have question on this statement, if you could see the query, the regdate will be vary based on user registration. It's not static. In that case how the Execution plan will work? will it create different cache for each user as this date is different for most of the users? any thoughts

  • February 23, 2015 at 9:39 am

    #1779315

    KGJ-Dev (2/23/2015)

    Hi Sean,

    thanks for your reply and i tested all three scenarios and i don't see performance difference.

    Exactly. So go with the simplest one which I posted a few days ago. 😉

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply