August 9, 2011 at 7:59 am
One annoyance about the query optimizer in SQL Server is that sometimes the prettier solutions perform far worse, eg. sometimes ugly procedural T-SQL with temp table use etc can perform far better than a single, succinct SQL SELECT statement using CTE's etc..
I think the optimizer could do a better job with CTE definitions in sql server..
Or maybe its just my queries 😛
August 9, 2011 at 8:02 am
probably just me but i try to avoid temp tables as much as i can only because i don't like the I/O overhead of using them. One developer i knew used to use views all the time. i think he went way over board with them because one time i was troubleshooting performance issues with one of his processes and it took me hours to dig into the multiple levels of views that he wrote. most of them only returned a few rows.
i don't write a lot of SQL code but end up reading it and most of the really long queries have lots of temp tables. makes it a PITA to find out where the problem is and a lot of times running an estimated execution plan is useless because there is no temp table yet. if there was a view instead of a temp table it would probably make things a lot easier
caveat - like everything in SQL server there is no black or white and different things work in different environments and workloads. temp tables have their uses in some cases
August 9, 2011 at 8:55 am
sometimes the prettier solutions perform far worse
unfortunately SQL Server cares not one jot for the aesthetics of your code
August 9, 2011 at 8:56 am
alen teplitsky (8/9/2011)
probably just me but i try to avoid temp tables as much as i can only because i don't like the I/O overhead of using them. One developer i knew used to use views all the time. i think he went way over board with them because one time i was troubleshooting performance issues with one of his processes and it took me hours to dig into the multiple levels of views that he wrote. most of them only returned a few rows.i don't write a lot of SQL code but end up reading it and most of the really long queries have lots of temp tables. makes it a PITA to find out where the problem is and a lot of times running an estimated execution plan is useless because there is no temp table yet. if there was a view instead of a temp table it would probably make things a lot easier
caveat - like everything in SQL server there is no black or white and different things work in different environments and workloads. temp tables have their uses in some cases
Outside of improper indexing and scalar UDF usage some of the biggest performance wins I have done for clients is breaking down immensely complex single SELECT statements into interim queries using temporary tables for storage.
Oh, and I get paid by the hour, so I go KAAACCHHHIIIIIIINNNGGGG when I see a client with a ton of nested views that I have to wade through!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 9, 2011 at 9:58 am
Josh Ashwood (8/9/2011)
Nice article, and definitely on topic for real database developers...However the more relevant question we are often faced with is what performs better - table variables, temp tables, or CTE's or leave the damn thing in one huge query ?
An 'It depends' answer would be ok, if expanded on!
I'd agree. Showing some examples with finite variables and specific environment described would be helpful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 9, 2011 at 9:31 pm
Could it possible to use @table variable or CTE for processing 10 million records up? i'm afraid it will drain the memory.
August 9, 2011 at 10:03 pm
From my experience, a temporary table is the only way to go with that many records.
A table variable or CTE would be way too slow.
August 10, 2011 at 7:17 am
Why creating the tables.. sometimes you just do your temptable like this:
select distinct CONVERT(nvarchar(30), (KO.a)) AS a,
CONVERT(nvarchar(30), (KO.b)) AS b,
Navn AS Name
into#MyTempTable
fromTable 1KO
INNER JOINTable 2KU
ONKO.NR = KU.NR
You avoid writting all these creates etc.. and - if it is inside a SP then it gets flushed automatically
August 10, 2011 at 7:21 am
martha-1063616 (8/10/2011)
Why creating the tables.. sometimes you just do your temptable like this:select distinct CONVERT(nvarchar(30), (KO.a)) AS a,
CONVERT(nvarchar(30), (KO.b)) AS b,
Navn AS Name
into#MyTempTable
fromTable 1KO
INNER JOINTable 2KU
ONKO.NR = KU.NR
You avoid writting all these creates etc.. and - if it is inside a SP then it gets flushed automatically
There is no difference whatsoever between a temp table created with teh CREATE TABLE statement or on the fly with SELECT ... INTO. The tempt table gets flushed automatically when the connection created it is closed.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
August 10, 2011 at 7:47 am
Buuuut.. if u r working with a looong sp.. u might want to avoid writting try catch.. bla .. 1 million times 🙂
August 10, 2011 at 7:53 am
@sqlist
But with select into you cannot have an identity column , you have to use row_number otherwise , which may affect the performance when you are dealing with large number of rows.
August 10, 2011 at 8:22 am
u got a point there
August 10, 2011 at 8:30 am
Patibandla (8/10/2011)
@sqlistBut with select into you cannot have an identity column , you have to use row_number otherwise , which may affect the performance when you are dealing with large number of rows.
1. That is incorect:
select identity(int, 1,1) as qq,* into #tbl from master..sysdatabases
2. That doesn't change the fact that there is no difference between the temp table regardless of how you create them.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
August 10, 2011 at 8:31 am
could have been bugs in earlier versions of sql 2005 but i used to have problems with temp tables not being deleted. my indexing maintenance was a stored procedure and it would create temp tables for the indexes that needed to be maintained. used to have issues with it failing because the table was already in tempdb. ended up using table variables to fix it
August 10, 2011 at 8:37 am
alen teplitsky (8/10/2011)
could have been bugs in earlier versions of sql 2005 but i used to have problems with temp tables not being deleted. my indexing maintenance was a stored procedure and it would create temp tables for the indexes that needed to be maintained. used to have issues with it failing because the table was already in tempdb. ended up using table variables to fix it
Most likely the issues were in regards with the indexes unless you used global temp tables. The SQL engine makes sure that a temp table has a unique name even if is created at the same time from multiple connections by adding to the name ______ followed by a unique code. However if the procedure code creates indexes to the temp table the names would not be unique as they are created explicitly as they are specified, if the case.
There is no issues with temp tables themselves and never been.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply