InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 11:40:10 GMT20http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote][b]alex (5/15/2009)[/b][hr]> I am not fond of cursors and advocate doing anything possible to replace the cursorWhat do you gain by handling the looping yourself rather than just using a cursor? I try and get rid of cursors by replacing with a single sql statement. Obviously this won't work if you have to call a stored procedure for every record in a table (for example), but if you have to do this then why is using your own looping mechanism preferable to using a cursor?[/quote]I agree... a good "firehose" cursor makes sense here.Fri, 15 May 2009 08:12:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx> I am not fond of cursors and advocate doing anything possible to replace the cursorWhat do you gain by handling the looping yourself rather than just using a cursor? I try and get rid of cursors by replacing with a single sql statement. Obviously this won't work if you have to call a stored procedure for every record in a table (for example), but if you have to do this then why is using your own looping mechanism preferable to using a cursor?Fri, 15 May 2009 04:40:40 GMTalex-746374http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxSQL Server 2005 handles most code better, but not all.If you are having performance issues, I suggest you post a more details explanation in the SQL Server 2005 Performance Tuning Forum.Thu, 12 Mar 2009 17:38:45 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxHi Randy, Thanks for the article. We also had similar issues joining two three tables with over 200 Million rows with WHERE condition, the O/P was dying if we used derived tables or JOINS. But once we started using #temp tables pre-populated with where conditions the queries executed in seconds. I think execution manager in SQL Server 2005 in not efficiently managing the worker tables or derived tables when the dataset is huge. Regards,Thu, 12 Mar 2009 17:09:58 GMTRazi, Mhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxyou can always put tempdb on multiple spindles using multiple filesI think MS did it this way because it would be a nightmare if apps created temp tables in user databases that couldn't be deleted until the next restart. this way all the junk is in the one database you rarely look intoFri, 06 Jun 2008 07:57:54 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxControl disk space and where it is usedReduce interference from other apps/databases ie locksMinimise effect of rogue apps/databases filling tempdbI did state ir was off the cuff.... didn't say it was a perfect idea :w00t:Fri, 06 Jun 2008 07:55:54 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxCREATE TABLE [temporaryTable]...LOLFri, 06 Jun 2008 07:53:01 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxMight be good if you had multiple filegroups and multiple temp tables you were gonna join?;)Fri, 06 Jun 2008 07:50:32 GMTSuperDBA-207096http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxwhat would be the point?Fri, 06 Jun 2008 07:47:05 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx:ermm: Out of the blue... off the cuff remark...Wouldn't it be nice to create a temp table and be able to specify the database in which is created :DFri, 06 Jun 2008 07:22:50 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxone of our analysts sent me a bunch of queries they run daily for some reports. each one is a select into a temp table, then another select into another temp table and joining data from the previous temp table and other tables.didn't feel like writing a crazy query with a bunch of where statements so i took the lazy way out. i created views for each of the temp tables. and running a select from a view calls the 2 other views automatically.Thu, 05 Jun 2008 09:21:31 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxNice article......Mon, 02 Jun 2008 04:59:03 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote][b]pduplessis (5/29/2008)[/b][hr]I agree that in most cases you can avoid the use of temp tables by structuring your query slightly differently, but for sure, sometimes they are unavoidable. [snip]...Firstly, ANYTHING going into a where statement, I queried the dimensions first and got the respective keys for (example, only certain products are deemed to be "activating" products). Popped these into a temporary products table, which I now could use in an inner join on my monster fact[snip]... [/quote]By forcing the temporary table in effect you are saying before you merge join hash/match whatever, that creating a single set of index seeks to produce a subset of records to join in a query can be more performant than a repeating series of clustered or even covering non-clustered index seeks each time the subset is referenced. Even though the number of index pages scanned is logarithmically related to the cardinality of the table it can still be non-trivial for large tables. Copying a small amount of data into a new table at some point is more efficient that certain amount of index seeks.Or in other words I know that there are a couple of (generally key always indexed) columns in this table and I know that if I restrict my working table to these before I start evaluating the rest of the query I can eliminate a whole pile of index page reads because the smaller number of rows I am interested in can be pre-fetched and much easier to index and join over as it is a much smaller range than via the index on the much large entire table.But the converse where the predicate is not a good indicator of selectivity (ie accounts < 100,000) the pre restriction wont help because the subset wont be much smaller than the original table (if indeed there are 110,000 accounts) and creating an indexed temporary table will be a waste of effort. For a given database design,row cardinality and list of selection predicates you can tell whether this method is going to be a benefit or not.Its forcing the data access design back into procedural programming (well 2 steps) away from a logical set definition of SQL (ie desired result and leaving the determination of the best method for evaluation to the query optimiser). The fact that this in practice this is necessary is a pity but I guess until you get large sets it gives no benefit and a DB never really goes back to being smaller. Whether this method is better is really a property of how selective a predicate is on an index eg: account = 123456789 on the product_sales table.Sun, 01 Jun 2008 19:10:19 GMTI cant let you do that Davehttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote][b]jmapledoram (5/30/2008)[/b][hr]But correct me if I'm wrong, doesn't a Table Variable get stored in ram rather than the tempdb? You will want to consult your DBA before writting a lot of these. You could just be transfering work load from one area to antoher without the hardware to support it.[/quote]Yes and no... if a table variable fits in memory, then it does just like a derived table does... it uses memory. If it doesn't fit in memory, it will use TempDB, just like a derived table does. Now, here's the surprise for some folks... where does a Temp Table live? If you said TempDB, you're only half right because, just like a Table Variable, if it fit's in memory, it lives in memory. Both will have an "entry" in TempDB and a derived table can appear as a "work" table in TempDB... same holds true for CTE's.Don't take my word for how both Table Variables and Temp Tables live in either memory or TempDB depending on their size though... I've posted it several times before on this very thread... if you haven't done so already, PLEASE read Q3/A3 and Q4/A4 in the following URL...[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k[/url]For those that think SELECT/INTO isn't logged in a database with a SIMPLE recovery mode... EVERY action in a database is logged... SELECT/INTO, if it meets certain conditions, is MINIMALLY logged and will still blow away an INSERT into a new table even if the database is in the FULL recovery mode.Again... don't take my word for it... run the following in each recovery mode... notice the number of logical reads on the INSERT INTO example... [code]--===== Create and populate a 10,000 row test table. -- This is the "controlled" source for further tests SELECT TOP 10000 SomeID = IDENTITY(INT,1,1), SomeInt = ABS(CHECKSUM(NEWID()))%50000+1, SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65) + CHAR(ABS(CHECKSUM(NEWID()))%26+65) INTO dbo.JBMTest FROM Master.dbo.SysColumns t1, Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN--===== A table is not properly formed unless a Primary Key has been assigned -- Takes about 1 second to execute. ALTER TABLE dbo.JBMTest ADD PRIMARY KEY CLUSTERED (SomeID)GO------------------------------------------------------------------------------------------ The INSERT/INTO test------------------------------------------------------------------------------------------===== Create a table for the Insert/Into testCREATE TABLE Table1 (SomeID INT,SomeInt INT, SomeLetters2 CHAR(2))--===== Do the test while capturing some statics information SET NOCOUNT ON PRINT '===== INSERT INTO =====' SET STATISTICS IO ON SET STATISTICS TIME ON INSERT INTO Table1 SELECT * FROM dbo.JBMTest SET STATISTICS TIME OFF SET STATISTICS IO OFF PRINT REPLICATE('=',78)GO------------------------------------------------------------------------------------------ The SELECT/INTO test------------------------------------------------------------------------------------------===== Do the test while capturing some statics information SET NOCOUNT ON PRINT '===== SELECT INTO =====' SET STATISTICS IO ON SET STATISTICS TIME ON SELECT * INTO Table2 FROM dbo.JBMTEST SET STATISTICS TIME OFF SET STATISTICS IO OFF PRINT REPLICATE('=',78)GO--===== Simple house keeping DROP TABLE dbo.Table1, dbo.Table2, dbo.JBMTest[/code]Fri, 30 May 2008 17:17:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxI guess in 90% of all cases where table variables / temp tables are applied, there is probably a better solution available like: - Rewriting the queries (fix bad access paths, remove functions around indexed fields) - Updating statistics - Using proper indexes (proper type, proper fields, proper index size) - Avoiding too many columns in one table (you could take it to the extreme: - Avoiding non-set based operations - Horizontal Partitioning - etc. etc.Thats at least the experience with my queries from the past ;-)I would be interested in others opinions regarding this!I also agree with some of the posters that mentioned that there is no such thing as black and white for these things. It's always a bit of this and a bit of that. You never can avoid the work of weighing the benefits against the drawbacks if you want to make a well reasoned decision. It will never be the perfect decision because as mentioned above, it is always a bit of this (good) and a bit of that (bad).But in order to make a good decision, you need to know all possible solutions to a problem and their characteristics. Otherwise you are just guessing and probably making the wrong guess. Ok, time for me to go to bed :DFri, 30 May 2008 14:27:20 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote][b]Simon Villiers (6/5/2006)[/b][hr]By "orphaned temp tables" I mean temp tables in tempdb that no longer have any connecting processes/sessions/etc. I know they are dropped when the connection terminates, and when I first saw this in the test environment I assumed it was a leftover from development in Query Analyzer or similar. When I saw this in the production environment I got an inkling something was amiss. Checking with the DBA revealed that there had been instances where temp tables remained after the end of the connection, and that there was a job to check for this. This was not a daily occurence, mind, just something that was done when tempdb became a little bloated; one of the things to check.I haven't seen this myself, and am quite curious to do so coz this shouldn't happen. This is about the extent of my knowledge on this situation. If you'd like more info, drop me an email and I'll see if I can dig something up.S.[/quote]On our busy production servers we see a build up of what you call "orphaned" temp tables (sql 2005). These are dropped temp tables that have not been cleaned up yet. In fact, there is a counter called Temp Tables for Destruction under the SQL Server:General Statistics object that will show how many tables are out there for the system cleanup thread to dispose of. But to my knowledge, there is no way to explicitly drop these objects without a restart of sql. If your DBA has a way of doing an explicit drop, PLEASE let me know. This has caused us grief ever since moving to sql 2005 and has forced us to do frequent (sometimes nightly) reboots to avoid a degradation of performance. (I've posted this issue to this forum several months ago but did not get much feedback). We are coming to the conclusion that the temp table count is related to 1) system memory and 2) the number of databases on an instance. We typically have between 300 and 500 databases per instance.Any help would be appreciated.Fri, 30 May 2008 13:30:01 GMTrubeshttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxOK decide to see what happens when I run the code for the same result each time in the values using a few different methods and here is what I found.Example 1: Using Table VariableRuntime: 8+ minutes[code]declare @t table(OrderID int, RequiredDate datetime not null, ShippedDate datetime null)set nocount ondeclare @x int, @job datetimeset @job = getdate()set @x = 0while @x < 1000beginDBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSdelete from @tinsert into @t select OrderID, RequiredDate, ShippedDate from Ordersset @x = @x + 1endselect @job, getdate()[/code]Example 2: Using Temp Table w/Insert Into and Delete From MethodRuntime: 2.5 minutes[code]create table #text(OrderID int, RequiredDate datetime not null, ShippedDate datetime null)set nocount ondeclare @x int, @job datetimeset @job = getdate()set @x = 0while @x < 1000beginDBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSdelete from #textinsert into #text select OrderID, RequiredDate, ShippedDate from Ordersset @x = @x + 1endselect @job, getdate()godrop table #textgo[/code]Example 3: Using Temp Table w/Insert Into and Truncate Table Method Runtime: 1.25 minutes[code]create table #text(OrderID int, RequiredDate datetime not null, ShippedDate datetime null)set nocount ondeclare @x int, @job datetimeset @job = getdate()set @x = 0while @x < 1000beginDBCC FREEPROCCACHEDBCC DROPCLEANBUFFERStruncate table #textinsert into #text select OrderID, RequiredDate, ShippedDate from Ordersset @x = @x + 1endselect @job, getdate()godrop table #textgo[/code]Example 4: Using Temp Table w/Select Into and Drop Table Method Runtime: 6-12 seconds[code]set nocount ondeclare @x int, @job datetimeset @job = getdate()set @x = 0while @x < 1000beginDBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSselect OrderID, RequiredDate, ShippedDate into #text from Ordersset @x = @x + 1drop table #textendselect @job, getdate()go[/code]I ran each item 5 times. Of course the examples are designed to try and stress each query by taking any advantages away.And the comment that "SELECT INTO isn't logged" is incorrect. I did check seperately to be and found like all objects stored in TempDB the tempdb log reflected the inserts so it is logged.Anyway, if anyone has an example where a Table Variable performs better than a Temp Table with regards to Northwind or other available database data please let me know so I can strip it down to see if there is anything that says why and when one may be better than another.As for the above examples when you shorten the cycle from 1000 to say 10 they all run about the same, at 100 they have a little more noticeable difference. And when you remove FREEPROCCACHE and DROPCLEANBUFFERS the longer running items do pickup quite a bit of speed.Fri, 30 May 2008 12:50:46 GMTAntares686http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxSorry about my CTE Post.I did not see that both [b]jmapledoram[/b] & [b]SQL Noob [/b]had already posted this as an alternative.matteoFri, 30 May 2008 12:43:11 GMTMatteo-317581http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxtop reason to use select into is to avoid logging...that makes it very fastUse global temporary tables and outside transactions you will never lock tempdb, no contention issue....Its using normal temp tables (#) with in transaction which lock tempdb Fri, 30 May 2008 12:31:59 GMTPrakash Hedahttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxI wonder if "Common Table Expressions" might also be an option here.You could create two CTE's that match your temp table creation and then join them together as you indicated.I have not tried it to see what the performance hit would be, but it would be a good thing to try out. regards allMatteoFri, 30 May 2008 12:27:51 GMTMatteo-317581http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxSorry you also have to run DBCC DROPCLEANBUFFERS for the execution plan to actually get regenerated.Fri, 30 May 2008 12:24:09 GMTAntares686http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxExpirementing a little more found this.Look at syscacheobjects in master I discovered that whichever item is ran first temp table or table variable or table variable with primary key for the same code a adhoc execution plan is stored. 99% of the time when I ran it the other way thereafter it would not create a new execution plan nor would it reuse the stored one. However if the code matched it would reuse the stored plan. If this is accurate then you have to make sure you run DBCC FREEPROCCACHE to clear this for the other to correctly measure performance. Also found that in any instance a "Prepared" execution plan is created and stored for(@1 varchar(30))SELECT * FROM [tempdb]..[sysobjects] WHERE [xtype]<>@1and all scenarios will reuse this plan and fire it twice during the run of the code I was testing with (see previous post).Will post any other findings I come up with as I continue testing.Fri, 30 May 2008 11:58:05 GMTAntares686http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxOne thing I love temp tables for is when I need to do some extensive processing on a large collection of rows. I want transactional consistency on the original data but I don't want to hold locks and block writers while all of my query executes. If I just select the data into a temp table first then I know I have a consistent set of data to start with, and I can process away without fear of blocking writers to the original table.Fri, 30 May 2008 10:01:11 GMTAdrian Hainshttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxYou are absolutely right!!! I found my self in a situation a few weeks ago where a stored procedure took over 20 minutes to run, i modified the stored procedure using temporary tables for the where clauses and the performance was increased by much!!!!Thanks for sharing!!!Daniel Hernández.Fri, 30 May 2008 09:27:03 GMTbreydanhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxAnother way I've found to further increase performance when joining an indexed temp table is to run UPDATE STATISTICS on the temp table. I wouldn't think this would be necessary but it helps when the temp table being joined has hundreds of thousands or millions of rows.One downside to this approach was that errors were being thrown (on SQL 2000) if the update statistics statement was in a procedure nested several levels deep. It worked fine when running the stored procedure directly.I've also found that temp tables are not always automatically dropped after the connection ends or the stored procedure goes out of scope. See [url=http://www.sqlservercentral.com/Forums/Topic488347-149-2.aspx]http://www.sqlservercentral.com/Forums/Topic488347-149-2.aspx[/url]ErikFri, 30 May 2008 09:23:40 GMTErik-131897http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote][b]SQL Noob (5/30/2008)[/b][hr]i personally try to avoid temp tablesi've seen people populate them with tens of thousands of rows and in many cases most of a source table they are selecting from. If I can i try to put logic in the query to avoid the temp table. in one case we have a database that is really supposed to be in LDAP, but we use it in SQL for business rules for an app. Users use a sp to access it and in some cases it returns over 100,000 rows and takes too long. I wrote a CTE to cut down on the time to run it. in other cases i like to use table variables to make sure the temp table is gone after it's used[/quote]CTEs are greate if you have >= SQL 05. And table variables are great if you don't have to pass the output to another SP. :) However, a table variable CAN be used in a UDF to pass output to another SP. Thus, essentially, a table variable CAN be used as input to an SP.But correct me if I'm wrong, doesn't a Table Variable get stored in ram rather than the tempdb? You will want to consult your DBA before writting a lot of these. You could just be transfering work load from one area to antoher without the hardware to support it.Again, this gets back to a all things in thier proper place and time. Temp tables have thier uses for sure.Fri, 30 May 2008 09:20:49 GMTjmapledoramhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxOK decided to take a look at TempDB to see what happens in the log [code](DBCC LOG 'tempdb',3)[/code]and found that regardless of table variable or temp table both are logged to the tempdb log. In fact I found this to be the case when I inserted only 1 row in the table variable. So tempdb logging will be a factor in the speed of the table variable it seems. Just as an FYI.Fri, 30 May 2008 09:17:49 GMTAntares686http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote][b]Matt Whitfield (5/30/2008)[/b][hr][quote][b]Matt Miller (5/30/2008)[/b][hr]Don't forget that settting a primary key is also the only way you have to put an index (clustered or not) on a table var. I'm not a huge fan of the table vars because of their screwy handling by the optimizer (who can't seem to see the true cardinality of a table var, and then "guesses"), but I still use them on really small things....[/quote]The article that JohnG posted shows you can actually... have a read it's quite interesting...[/quote]Yup I see that. It goes along with what I was saying about back-dooring an index with the Primary Key, but I hadn't thought of the unique constraint to backdoor in another index...nice!It still doesn't fix the cardinality/statistics issue, which may still lead to bad exec plans....Fri, 30 May 2008 08:53:36 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxYou know looking at it directly you cannot see a table variable because of the scope but if you look at it this way [code]declare @t table(OrderID int, RequiredDate datetime not null, ShippedDate datetime null)-- declare @t table-- (OrderID int primary key, -- RequiredDate datetime not null, -- ShippedDate datetime null, -- unique (RequiredDate, OrderID))--declare @a intinsert into @t select OrderID, RequiredDate, ShippedDate from Ordersselect * from tempdb..sysobjects where xtype != 's'goselect * from tempdb..sysobjects where xtype != 's'go[/code]you can see quite clearly that the table variable is still created in tempdb regardless. And if you create a Primary Key or Unique constraint they too are going to be defined in tempdb. So regardless of table variable or temp table you still would be impacted by events in tempdb. Would have to research the under the hood aspect of table variable management to see what it does compared to temp tables that may offer it a better performance and what the break point is between use of either. My big concern with table variables would be large sets of data depending on what it may be doing in the cache when you consider how the cache handles reuse of information. If a table variable is managed more in memory than on the tempdb tabl you run the risk of pushing cahced information out and having to reread those objects if they are used often enough they stay in memory. Just a curiosity I think someone should explore. It is great that table variables can potnetially save time and effort especially when testing in an environment with a minimal load, but what if any impact does it have to environments under heavy loads. That is what intrigues me most.Fri, 30 May 2008 08:49:37 GMTAntares686http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote][b]Matt Miller (5/30/2008)[/b][hr]Don't forget that settting a primary key is also the only way you have to put an index (clustered or not) on a table var. I'm not a huge fan of the table vars because of their screwy handling by the optimizer (who can't seem to see the true cardinality of a table var, and then "guesses"), but I still use them on really small things....[/quote]The article that JohnG posted shows you can actually... have a read it's quite interesting...Fri, 30 May 2008 08:48:40 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxi personally try to avoid temp tablesi've seen people populate them with tens of thousands of rows and in many cases most of a source table they are selecting from. If I can i try to put logic in the query to avoid the temp table. in one case we have a database that is really supposed to be in LDAP, but we use it in SQL for business rules for an app. Users use a sp to access it and in some cases it returns over 100,000 rows and takes too long. I wrote a CTE to cut down on the time to run it. in other cases i like to use table variables to make sure the temp table is gone after it's usedFri, 30 May 2008 08:46:37 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote][b]JohnG (5/30/2008)[/b][hr][quote]PK - can't see it making much difference at all.[/quote]Again, a typical "off the cuff" remark without thinking. I'm not saying ALL the time. One thing that a PK gives you is duplicate key detection (constraint violation). Yes, even for a table variable! You use it when the logic dictates its use.[code]DECLARE @v_temp_table TABLE (foobar int PRIMARY KEY CLUSTERED NOT NULL)insert into @v_temp_table values (1);insert into @v_temp_table values (1);[/code][font="Courier New"]Msg 2627, Level 14, State 1, Line 3Violation of PRIMARY KEY constraint 'PK__#02FEA01F__03F2C458'. Cannot insert duplicate key in object 'dbo.@v_temp_table'.[/font][/quote]Don't forget that settting a primary key is also the only way you have to put an index (clustered or not) on a table var. I'm not a huge fan of the table vars because of their screwy handling by the optimizer (who can't seem to see the true cardinality of a table var, and then "guesses"), but I still use them on really small things....Edit: I see that's been mentioned already. Okay - so I'm a little slow this morning:)Fri, 30 May 2008 08:27:09 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote][b]Matt Whitfield (5/30/2008)[/b][hr]2) Derived tables avoid using tempdbAgain not necessarily true. Derived tables quite often end up as a system generated temporary table in tempdb, this is done by the query processor if it decides it needs to (although i've never looked into it enough to find out why).[/quote]Was just going to mention this. Also, even in cases such as IN where ou have a list of items such as 1,2,3,4...10000 I have seen the query engine create a temp table and replace as an inner join. Couldn't find an exact point as to when it does this but noted on multiple occasion that adding one more item to my IN criteria resulted in a drastic decrease in time for the results and found this as the reason.But to a point mentioned earlier about orphaned temp tables, they should only occurr if the connection that created a # (local temp table) is still open check to make sure your app is closing the connection. In the case of ## (global temp tables) if any connection is open to the server it will remain there until all connections close or it is destroyed. Either way thou I do this in my code[code]If object_id('tempdb..#x') IS NOT NULL drop table #x[/code]I will usually perform at the start and the end of the process to be sure if I reuse the name later on with the same connection I don't accidentally end up with an error or the wrong set of data.If in TempDB multiple copies of #x exists only the one for the connection is acutally going to cause a non-null value.Fri, 30 May 2008 08:26:52 GMTAntares686http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote][b]JohnG (5/30/2008)[/b][hr]Well, somebody decided to test that theory to prove it or not and found that it can improve performance. See [url]http://msdn.microsoft.com/en-us/library/aa175774(SQL.80).aspx[/url]And before you discount the article, you may wish to take a serious look at the author's resumé.[/quote]Yep happy to eat my words then. One thing i've definitely learned today is that you do have the ability to create a clustered index on multiple columns on a table variable. Not sure if you can do it without the unique constraint though?Basically I just tested a temp table with 1024 rows inserting into another temp table. When ordering by two columns (which i deliberately inserted such that the entire table would have to be reversed) the query came out at 55 seconds over 10,000 iterations. Using the unique clustered index as tom has comes out at 35 seconds. Interestingly enough, doing the same, but with 1 column .v. clustered primary key, took 28 seconds on the ordering version and 32 on the clustered primary key version.So it's still back to the same old story of test test and test again. To be honest though, given that you should stick to temp tables for larger data sets, and that you're really not very likely to be inserting the same set of data into a table variable 10,000 times, i'll stick to the nuclear meltdown analogy, but i'll enlarge the size of the bucket :)Fri, 30 May 2008 08:09:29 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxThis is a good article. Could you do something besides temp tables? Yes, and the author points this out. Are there draw backs to using temp tables? Yes there can be as with any other choice that one may make. just to add to the author's points, hardware configurations can have a tremendous impact on performance. This is where testing is so important. Developers are often left to do the best they can with what is given. Even in the best of shops, there are limitations that cannot be overcome and thus the "best" way to write a query may not be available.Fri, 30 May 2008 07:50:02 GMTQ -631159http://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote][b]Matt Whitfield (5/30/2008)[/b][hr]And, basically, from a performance standpoint (which is where I was coming from in the first place), it will make as much difference as trying to stop a nuclear meltdown with water and a bucket.[/quote]Well, somebody decided to test that theory to prove it or not and found that it can improve performance. See [url]http://msdn.microsoft.com/en-us/library/aa175774(SQL.80).aspx[/url]And before you discount the article, you may wish to take a serious look at the author's resumé.Fri, 30 May 2008 07:49:21 GMTMauvehttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxYet another good article on the use of temp tables. And once again I think it highlights the fact that you can't generalize what is or isn't good practice.Sure table variables can be faster BUT ONLY if you are using them for small data sets. I've also seen SQL decide not to persist data in the tempdb for a temp table.... so it's never cut and dry.The key things I would take from this article are:1 Modulate your code2 Test what works best for a particular situation, e.g. derived tables vs temp tables etc3 If you are going to use temp tables maxamise their perfomance with proper indexing etcThats my two cents anyway... :)Fri, 30 May 2008 07:41:34 GMTrobertmhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspxAnd another typical post thinking you're amazing and that we're all idiots.Of course it's going to be useful for enforcing duplicate checking, if you're inserting duplicates in there in the first place. But what I was saying is that it would be rare to find that useful. Maybe you find it useful all the time because you love inserting duplicates or something. Personally I wouldn't use it too much. And, basically, from a performance standpoint (which is where I was coming from in the first place), it will make as much difference as trying to stop a nuclear meltdown with water and a bucket.Fri, 30 May 2008 07:24:58 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote]PK - can't see it making much difference at all.[/quote]Again, a typical "off the cuff" remark without thinking. I'm not saying ALL the time. One thing that a PK gives you is duplicate key detection (constraint violation). Yes, even for a table variable! You use it when the logic dictates its use.[code]DECLARE @v_temp_table TABLE (foobar int PRIMARY KEY CLUSTERED NOT NULL)insert into @v_temp_table values (1);insert into @v_temp_table values (1);[/code][font="Courier New"]Msg 2627, Level 14, State 1, Line 3Violation of PRIMARY KEY constraint 'PK__#02FEA01F__03F2C458'. Cannot insert duplicate key in object 'dbo.@v_temp_table'.[/font]Fri, 30 May 2008 07:15:08 GMTMauvehttp://www.sqlservercentral.com/Forums/Topic3450-84-1.aspx[quote][b]JohnG (5/30/2008)[/b][hr]Various comments:4. Related to the above and "why would you create a PK on a table variable?"Often enough. In the case of my delimited values pivot function, for business logic reasons, I need to know the order that the values appear in the list. So certain table variables [i]may[/i] have an IDENTITY column defined.[/quote]Right... and having a PK defined on an in-memory small data set is going to make loads of difference.... having an IDENTITY, yes. PK - can't see it making much difference at all.Fri, 30 May 2008 06:57:39 GMTMatt Whitfield