Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Simplify Large Queries with Temporary Tables, Table Variables and CTEs Expand / Collapse
Author
Message
Posted Tuesday, August 9, 2011 9:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:29 AM
Points: 11, Visits: 49
Could it possible to use @table variable or CTE for processing 10 million records up? i'm afraid it will drain the memory.
Post #1157399
Posted Tuesday, August 9, 2011 10:03 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:05 AM
Points: 128, Visits: 920
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.
Post #1157404
Posted Wednesday, August 10, 2011 7:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 10, 2012 4:59 AM
Points: 12, Visits: 33
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
from Table 1 KO
INNER JOIN Table 2 KU
ON KO.NR = KU.NR

You avoid writting all these creates etc.. and - if it is inside a SP then it gets flushed automatically
Post #1157592
Posted Wednesday, August 10, 2011 7:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:56 PM
Points: 136, Visits: 624
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
from Table 1 KO
INNER JOIN Table 2 KU
ON KO.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

Post #1157595
Posted Wednesday, August 10, 2011 7:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 10, 2012 4:59 AM
Points: 12, Visits: 33
Buuuut.. if u r working with a looong sp.. u might want to avoid writting try catch.. bla .. 1 million times
Post #1157617
Posted Wednesday, August 10, 2011 7:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:12 AM
Points: 53, Visits: 167
@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.
Post #1157624
Posted Wednesday, August 10, 2011 8:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 10, 2012 4:59 AM
Points: 12, Visits: 33
u got a point there
Post #1157654
Posted Wednesday, August 10, 2011 8:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:56 PM
Points: 136, Visits: 624
Patibandla (8/10/2011)
@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.


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

Post #1157668
Posted Wednesday, August 10, 2011 8:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:10 PM
Points: 1,414, Visits: 4,541
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

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #1157670
Posted Wednesday, August 10, 2011 8:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:56 PM
Points: 136, Visits: 624
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

Post #1157679
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse