how different could it be joining a #temp_table Vs @table_variable?

Question

how different could it be joining a #temp_table Vs @table_variable?

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

To add to that, DISTINCT in an IN subquery is neither necessary nor desired. SQL ignores duplicates in an IN, regardless of DISTINCT or no DISTINCT.

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

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

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

Eric M Russell (12/14/2011)
sqldba_newbie (12/12/2011)
I see huge performance difference on joining a #temp_table instead of @table_variable. Query runs way faster when i join on a #temp_table. Any idea what could make it so slow when i use @table_variable? I know i should provide more information but just wanted to know if there is any difference.
When a stored procedure references a table variable, SQL Server will attempt to keep a fixed execution plan, and to do this it will assume that the table variable contains a small record count. That's one reason why Microsoft provided a table variable as an alternative to temp tables, so it can be used in scenarios where it is considered beneficial to keep a fixed plan. However, a fixed plan doesn't always result in the best runtime performance, like when dealing with large resultsets.
Look at the exection plan of the table variable query versus the temp table query and see what they are doing differently. For example, are they using different join operators? You can attempt to compensate for this by using a query hints to force a certain JOIN operator (LOOP | MERGE | HASH), if you know one consistently performs better than the default that SQL Server wants to use with that table variable.
http://msdn.microsoft.com/en-us/library/ms181714.aspx
Also, it is commonly believed that one can't create an index on a table variable, however, this can be achieved by declaring a primary key like in the example below. Try declaring a primary key on the column(s) used in the table joins.
declare @mytable table ( order_id not null primary key);

Sure i can try this. Just wondering if you see the code which i have attached 'option (recompile)' works when i ran the batch of sql itself...doesn't seem to work when i use within a function? Any idea why would that be?

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 3

sqldba_newbie (12/14/2011)
Eric M Russell (12/14/2011)
sqldba_newbie (12/12/2011)
I see huge performance difference on joining a #temp_table instead of @table_variable. Query runs way faster when i join on a #temp_table. Any idea what could make it so slow when i use @table_variable? I know i should provide more information but just wanted to know if there is any difference.
When a stored procedure references a table variable, SQL Server will attempt to keep a fixed execution plan, and to do this it will assume that the table variable contains a small record count. That's one reason why Microsoft provided a table variable as an alternative to temp tables, so it can be used in scenarios where it is considered beneficial to keep a fixed plan. However, a fixed plan doesn't always result in the best runtime performance, like when dealing with large resultsets.
Look at the exection plan of the table variable query versus the temp table query and see what they are doing differently. For example, are they using different join operators? You can attempt to compensate for this by using a query hints to force a certain JOIN operator (LOOP | MERGE | HASH), if you know one consistently performs better than the default that SQL Server wants to use with that table variable.
http://msdn.microsoft.com/en-us/library/ms181714.aspx
Also, it is commonly believed that one can't create an index on a table variable, however, this can be achieved by declaring a primary key like in the example below. Try declaring a primary key on the column(s) used in the table joins.
declare @mytable table ( order_id not null primary key);
Sure i can try this. Just wondering if you see the code which i have attached 'option (recompile)' works when i ran the batch of sql itself...doesn't seem to work when i use within a function? Any idea why would that be?

Update: Actually the portion of sql does recompile when executed as function but still takes more time to execute. The ONLy difference in the exec plan is one does HASH match (good one) and SORT for the other one.

Eric M Russell SSC Guru Points: 125565 More actions · Answer 4

sqldba_newbie (12/14/2011)
sqldba_newbie (12/14/2011)
Eric M Russell (12/14/2011)
sqldba_newbie (12/12/2011)
I see huge performance difference on joining a #temp_table instead of @table_variable. Query runs way faster when i join on a #temp_table. Any idea what could make it so slow when i use @table_variable? I know i should provide more information but just wanted to know if there is any difference.
When a stored procedure references a table variable, SQL Server will attempt to keep a fixed execution plan, and to do this it will assume that the table variable contains a small record count. That's one reason why Microsoft provided a table variable as an alternative to temp tables, so it can be used in scenarios where it is considered beneficial to keep a fixed plan. However, a fixed plan doesn't always result in the best runtime performance, like when dealing with large resultsets.
Look at the exection plan of the table variable query versus the temp table query and see what they are doing differently. For example, are they using different join operators? You can attempt to compensate for this by using a query hints to force a certain JOIN operator (LOOP | MERGE | HASH), if you know one consistently performs better than the default that SQL Server wants to use with that table variable.
http://msdn.microsoft.com/en-us/library/ms181714.aspx
Also, it is commonly believed that one can't create an index on a table variable, however, this can be achieved by declaring a primary key like in the example below. Try declaring a primary key on the column(s) used in the table joins.
declare @mytable table ( order_id not null primary key);
Sure i can try this. Just wondering if you see the code which i have attached 'option (recompile)' works when i ran the batch of sql itself...doesn't seem to work when i use within a function? Any idea why would that be?
Update: Actually the portion of sql does recompile when executed as function but still takes more time to execute. The ONLy difference in the exec plan is one does HASH match (good one) and SORT for the other one.

