February 11, 2011 at 4:35 am
what are the main purposes for which temporary tables are used?
I want to know the uses of temporary tables in practical and commercial applications i.e. practically in working softwares.
February 11, 2011 at 5:38 am
Temporary storage of interim result sets that will be further processed by the procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2011 at 5:56 am
SQLCHILD (2/11/2011)
what are the main purposes for which temporary tables are used?I want to know the uses of temporary tables in practical and commercial applications i.e. practically in working softwares.
Temporary tables are used if you want to hold data temporarily for certain processos to be done on that.
February 11, 2011 at 7:46 am
Also keep in mind SQL Server will use temp tables without you ever explicitly knowing about it.
February 12, 2011 at 10:11 am
SQLCHILD (2/11/2011)
what are the main purposes for which temporary tables are used?
To go along with what Gail said above...
When used properly do "Divide'n'Conquer" a large "problem", you can get absolutely mind boggling blinding speed out of some queries. Seriously. Way too many people think that "good set based code" has to be a single query to accomplish a task and nothing could be further from the truth. I've used Temp Tables to divide up queries that have dozens of joins (typically created by an ORM or someone using a query designer) and that previous took anywhere from 45 minutes to 2 hours to run and have gotten them to run in seconds.
There are DBA's that won't allow Temp Tables to be used by developed code. It sometimes takes a bit to convince them that T-SQL will build "Work" tables in Temp DB behind the scenes but they normally come around when you show them that properly used Temp Tables can convert long running, resource greedy code that takes (sometimes) hours to run into something that takes only 3 or 4 seconds to run.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2011 at 9:46 pm
Dear Jeff Sir,
Thanks a lot for your reply, i also wanted to know that how do temporary tables hold the result set of a stored procedure. i have read about temporary tables uses and found that they are used to hold the result set of a stored procedure.
Please can you explain this with an example?
February 13, 2011 at 9:46 pm
Dear Sir,
Thanks a lot for your reply, i also wanted to know that how do temporary tables hold the result set of a stored procedure. i have read about temporary tables uses and found that they are used to hold the result set of a stored procedure.
Please can you explain this with an example?
February 13, 2011 at 9:47 pm
Dear Joy Sir,
Thanks a lot for your reply, i also wanted to know that how do temporary tables hold the result set of a stored procedure. i have read about temporary tables uses and found that they are used to hold the result set of a stored procedure.
Please can you explain this with an example?
February 13, 2011 at 10:34 pm
They can be, but it's the same principal. Store the results in a temp table for further processing. Nothing much fancier than that.
CREATE TABLE #StoredProcOutput (
<definition>
);
INSERT INTO #StoredProcOutput
EXEC SomeStoredProcedure
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2011 at 11:07 pm
SQLCHILD (2/13/2011)
Dear Jeff Sir,Thanks a lot for your reply, i also wanted to know that how do temporary tables hold the result set of a stored procedure. i have read about temporary tables uses and found that they are used to hold the result set of a stored procedure.
Please can you explain this with an example?
Gail covered it above.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2011 at 9:29 am
Jeff Moden (2/12/2011)
To go along with what Gail said above...
When used properly do "Divide'n'Conquer" a large "problem", you can get absolutely mind boggling blinding speed out of some queries. Seriously. Way too many people think that "good set based code" has to be a single query to accomplish a task and nothing could be further from the truth. I've used Temp Tables to divide up queries that have dozens of joins (typically created by an ORM or someone using a query designer) and that previous took anywhere from 45 minutes to 2 hours to run and have gotten them to run in seconds.
+1: For example, on a query that, say, SET STATISTICS PROFILE ON is showing hundreds of thousands of executions against a large table of which we're using only a few columns, if you've done your normal optimization steps already (particularly simplification; get rid of everything unnecessary), then take only the columns required from that often-hit subset (perhaps one to three of your tables), put them into a #temp table, index it for your particular use, use it in a second SQL statement, and drop it afterwards.
As Jeff has said, I've seen order of magnitude or more improvements on the "create temp table, index it, use it only once, destroy it" vs "one big SQL". Note that you need to try both ways, and try it with and without indexing; the cost of the index may or may not be worth it.
I tend to annotate the code, however, listing out what the gains of a temp table were in terms of CPU, reads, and writes (from Profiler); in many cases when people use temp tables, they actually harm performance; derived tables or pure JOIN integration are superior.
Rule of thumb: Simpler SQL is more likely to perform better as one statement. Complex and large SQL is more likely to benefit from a #temp table approach.
Always benchmark at this level of tuning; it's very difficult to predict with complete accuracy, and the exceptions can really hurt.
February 15, 2011 at 10:52 am
A couple of uses I've run into that were a bit specialized were breaking up data so that deadlocks would be avoided. A lock on the source table can be released if it finishes puting data into a local temp table, and then you use that temp table instead of the source table. Was the only way around a deadlock issue I had to resolve once. (Snapshot isolation is better at this, but I didn't have that as an option in that version of SQL Server.)
Another was a query with a lot of very, very complex business rules, that suffered from frequent changes. I found that applying the rules one at a time and inserting appropriate table IDs into a temp table, then using that temp table to control the final output of the query, made the whole query a tiny bit slower, but made refactoring the query for new/modified rules MUCH simpler. Was worth the performance hit to save hours of dev time every couple of weeks.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2011 at 10:59 am
In addition to the breaking up of queries for better performance...
Temp tables give me the option of winnowing down my data results, and deleting duplicates as necessary, when working with imports from other systems. This way, I can get everything done with one proc and before my INSERT instead of cleaning up the table afterward.
Temp tables are also good for storing results when I'm troubleshooting. I save different parts of a query in a temp table to see at what point the code went south and gave the wrong answer.
February 16, 2011 at 7:29 am
You will get much better much more quickly by reading a few books on sql server development (and working through their examples) than you will by asking basic questions such as this online.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 16, 2011 at 8:12 am
What about issues of memory usage on temp tables or table variables.
Is there a difference?
i.e. does a temp table (#mytable) write to the disk in tempdb?
vs. a table variable (@mytable) which I believe is stored in RAM or the pagefile if needed?
Is the amount of this memory usage significant?
Should it be a consideration depending on the size of the temp table being used?
Thanks in advance... TS
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply