| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-11 : 11:13:34
|
| Sophie writes "Can you use a Stored Procedure to open a table and copy data to a sort of virtual table (or a records set) so that you can change the values with and not affect the actual data in the actual table. And then return the results of the virtual table? Thanks!" Article Link. |
|
|
tinks
Starting Member
34 Posts |
Posted - 2002-02-06 : 04:27:16
|
| A question a collegue asked me... We have a temp table that holds data for either 1 or many clients. Retrieval of data in permanent tables which we link to the temp table is slow in certain cases and we wish to add an index to the temp table (initial tests have show a large performance increase with the index!), however the temp table is referred to in stored procedures.The temp table is created in our front end application (therefore outside of the scope of the stored procedure)If an index is created on the temp table will the stored procedure use this when executing. I know that execution plans are created at the creation time of the stored procedure to speed up retrieval / processing time but seen as the temp table and the temp tables index will differ each time how does SQL deal with it?Some options that I have rattling in my brain is the use of 'with recompile' as a stored procedure option and create the index outside of the sproc or will using named indexes override the need for this.Taryn-Vee@>-'-,--- |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-06 : 10:09:00
|
| With v7+ the SPs are compiled at first run not at creation time. Creation just performs syntax checksWith v7+ if a temp table is accessed within a stored procedure but not created in it then the SP will be recompiled on each run.The SP cannot guarantee the structure of the temp table so has to be recompiled on each run.Given this all the SPs should use the index if useful.Note that as all the SPs that use the table will be recompiled on each run then this will degrrade performance and you could end up with contention on system tables.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
ProEdge
Yak Posting Veteran
64 Posts |
Posted - 2005-03-03 : 15:08:24
|
| I have a very similar problem to this except I'd like the temporary table to hold some copied data, then give the user the option to edit it (using the datalist control in ASP.NET), then update the data in the temporary table, and when ready, have them submit it to a final table. Does anyone know if I could do this all in one stored procedure or would I have to have this done in separate ones? Is this even possible? Any help would greatly be appreciated. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-02-13 : 20:59:05
|
Hi Bill, Great article... but you may want to update the article a bit because I think there's a couple of misperceptions on your part... You wrote (and I mean NO disrespect)... quote:
If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code above using a table variable might look like this:
The red part is what I have an issue with... please read the following Microsoft article on Temp Tables and Table Variables paying particular attention to Q3/A3 and Q4/A4...[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k[/url]... considering that table variables cannot be created using a SELECT/INTO, they cannot be made to use statistics, they cannot be made to use non-constraint/keyed indexes, they cannot be referenced in nested scope, and the fact that they spool to disk (TempDB) if they get too big, one has to ask why you said they are more flexible than Temp Tables.Further, Temp Tables also live in memory, just like table variables, until they too get too big at which time they spool to TempDB. They also allow creation using SELECT/INTO, do have statistics, and can use non-key indexes AND constraints. Still further, they "persist" in a QUERY ANALYZER or SSMS session (unlike table variables) so that you can do ad-hoc selects from them for troubleshooting and other analysis and they can be accessed in nested scope.What about all the recompiles that Temp tables cause? Well, apparently, that's a bloody myth. They state in the following URL...[url]http://blogs.msdn.com/sqlprogrammability/archive/2007/01/18/11-0-temporary-tables-table-variables-and-recompiles.aspx[/url]... the following... (I've highlighted the "Myth Buster" part...)When the stored procedure DemoProc1 is compiled, the insert and select query are not compiled. This is because during initial compilation, the temporary table does not exist and the compilation of this query is deferred until execution time. A compiled plan for the stored procedure is generated, but is incomplete. At execution time, the temporary table is created, and the select and insert statement are compiled. Since the stored procedure is already in execution, this compilation of the select and insert query are classified as a recompilation. It is important to note that in SQL Server 2005, only the select and insert statement in the stored procedure are recompiled. In SQL Server 2000, the entire stored procedure is recompiled. Subsequent re-executions of this stored procedure do not result in any more recompiles since the compiled plan is cached. Notice that even though the temporary table is re-created each time the stored procedure is executed, we do not recompile the stored procedure each time. This is because the temporary table is referenced in the plan by name and not by ID if they are created in the same module. Since the temporary table is re-created each time with the same name, the same compiled plan is re-used. Now consider a case when the temporary table is referenced in a second stored procedure as below:The beauty of the article (MSDN Blog, really) is that they have all the code to backup what they say.To me, the only advantage a Table Variable has over a Temp table, is that a UDF cannot use a Temp Table. If they removed that particular advantage, I'd never use a Table variable...... of course, I could be wrong...  Thanks for "listening". Again, I've not meant any disrespect... this is a great forum with a great "core" of people and you're one of the best.--Jeff Moden |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-02-14 : 04:55:55
|
| table variables are good to have for small sets of data that all fit in the memory.if the data in the table variable exceeds the available memory size is will get flushed to the tempdb._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2008-02-14 : 08:24:15
|
| Jeff,Interesting points. I mostly agree with what you're saying. The situation is certainly better now with 2005 than it was with 2000. I'll put that article in my queue to get updated.-Bill=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-02-14 : 23:23:06
|
quote:
Originally posted by spirit1
table variables are good to have for small sets of data that all fit in the memory.if the data in the table variable exceeds the available memory size is will get flushed to the tempdb._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Heh... agreed... the following is also true...Temp Tables are good to have for small sets of data that all fit in the memory.if the data in the Temp Table exceeds the available memory size is will get flushed to the tempdb.--Jeff Moden |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-02-14 : 23:25:20
|
quote:
Originally posted by graz
Jeff,Interesting points. I mostly agree with what you're saying. The situation is certainly better now with 2005 than it was with 2000. I'll put that article in my queue to get updated.-Bill=================================================Creating tomorrow's legacy systems today. One crisis at a time.
Outstanding... thanks for your consideration, Bill.--Jeff Moden |
|
|
|
Wodzu
Yak Posting Veteran
58 Posts |
Posted - 2009-09-09 : 08:33:23
|
quote:
Originally posted by graz
Jeff,Interesting points. I mostly agree with what you're saying. The situation is certainly better now with 2005 than it was with 2000. I'll put that article in my queue to get updated.-Bill=================================================Creating tomorrow's legacy systems today. One crisis at a time.
Bill, you still haven't upgraded your article in the part which was marked red by Jeff ;-)Anyway, very nice article. Thanks for your effort. |
|
|
|
Postalus_Michaelus
Starting Member
1 Post |
Posted - 2010-05-02 : 11:24:30
|
| Such a simple article and it gets the point across correctly.I'm able to see why some temp table objects in a script I'm debugging aren't going to work and I can rapidly do something about it. This presentation of information allows me to find this as a top link, get my question answered and get something done.Keep up the good work.Postalus_MichaelusFrom the City ofCincinnatus |
|
|
|
|
Article: Temporary Tables