A SELECT DISTINCT will use either HASH or SORT. Does specifying a HASH query hint help?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

Eric M Russell (12/15/2011)
sqldba_newbie (12/14/2011)
sqldba_newbie (12/14/2011)
Eric M Russell (12/14/2011)
sqldba_newbie (12/12/2011)
I see huge performance difference on joining a #temp_table instead of @table_variable. Query runs way faster when i join on a #temp_table. Any idea what could make it so slow when i use @table_variable? I know i should provide more information but just wanted to know if there is any difference.
When a stored procedure references a table variable, SQL Server will attempt to keep a fixed execution plan, and to do this it will assume that the table variable contains a small record count. That's one reason why Microsoft provided a table variable as an alternative to temp tables, so it can be used in scenarios where it is considered beneficial to keep a fixed plan. However, a fixed plan doesn't always result in the best runtime performance, like when dealing with large resultsets.
Look at the exection plan of the table variable query versus the temp table query and see what they are doing differently. For example, are they using different join operators? You can attempt to compensate for this by using a query hints to force a certain JOIN operator (LOOP | MERGE | HASH), if you know one consistently performs better than the default that SQL Server wants to use with that table variable.
http://msdn.microsoft.com/en-us/library/ms181714.aspx
Also, it is commonly believed that one can't create an index on a table variable, however, this can be achieved by declaring a primary key like in the example below. Try declaring a primary key on the column(s) used in the table joins.
declare @mytable table ( order_id not null primary key);
Sure i can try this. Just wondering if you see the code which i have attached 'option (recompile)' works when i ran the batch of sql itself...doesn't seem to work when i use within a function? Any idea why would that be?
Update: Actually the portion of sql does recompile when executed as function but still takes more time to execute. The ONLy difference in the exec plan is one does HASH match (good one) and SORT for the other one.
A SELECT DISTINCT will use either HASH or SORT. Does specifying a HASH query hint help?

I am not sure where to apply HASH join hint because it is really not doing a join( i understand internally it is)..so my query is something like this

select a,b,c from table1 T

where t.c in (select distinct c from table 2)

Eric M Russell SSC Guru Points: 125565 More actions · Answer 6

sqldba_newbie (12/15/2011)
Eric M Russell (12/15/2011)
sqldba_newbie (12/14/2011)
sqldba_newbie (12/14/2011)
Eric M Russell (12/14/2011)
sqldba_newbie (12/12/2011)
I see huge performance difference on joining a #temp_table instead of @table_variable. Query runs way faster when i join on a #temp_table. Any idea what could make it so slow when i use @table_variable? I know i should provide more information but just wanted to know if there is any difference.
When a stored procedure references a table variable, SQL Server will attempt to keep a fixed execution plan, and to do this it will assume that the table variable contains a small record count. That's one reason why Microsoft provided a table variable as an alternative to temp tables, so it can be used in scenarios where it is considered beneficial to keep a fixed plan. However, a fixed plan doesn't always result in the best runtime performance, like when dealing with large resultsets.
Look at the exection plan of the table variable query versus the temp table query and see what they are doing differently. For example, are they using different join operators? You can attempt to compensate for this by using a query hints to force a certain JOIN operator (LOOP | MERGE | HASH), if you know one consistently performs better than the default that SQL Server wants to use with that table variable.
http://msdn.microsoft.com/en-us/library/ms181714.aspx
Also, it is commonly believed that one can't create an index on a table variable, however, this can be achieved by declaring a primary key like in the example below. Try declaring a primary key on the column(s) used in the table joins.
declare @mytable table ( order_id not null primary key);
Sure i can try this. Just wondering if you see the code which i have attached 'option (recompile)' works when i ran the batch of sql itself...doesn't seem to work when i use within a function? Any idea why would that be?
Update: Actually the portion of sql does recompile when executed as function but still takes more time to execute. The ONLy difference in the exec plan is one does HASH match (good one) and SORT for the other one.
A SELECT DISTINCT will use either HASH or SORT. Does specifying a HASH query hint help?
I am not sure where to apply HASH join hint because it is really not doing a join( i understand internally it is)..so my query is something like this
select a,b,c from table1 T
where t.c in (select distinct c from table 2)

