August 7, 2015 at 7:53 am
ChrisM@Work (8/7/2015)
Sean Lange (8/7/2015)
j-1064772 (8/7/2015)
[font="Comic Sans MS"]One simple improvement would be to avoid DDL in the middle of DML statements.Move to the top, before any DML the following:[/font]
[font="Courier New"]CREATE TABLE #results(...[/font]
[font="Comic Sans MS"]No, this is not the magic bullet, just something to be avoided. I seem to recall from way back when that defining a new table in the middle of select's caused overhead.[/font]
Huh??? I don't think there is any truth to this. If there is I would love to see some documentation or article explaining this. Not saying it isn't correct but without something to back it up I disagree.
Well, maybe if you gave the table a dirty name you might end up with dirty data?
LOL 😀
_______________________________________________________________
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/
August 7, 2015 at 7:57 am
[font="Comic Sans MS"]OK, I though it was something that was well known and that someone would clarify. Given your level of experience I have to take your comment seriously and I will have to do my own research - sort of put my money where my mouth is. My unsubstantiated statement does not belong in this forum. I'll get back to you all on this.[/font]
[font="Comic Sans MS"]OK, here is what I found: possibility of recompile[/font]
http://johnnycoder.com/blog/2008/02/05/dont-mix-ddl-and-dml/
Mixing DDL and DML Operations Can Cause a Recompile.
What does this mean? DDL (Data Definition Language) and DML (Data Manipulation Language) almost always exists in our stored procedures. Examples of DDL are our DECLARE variable and CREATE TABLE statements. DML, on the other hand, includes our SET, SELECT and INSERT to name a few. A key cause of stored procedure recompiles is the interleaving of DML and DDL statements — especially DDL following DML statements. In other words, according to Microsoft best practices, place all of your DDL statements at the top of your stored procedures and then do your actual query work. Now, are recompiles always going to happen if you have interleaved DDL and DML? Nope, not in all case, but you should do everything possible to prevent recompiles (even if it is super easy.)
August 7, 2015 at 8:08 am
j-1064772 (8/7/2015)
[font="Comic Sans MS"]OK, I though it was something that was well known and that someone would clarify. Given your level of experience I have to take your comment seriously and I will have to do my own research - sort of put my money where my mouth is. My unsubstantiated statement does not belong in this forum. I'll get back to you all on this.[/font][font="Comic Sans MS"]OK, here is what I found: possibility of recompile[/font]
http://johnnycoder.com/blog/2008/02/05/dont-mix-ddl-and-dml/
Mixing DDL and DML Operations Can Cause a Recompile.
What does this mean? DDL (Data Definition Language) and DML (Data Manipulation Language) almost always exists in our stored procedures. Examples of DDL are our DECLARE variable and CREATE TABLE statements. DML, on the other hand, includes our SET, SELECT and INSERT to name a few. A key cause of stored procedure recompiles is the interleaving of DML and DDL statements — especially DDL following DML statements. In other words, according to Microsoft best practices, place all of your DDL statements at the top of your stored procedures and then do your actual query work. Now, are recompiles always going to happen if you have interleaved DDL and DML? Nope, not in all case, but you should do everything possible to prevent recompiles (even if it is super easy.)
I would disagree that your statement doesn't belong here. On the contrary, I feel that any statement (as long as it stays professional) belongs here. That is one the best things about this site.
I have never heard of this practice of putting all your ddl at the beginning to avoid recompiles. I am not sure I believe it just yet but I will certainly do some digging.
Also, don't get confused about the difference of experience and points on this site. I do have quite a few points but that just means I have posted more than many other people. It by no means makes me any kind of authority on this or any other topic.
_______________________________________________________________
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/
August 7, 2015 at 8:13 am
j-1064772 (8/7/2015)
[font="Comic Sans MS"]One simple improvement would be to avoid DDL in the middle of DML statements.Move to the top, before any DML the following:[/font]
[font="Courier New"]CREATE TABLE #results(...[/font]
[font="Comic Sans MS"]No, this is not the magic bullet, just something to be avoided. I seem to recall from way back when that defining a new table in the middle of select's caused overhead.[/font]
Mixing DML and DDL can lead to recompiles. For example, you added an index or a PK to a temp table, that may cause a statement level recompile. That can cause performance issues in some cases.
With everything else involved in this particular query, I'm not sure I'd sweat that one just yet.
"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
August 7, 2015 at 8:37 am
Grant Fritchey (8/7/2015)
j-1064772 (8/7/2015)
[font="Comic Sans MS"]One simple improvement would be to avoid DDL in the middle of DML statements.Move to the top, before any DML the following:[/font]
[font="Courier New"]CREATE TABLE #results(...[/font]
[font="Comic Sans MS"]No, this is not the magic bullet, just something to be avoided. I seem to recall from way back when that defining a new table in the middle of select's caused overhead.[/font]
Mixing DML and DDL can lead to recompiles. For example, you added an index or a PK to a temp table, that may cause a statement level recompile. That can cause performance issues in some cases.
With everything else involved in this particular query, I'm not sure I'd sweat that one just yet.
Nice reference from MS here. It's a long while since I've read it, and I came away with the conclusion that recompiles are quite likely for stored procedures using #temp tables. On the other hand although recompiles are cpu-intensive, as you point out Grant there are far more significant issues to deal with here.
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
August 7, 2015 at 8:38 am
Looking at the internals of the stored procedure and considering the table is 21 million rows, I'm impressed that it would run within 5 seconds. Unless you completely overhawl the coding, I believe that a target of 1 second is not realistic.
One observation that you're using a table variable to hold a large temporary result. The problem with this is that statistics are not maintained on table variables.
declare @LipperId table
If this procedure is called hundreds of times a day or more, and the results tend to be static between each call, and it's not critical that the results be fresh, then perhaps you could consider implementing a cache table.
Basically I'm suggesting that the result of this procedure be inserted into a cache table keyed on Cache_DateTime + Row_ID. When the procedure runs, it first queries MAX( Cache_DateTime ). If last runtime is less than let's say 5 minutes, then query the cached resultset WHERE Cache_DateTime = MAX( Cache_DateTime ). If the last runtime is more than 5 minutes, then query a new resultset in the regular way, insert that into the cache table, and then return the result.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 7, 2015 at 8:43 am
[font="Arial"]On the other hand although recompiles are cpu-intensive, as you point out Grant there are far more significant issues to deal with here.
[/font]
[font="Comic Sans MS"]Yes - I stated right off the bat it was no magic bullet. My suggestion though required no analysis, it is very easy to implement. It also applies to any stored proc, not just the ones that desperately seek optimization.
Thanks for the link to MS.
Regards[/font]
August 7, 2015 at 8:46 am
ChrisM@Work (8/7/2015)
Grant Fritchey (8/7/2015)
j-1064772 (8/7/2015)
[font="Comic Sans MS"]One simple improvement would be to avoid DDL in the middle of DML statements.Move to the top, before any DML the following:[/font]
[font="Courier New"]CREATE TABLE #results(...[/font]
[font="Comic Sans MS"]No, this is not the magic bullet, just something to be avoided. I seem to recall from way back when that defining a new table in the middle of select's caused overhead.[/font]
Mixing DML and DDL can lead to recompiles. For example, you added an index or a PK to a temp table, that may cause a statement level recompile. That can cause performance issues in some cases.
With everything else involved in this particular query, I'm not sure I'd sweat that one just yet.
Nice reference from MS here. It's a long while since I've read it, and I came away with the conclusion that recompiles are quite likely for stored procedures using #temp tables. On the other hand although recompiles are cpu-intensive, as you point out Grant there are far more significant issues to deal with here.
Yeah there is a LOT of low hanging performance fruit in there.
_______________________________________________________________
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/
August 7, 2015 at 4:11 pm
For best overall performance, for this proc and for others, I strongly urge you to switch the clustering on the Performance to be on ReportDate, or, if you prefer, ( ReportDate, ID ) to make it unique. Yes, this will be a royal pain, since typically you would:
1) drop all nonclustered indexes
2) drop the current clustered index
3) create the new clustered index
4) re-create all nonclustered indexes that are still needed; be sure to add one for the ID column.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply