Temp tables vs Permanent table with deletes
in [Programming SQL]
|
From: tshad on 7 Apr 2010 20:44 "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message news:4BBCC7ED.60512C83(a)xs4all.nl... > Tom, > >> It's just confusing when in one case it says it will recompile and in >> another it says (or seems to say) that it won't. >> >> Tony says it will recompile the statement each time it sees the #Temp > > I haven't seen Tony say this. He said that the use of temporary tables > causes recompiles. But not "each time". > In one of the earlier resonses ***************************************** > How does that work? > Statements using the # table are recompiled. > If you are working with a temporary table but the plan is created before > or as you are starting the procedure - how does it use statistics? > The statement using the # table is recompiled - even in a stored procedure. > Or am I wrong here. > > Is the plan created during execution? > Yes - the plan is created during execution, but on hitting a # table it will recompile individual statements taking into account the statistics on those # tables. ***************************************** I wasn't sure what he meant. He says the statement using the # table is recompiled. So does that mean if I have 3 statements in my procedure it recompiles those 3 statements each time I run the procedure. I also saw where they say if you move the DDL statement (Create) to the top, it won't recompile (not sure on this)? Also, is there a difference between SP recompile and individual statement recompile where the SP would recompile then as it gets to any statement that has a # table, it would also recompile (again?)? > SQL Server keeps statistics for tables, both temporary tables and > regular tables. When there have been many changes, the statistics will > be recalculated. When this happens, the query plans that use this > information are invalidated. So the next time you run "the same" query, > it will cause a recompile, because there will be no cached version > anymore. > But a Temp table would be rebuilt the next time you ran the query any, wouldn't it and cause a recompile? Thanks, Tom > This also happens for regular tables, but the chance is much lower that > you will encounter this, because many users will be using the same > table. > > -- > Gert-Jan
From: John Bell on 8 Apr 2010 02:21 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. John
From: Gert-Jan Strik on 8 Apr 2010 13:35 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. So if you have a loop in which the same statement is executed again and again, then the first invocation might need a recompile, but after that, the cached query plan will remain available until the thresshold of inserts/updates/deletes has been reached, or until you drop the temp table (or SQL Server drops it). So if the temp table is creating within the SP, then each call to the SP will cause at least one recompile, but not necessariry recompile each statement again and again. -- Gert-Jan tshad wrote: > > "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message > news:4BBCC7ED.60512C83(a)xs4all.nl... > > Tom, > > > >> It's just confusing when in one case it says it will recompile and in > >> another it says (or seems to say) that it won't. > >> > >> Tony says it will recompile the statement each time it sees the #Temp > > > > I haven't seen Tony say this. He said that the use of temporary tables > > causes recompiles. But not "each time". > > > > In one of the earlier resonses > ***************************************** > > How does that work? > > > > Statements using the # table are recompiled. > > > If you are working with a temporary table but the plan is created before > > or as you are starting the procedure - how does it use statistics? > > > > The statement using the # table is recompiled - even in a stored procedure. > > > Or am I wrong here. > > > > Is the plan created during execution? > > > > Yes - the plan is created during execution, but on hitting a # table it will > recompile individual statements taking into account the statistics on those > # tables. > ***************************************** > I wasn't sure what he meant. > > He says the statement using the # table is recompiled. So does that mean if > I have 3 statements in my procedure it recompiles those 3 statements each > time I run the procedure. > > I also saw where they say if you move the DDL statement (Create) to the top, > it won't recompile (not sure on this)? > > Also, is there a difference between SP recompile and individual statement > recompile where the SP would recompile then as it gets to any statement that > has a # table, it would also recompile (again?)? > > > SQL Server keeps statistics for tables, both temporary tables and > > regular tables. When there have been many changes, the statistics will > > be recalculated. When this happens, the query plans that use this > > information are invalidated. So the next time you run "the same" query, > > it will cause a recompile, because there will be no cached version > > anymore. > > > > But a Temp table would be rebuilt the next time you ran the query any, > wouldn't it and cause a recompile? > > Thanks, > > Tom > > > This also happens for regular tables, but the chance is much lower that > > you will encounter this, because many users will be using the same > > table. > > > > -- > > Gert-Jan
From: tshad on 8 Apr 2010 14:27 "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??? 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
From: tshad on 8 Apr 2010 14:36
"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"? 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 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. Thanks, Tom > So if you have a loop in which the same statement is executed again and > again, then the first invocation might need a recompile, but after that, > the cached query plan will remain available until the thresshold of > inserts/updates/deletes has been reached, or until you drop the temp > table (or SQL Server drops it). > > So if the temp table is creating within the SP, then each call to the SP > will cause at least one recompile, but not necessariry recompile each > statement again and again. > > -- > Gert-Jan > > > tshad wrote: >> >> "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message >> news:4BBCC7ED.60512C83(a)xs4all.nl... >> > Tom, >> > >> >> It's just confusing when in one case it says it will recompile and in >> >> another it says (or seems to say) that it won't. >> >> >> >> Tony says it will recompile the statement each time it sees the #Temp >> > >> > I haven't seen Tony say this. He said that the use of temporary tables >> > causes recompiles. But not "each time". >> > >> >> In one of the earlier resonses >> ***************************************** >> > How does that work? >> > >> >> Statements using the # table are recompiled. >> >> > If you are working with a temporary table but the plan is created >> > before >> > or as you are starting the procedure - how does it use statistics? >> > >> >> The statement using the # table is recompiled - even in a stored >> procedure. >> >> > Or am I wrong here. >> > >> > Is the plan created during execution? >> > >> >> Yes - the plan is created during execution, but on hitting a # table it >> will >> recompile individual statements taking into account the statistics on >> those >> # tables. >> ***************************************** >> I wasn't sure what he meant. >> >> He says the statement using the # table is recompiled. So does that mean >> if >> I have 3 statements in my procedure it recompiles those 3 statements each >> time I run the procedure. >> >> I also saw where they say if you move the DDL statement (Create) to the >> top, >> it won't recompile (not sure on this)? >> >> Also, is there a difference between SP recompile and individual statement >> recompile where the SP would recompile then as it gets to any statement >> that >> has a # table, it would also recompile (again?)? >> >> > SQL Server keeps statistics for tables, both temporary tables and >> > regular tables. When there have been many changes, the statistics will >> > be recalculated. When this happens, the query plans that use this >> > information are invalidated. So the next time you run "the same" query, >> > it will cause a recompile, because there will be no cached version >> > anymore. >> > >> >> But a Temp table would be rebuilt the next time you ran the query any, >> wouldn't it and cause a recompile? >> >> Thanks, >> >> Tom >> >> > This also happens for regular tables, but the chance is much lower that >> > you will encounter this, because many users will be using the same >> > table. >> > >> > -- >> > Gert-Jan |