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 Wednesday, August 10, 2011 6:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:29 AM
Points: 11, Visits: 49
i created #tmp table when processing large of rows then i made index for some columns, those are often used in inquiry. Did i do right?
Post #1158154
Posted Wednesday, August 10, 2011 8:02 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 128, Visits: 912
Indexes will definitely help.

The only way to know for sure is try it both ways.
Post #1158161
Posted Thursday, August 11, 2011 7:11 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:44 AM
Points: 136, Visits: 611
amir.mochtar (8/10/2011)
i created #tmp table when processing large of rows then i made index for some columns, those are often used in inquiry. Did i do right?


The only problem with the indexes on temp tables in stored procedures or scripts is that they are not given by the SQL engine unique names as in the case of temp tables themselves. So if one connection invokes a procedure or script that creates a temp table and then its indexes, a second connection that will try to do this at the same time will fail because the indexes were already created by the first one. Remember, objects in SQL server has to have unique names.


Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

Post #1158449
Posted Thursday, August 11, 2011 7:17 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:44 AM
Points: 136, Visits: 611
the sqlist (8/11/2011)
amir.mochtar (8/10/2011)
i created #tmp table when processing large of rows then i made index for some columns, those are often used in inquiry. Did i do right?


The only problem with the indexes on temp tables in stored procedures or scripts is that they are not given by the SQL engine unique names as in the case of temp tables themselves. So if one connection invokes a procedure or script that creates a temp table and then its indexes, a second connection that will try to do this at the same time will fail because the indexes were already created by the first one. Remember, objects in SQL server has to have unique names.


I will have to retract that. You actually can create indexes with the same name but on distinct tables.

No issues with temp tables indexes.


Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

Post #1158454
Posted Thursday, August 11, 2011 7:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
the sqlist (8/11/2011)
I will have to retract that. You actually can create indexes with the same name but on distinct tables.

No issues with temp tables indexes.


Index names have to be unique on a table. Constraint names (incl primary key and unique constraints) have to be unique in a database.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1158462
Posted Thursday, August 11, 2011 7:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:29 AM
Points: 11, Visits: 49
the sqlist (8/11/2011)
the sqlist (8/11/2011)
amir.mochtar (8/10/2011)
i created #tmp table when processing large of rows then i made index for some columns, those are often used in inquiry. Did i do right?


The only problem with the indexes on temp tables in stored procedures or scripts is that they are not given by the SQL engine unique names as in the case of temp tables themselves. So if one connection invokes a procedure or script that creates a temp table and then its indexes, a second connection that will try to do this at the same time will fail because the indexes were already created by the first one. Remember, objects in SQL server has to have unique names.


I will have to retract that. You actually can create indexes with the same name but on distinct tables.

No issues with temp tables indexes.


yeah, thanks.

i got error that you mention earlier, i didn't realize the error will show in second connection, i put in foreachLoop component. i was planning to create hole process in ssis package that will be run in sql agent.

by the way, do you know where should i implement begin trans, commit, and rollback in package data flow? if in store procedure, i could easily implement those transaction keyword.

Post #1158482
Posted Thursday, August 11, 2011 5:30 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:06 PM
Points: 436, Visits: 2,277
EdSwiedler (8/9/2011)
One thing that I have found that can cause a temporary table or table variable to perform better than a CTE is that you can create indexes on them. This will help when joining them to other tables in later processing. No, I have not actually captured statistics on this, but have noticed a perceived performance boost. As with everything SQL Server, this will all be impacted by other tasks running at the same time.

I will say that I have found that a series of CTE's, each building on the last, does better than the massive join's that I have encountered.


As far as I know you can only create a single column, automatically-named primary key on a table variable (just learned this pretty recently).



Cursors are useful if you don't know SQL
Post #1158904
Posted Friday, August 12, 2011 2:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
mstjean (8/11/2011)
EdSwiedler (8/9/2011)
One thing that I have found that can cause a temporary table or table variable to perform better than a CTE is that you can create indexes on them. This will help when joining them to other tables in later processing. No, I have not actually captured statistics on this, but have noticed a perceived performance boost. As with everything SQL Server, this will all be impacted by other tasks running at the same time.

I will say that I have found that a series of CTE's, each building on the last, does better than the massive join's that I have encountered.


As far as I know you can only create a single column, automatically-named primary key on a table variable (just learned this pretty recently).


Automatically named, yes, one column, no.

DECLARE @Test TABLE (
Col1 INT,
col2 INT,
col3 INT,
col4 INT,
col5 INT,
PRIMARY KEY (col1, col2, col4)
)

You can create multiple unique constraints in the same way.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1159027
Posted Friday, August 12, 2011 10:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:06 PM
Points: 436, Visits: 2,277
GilaMonster (8/12/2011)
mstjean (8/11/2011)
EdSwiedler (8/9/2011)
One thing...(snipped).

As far as I know...(snipped).

Automatically named, yes, one column, no...(snipped)...You can create multiple unique constraints in the same way.


Awesome! Now that is gonna come in handy!



Cursors are useful if you don't know SQL
Post #1159378
Posted Wednesday, October 5, 2011 8:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:35 AM
Points: 33, Visits: 291
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!


Answer : CTE and leave the damn thing in one huge query should be the same because they do the same
Table variables have the advantage that are defined witih the session
Best performance is with temp tables, which are regular tables stored into the db tempd
I want to underline that on temp tables is possible to create indexes and statistics , and this can really improve the performance
Post #1185937
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse