Execution plan Help
-
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.
-
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/ -
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
-
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/ -
Great and thanks for the confirmation that there is no performance impact on this query.
-
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.
😎
-
thanks eric and in that case what i supposed to use? temp table?
-
Go with Sean's method, no need to "materialize" the set and better chances of benefiting from the cache.
😎
-
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.
-
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/ -
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.
😎
-
Hi Sean,
thanks for your reply and i tested all three scenarios and i don't see performance difference.
-
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
-
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