select a,b,c from table1 T

where t.c in (select distinct c from table 2 option (hash group))

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 7

Eric M Russell (12/15/2011)
sqldba_newbie (12/15/2011)
Eric M Russell (12/15/2011)
sqldba_newbie (12/14/2011)
sqldba_newbie (12/14/2011)
Eric M Russell (12/14/2011)
sqldba_newbie (12/12/2011)
I see huge performance difference on joining a #temp_table instead of @table_variable. Query runs way faster when i join on a #temp_table. Any idea what could make it so slow when i use @table_variable? I know i should provide more information but just wanted to know if there is any difference.
When a stored procedure references a table variable, SQL Server will attempt to keep a fixed execution plan, and to do this it will assume that the table variable contains a small record count. That's one reason why Microsoft provided a table variable as an alternative to temp tables, so it can be used in scenarios where it is considered beneficial to keep a fixed plan. However, a fixed plan doesn't always result in the best runtime performance, like when dealing with large resultsets.
Look at the exection plan of the table variable query versus the temp table query and see what they are doing differently. For example, are they using different join operators? You can attempt to compensate for this by using a query hints to force a certain JOIN operator (LOOP | MERGE | HASH), if you know one consistently performs better than the default that SQL Server wants to use with that table variable.
http://msdn.microsoft.com/en-us/library/ms181714.aspx
Also, it is commonly believed that one can't create an index on a table variable, however, this can be achieved by declaring a primary key like in the example below. Try declaring a primary key on the column(s) used in the table joins.
declare @mytable table ( order_id not null primary key);
Sure i can try this. Just wondering if you see the code which i have attached 'option (recompile)' works when i ran the batch of sql itself...doesn't seem to work when i use within a function? Any idea why would that be?
Update: Actually the portion of sql does recompile when executed as function but still takes more time to execute. The ONLy difference in the exec plan is one does HASH match (good one) and SORT for the other one.
A SELECT DISTINCT will use either HASH or SORT. Does specifying a HASH query hint help?
I am not sure where to apply HASH join hint because it is really not doing a join( i understand internally it is)..so my query is something like this
select a,b,c from table1 T
where t.c in (select distinct c from table 2)
select a,b,c from table1 T
where t.c in (select distinct c from table 2 option (hash group))

i tried 'Incorrect syntax near the keyword 'option'.'.Were you able to parse it?

Eric M Russell SSC Guru Points: 125565 More actions · Answer 8

sqldba_newbie (12/15/2011)
i tried 'Incorrect syntax near the keyword 'option'.'.Were you able to parse it?

It seems in my previous example, I misplaced the option (hash group). Below are two versions of a query against sys.objects table where I've reproduced forcing a Hash Match, which is reflected when setting statistics profile on and looking at the PhysicalOp column.

set statistics profile on;

select object_id, name from sys.objects T

where t.name in (select name from sys.objects);

set statistics profile off;

Merge Join

Sort

Filter

Clustered Index Scan

Sort

Filter

Clustered Index Scan

set statistics profile on;

select object_id, name from sys.objects T

where t.name in (select name from sys.objects)

option (hash group);

set statistics profile off;

Hash Match

Filter

Clustered Index Scan

Filter

Clustered Index Scan

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 9

Eric M Russell (12/15/2011)
sqldba_newbie (12/15/2011)
i tried 'Incorrect syntax near the keyword 'option'.'.Were you able to parse it?
It seems in my previous example, I misplaced the option (hash group). Below are two versions of a query against sys.objects table where I've reproduced forcing a Hash Match, which is reflected when setting statistics profile on and looking at the PhysicalOp column.
set statistics profile on;
select object_id, name from sys.objects T
where t.name in (select name from sys.objects);
set statistics profile off;
Merge Join
Sort
Filter
Clustered Index Scan
Sort
Filter
Clustered Index Scan
set statistics profile on;
select object_id, name from sys.objects T
where t.name in (select name from sys.objects)
option (hash group);
set statistics profile off;
Hash Match
Filter
Clustered Index Scan
Filter
Clustered Index Scan

Thanks again. I used the same code as attached in my initial post and wrapped in a sproc works perfectly fine. Seems like query hints just don't take in-effect inside a function?