Temp tables vs Permanent table with deletes
in [Programming SQL]
|
From: John Bell on 8 Apr 2010 15:14 On Thu, 8 Apr 2010 11:27:27 -0700, "tshad" <tfs(a)dslextreme.com> wrote: > >"John Bell" <jbellnewsposts(a)hotmail.com> wrote in message >news:8btqr5hh0gq5cfdctgtllo58ju6m0cu0ce(a)4ax.com... >> On Wed, 7 Apr 2010 17:34:08 -0700, "tshad" <tfs(a)dslextreme.com> wrote: >> >>> >>>"John Bell" <jbellnewsposts(a)hotmail.com> wrote in message >>>news:1pfor5peafkd1sqbltagb6f6p8ge2j6dlt(a)4ax.com... >>>>>> >>>>>> Any actual solution would be a guess as we haven't seen the code, but >>>>>> it sounds to me like this is probably one of those common cases where >>>>>> the solution is not set based. >>>>>> >>>>>It is all set based here. >>>>> >>>>>1) insert set of rows that is a subset of another table (or two) into >>>>>the >>>>>table >>>>>2) use the table, usually to update another table or insert records into >>>>>it >>>>>using this table in a join. >>>>>3) delete the records that were just added into the table (if you were >>>>>the >>>>>only one using the table at the moment - it would then be empty). >>>>> >>>> >>>> From this description I can't see the need for a temporry table so >>>> long as you use transaction correctly. >>>> >>>> If you used the subset for multiple procedures then you would need to >>>> create the temporary table in the parent procedure and then call the >>>> other procedures from that because when the parent procedure goes out >>>> of scope the temporary table would not exist... but that is not the >>>> pattern you have described! >>> >>>Sure it is. >>> >>>I was describing what happens now where the deletes seem to be slowing >>>down >>>the procedure. >>> >>>If I change that to: >>> >>>1) Create table in procedure (in the parent) >>>2) Insert rows >>>3) call procedure that uses the table >>>4) drop the table or drop out of the procedure (out of scope) which would >>>drop the table for me. >>> >>>At the moment all is done in one procedure (or another procedure is called >>>to do something with the table). It then comes back and the records are >>>deleted. >>> >>>> >>>>>> If you are looking at 1000 rows then adding an index/statistics on a >>>>>> table may be more expensive than not having the index. Because the >>>>>> permanent table is volatile statistics could be stale. >>>>>> >>>>>So then the Temp table would be better - based on what Tony said that >>>>>statistics are created on the fly as rows are added. >>>>> >>>>>> But without ciode that is all speculation. >>>>>> >>>>>True. But the code is different in each case. >>>> >>>> I have seen many systems where they have developed a poor pattern and >>>> then propogated it!! >>>> >>>True. >>> >>>And in this case, we have an issue where one or more of the procedures is >>>not deleting the records. One of the procedures jumps around the delete >>>if >>>there is nothing processed (but the records are still in the table). So we >>>end up with 25000 records. Shouldn't have any or just the ones currently >>>in >>>process. >>> >>>Thanks, >>> >>>Tom >>> >>>>> >>>>>The table has 3 columns (GUID, ParentID, ChildID). One Guid per >>>>>session. >>>>>If I were to use the table and add 100 rows, I would get a new GUID and >>>>>all >>>>>the 100 rows would use that Guid. >>>>> >>>>>When done I do a delete using that GUID. >>>>> >>>>>DELETE table WHERE GUID = @GUID >>>>> >>>>>Tom >>>> >>>> >>>> John >> >> I don't see why you are creating/dropping a procedure. They can exist >> without the temporary table existing. So long as it exists when it >> runs you will be ok. >> >I am not sure what you mean here. Maybe I explained it wrong or just >misunderstanding what you are saying. > >Creating and dropping what procedure??? Looks like I mis-read it. > >I would be creating and dropping a temp table. > >The temp table replacing the permanent table so I don't have to do all the >deleting at the end. > >Tom >> John > John
From: Gert-Jan Strik on 8 Apr 2010 15:53 tshad wrote: > > "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message > news:4BBE13F1.890696A8(a)xs4all.nl... > > Tom, > > > > When you call a stored procedure for the first time, it is compiled as a > > batch of statements, then it is executed. > > > > If you create a temp table in the SP, then the next statement that uses > > this temp table will have to be recompiled. In fact, any next statement > > that uses the temp table will be marked for recompilation. However, > > after that, if you execute the same statement again, it will not > > recompile. > > > What do you mean "Next statement"? "CREATE TABLE" is a statement. In your example (below), "SELECT ... FROM #Tom" is the next (2nd) statement. > If I create the table, there is no recompile there. But the next statement > it sees (select, update etc) that uses the table will be recompiled. > > And every other statement will be. > > For example: > > CREATE TABLE #Tom... <--no recompile > > SELECT ... FROM #Tom <- recompile each time SP called > > UPDATE #Tom... <- recompiled each time SP called > > WHILE... > BEGIN > INSERT #tom <-recompiled 1st loop only each time SP called. > SELECT... > END What you describe in your example above is exactly what I was saying. > But there was also something about recompiling each time if the CREATE TABLE > was mixed with the code instead of being at the start of the procedure. You seem to be missing the point what triggers a recompile or why there are recompiles. The key to this all is changed statistics! Nothing more, nothing less. Whenever you create a table, that table will have "new" statistics, as well as the definition about the actual columns, data types, etc. Obviously no possible query plan can exist (and still be valid) for any "old" table with the same name. The 2nd statement in your example ("SELECT ... FROM #Tom") is a different statement than the 4th ("INSERT #tom SELECT ..."), so obviously the recompile of the 2nd statement doesn't help the 4th statement at all. Each distinct statement requires its own query plan. There are only two other situations that cause a recompile: 1) you have done so many inserts/update/deletes that the statistics are recalculated. These new statistics can only be effectively used after a recompilation, which is exactly what happens 2) because of memory pressure, the existing query plan was purged from the cache to make room for something else -- Gert-Jan
From: tshad on 8 Apr 2010 22:03 "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message news:4BBE343B.B8798813(a)xs4all.nl... > tshad wrote: >> >> "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message >> news:4BBE13F1.890696A8(a)xs4all.nl... >> > Tom, >> > >> > When you call a stored procedure for the first time, it is compiled as >> > a >> > batch of statements, then it is executed. >> > >> > If you create a temp table in the SP, then the next statement that uses >> > this temp table will have to be recompiled. In fact, any next statement >> > that uses the temp table will be marked for recompilation. However, >> > after that, if you execute the same statement again, it will not >> > recompile. >> > >> What do you mean "Next statement"? > > "CREATE TABLE" is a statement. In your example (below), "SELECT ... FROM > #Tom" is the next (2nd) statement. > > >> If I create the table, there is no recompile there. But the next >> statement >> it sees (select, update etc) that uses the table will be recompiled. >> >> And every other statement will be. >> >> For example: >> >> CREATE TABLE #Tom... <--no recompile >> >> SELECT ... FROM #Tom <- recompile each time SP called >> >> UPDATE #Tom... <- recompiled each time SP called >> >> WHILE... >> BEGIN >> INSERT #tom <-recompiled 1st loop only each time SP called. >> SELECT... >> END > > What you describe in your example above is exactly what I was saying. > > >> But there was also something about recompiling each time if the CREATE >> TABLE >> was mixed with the code instead of being at the start of the procedure. > > You seem to be missing the point what triggers a recompile or why there > are recompiles. The key to this all is changed statistics! Nothing more, > nothing less. > I may be. > Whenever you create a table, that table will have "new" statistics, as > well as the definition about the actual columns, data types, etc. > Obviously no possible query plan can exist (and still be valid) for any > "old" table with the same name. > > The 2nd statement in your example ("SELECT ... FROM #Tom") is a > different statement than the 4th ("INSERT #tom SELECT ..."), so > obviously the recompile of the 2nd statement doesn't help the 4th > statement at all. Each distinct statement requires its own query plan. > I think I am misunderstanding here. We talk about whether a stored procedure has to be recompiled or not and we have a statement on the SP (with recompile). I was always thinking of the query place as being the plan for the whole procedure. But is seems what you are saying that there are multiple query plans for each procedure if there are multiple statements. So if the above example is in a SP, there are 3 query plans not one for the stored procedure. Tom > There are only two other situations that cause a recompile: > 1) you have done so many inserts/update/deletes that the statistics are > recalculated. These new statistics can only be effectively used after a > recompilation, which is exactly what happens > 2) because of memory pressure, the existing query plan was purged from > the cache to make room for something else > > -- > Gert-Jan
From: Kalen Delaney on 8 Apr 2010 22:53 Each statement has a plan, but the proc also has a plan that includes the plan for each of the statements. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "tshad" <tfs(a)dslextreme.com> wrote in message news:OQ7aWj41KHA.6108(a)TK2MSFTNGP06.phx.gbl... > > "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message > news:4BBE343B.B8798813(a)xs4all.nl... >> tshad wrote: >>> >>> "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message >>> news:4BBE13F1.890696A8(a)xs4all.nl... >>> > Tom, >>> > >>> > When you call a stored procedure for the first time, it is compiled as >>> > a >>> > batch of statements, then it is executed. >>> > >>> > If you create a temp table in the SP, then the next statement that >>> > uses >>> > this temp table will have to be recompiled. In fact, any next >>> > statement >>> > that uses the temp table will be marked for recompilation. However, >>> > after that, if you execute the same statement again, it will not >>> > recompile. >>> > >>> What do you mean "Next statement"? >> >> "CREATE TABLE" is a statement. In your example (below), "SELECT ... FROM >> #Tom" is the next (2nd) statement. >> >> >>> If I create the table, there is no recompile there. But the next >>> statement >>> it sees (select, update etc) that uses the table will be recompiled. >>> >>> And every other statement will be. >>> >>> For example: >>> >>> CREATE TABLE #Tom... <--no recompile >>> >>> SELECT ... FROM #Tom <- recompile each time SP called >>> >>> UPDATE #Tom... <- recompiled each time SP called >>> >>> WHILE... >>> BEGIN >>> INSERT #tom <-recompiled 1st loop only each time SP called. >>> SELECT... >>> END >> >> What you describe in your example above is exactly what I was saying. >> >> >>> But there was also something about recompiling each time if the CREATE >>> TABLE >>> was mixed with the code instead of being at the start of the procedure. >> >> You seem to be missing the point what triggers a recompile or why there >> are recompiles. The key to this all is changed statistics! Nothing more, >> nothing less. >> > > I may be. > >> Whenever you create a table, that table will have "new" statistics, as >> well as the definition about the actual columns, data types, etc. >> Obviously no possible query plan can exist (and still be valid) for any >> "old" table with the same name. >> >> The 2nd statement in your example ("SELECT ... FROM #Tom") is a >> different statement than the 4th ("INSERT #tom SELECT ..."), so >> obviously the recompile of the 2nd statement doesn't help the 4th >> statement at all. Each distinct statement requires its own query plan. >> > > I think I am misunderstanding here. > > We talk about whether a stored procedure has to be recompiled or not and > we have a statement on the SP (with recompile). > > I was always thinking of the query place as being the plan for the whole > procedure. > > But is seems what you are saying that there are multiple query plans for > each procedure if there are multiple statements. > > So if the above example is in a SP, there are 3 query plans not one for > the stored procedure. > > Tom > >> There are only two other situations that cause a recompile: >> 1) you have done so many inserts/update/deletes that the statistics are >> recalculated. These new statistics can only be effectively used after a >> recompilation, which is exactly what happens >> 2) because of memory pressure, the existing query plan was purged from >> the cache to make room for something else >> >> -- >> Gert-Jan > >
From: tshad on 9 Apr 2010 12:40
"Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message news:18C230E3-4D4D-4F32-AF04-DF5C404AF40F(a)microsoft.com... > Each statement has a plan, but the proc also has a plan that includes the > plan for each of the statements. > So when you are doing a WITH RECOMPILE. Does it do only the PROC plan? Or does it do the PROC plan and all the plans for all the queries. Then, according to Erlands article (if I am reading it right), if you then run the procedure again, it hashes the text of the procedure and tries to get the plan from cache. If it does, it now has the PROC Plan. What about the individual queries plans? Are they picked up as well? Thanks, Tom > -- > HTH > Kalen > ---------------------------------------- > Kalen Delaney > SQL Server MVP > www.SQLServerInternals.com > > "tshad" <tfs(a)dslextreme.com> wrote in message > news:OQ7aWj41KHA.6108(a)TK2MSFTNGP06.phx.gbl... >> >> "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message >> news:4BBE343B.B8798813(a)xs4all.nl... >>> tshad wrote: >>>> >>>> "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message >>>> news:4BBE13F1.890696A8(a)xs4all.nl... >>>> > Tom, >>>> > >>>> > When you call a stored procedure for the first time, it is compiled >>>> > as a >>>> > batch of statements, then it is executed. >>>> > >>>> > If you create a temp table in the SP, then the next statement that >>>> > uses >>>> > this temp table will have to be recompiled. In fact, any next >>>> > statement >>>> > that uses the temp table will be marked for recompilation. However, >>>> > after that, if you execute the same statement again, it will not >>>> > recompile. >>>> > >>>> What do you mean "Next statement"? >>> >>> "CREATE TABLE" is a statement. In your example (below), "SELECT ... FROM >>> #Tom" is the next (2nd) statement. >>> >>> >>>> If I create the table, there is no recompile there. But the next >>>> statement >>>> it sees (select, update etc) that uses the table will be recompiled. >>>> >>>> And every other statement will be. >>>> >>>> For example: >>>> >>>> CREATE TABLE #Tom... <--no recompile >>>> >>>> SELECT ... FROM #Tom <- recompile each time SP called >>>> >>>> UPDATE #Tom... <- recompiled each time SP called >>>> >>>> WHILE... >>>> BEGIN >>>> INSERT #tom <-recompiled 1st loop only each time SP called. >>>> SELECT... >>>> END >>> >>> What you describe in your example above is exactly what I was saying. >>> >>> >>>> But there was also something about recompiling each time if the CREATE >>>> TABLE >>>> was mixed with the code instead of being at the start of the procedure. >>> >>> You seem to be missing the point what triggers a recompile or why there >>> are recompiles. The key to this all is changed statistics! Nothing more, >>> nothing less. >>> >> >> I may be. >> >>> Whenever you create a table, that table will have "new" statistics, as >>> well as the definition about the actual columns, data types, etc. >>> Obviously no possible query plan can exist (and still be valid) for any >>> "old" table with the same name. >>> >>> The 2nd statement in your example ("SELECT ... FROM #Tom") is a >>> different statement than the 4th ("INSERT #tom SELECT ..."), so >>> obviously the recompile of the 2nd statement doesn't help the 4th >>> statement at all. Each distinct statement requires its own query plan. >>> >> >> I think I am misunderstanding here. >> >> We talk about whether a stored procedure has to be recompiled or not and >> we have a statement on the SP (with recompile). >> >> I was always thinking of the query place as being the plan for the whole >> procedure. >> >> But is seems what you are saying that there are multiple query plans for >> each procedure if there are multiple statements. >> >> So if the above example is in a SP, there are 3 query plans not one for >> the stored procedure. >> >> Tom >> >>> There are only two other situations that cause a recompile: >>> 1) you have done so many inserts/update/deletes that the statistics are >>> recalculated. These new statistics can only be effectively used after a >>> recompilation, which is exactly what happens >>> 2) because of memory pressure, the existing query plan was purged from >>> the cache to make room for something else >>> >>> -- >>> Gert-Jan >> >> |