August 10, 2011 at 6:57 pm
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?
August 10, 2011 at 8:02 pm
Indexes will definitely help.
The only way to know for sure is try it both ways.
August 11, 2011 at 7:11 am
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
August 11, 2011 at 7:17 am
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
August 11, 2011 at 7:32 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2011 at 7:54 am
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.
August 11, 2011 at 5:30 pm
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).
August 12, 2011 at 2:18 am
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-2 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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2011 at 10:29 am
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!
October 5, 2011 at 8:48 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!
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
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply