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

Query hangs on table variable Expand / Collapse
Author
Message
Posted Wednesday, March 06, 2013 10:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 06, 2013 10:18 AM
Points: 5, Visits: 49
I have a query that consistently runs fast and returns the expected output, but when I try to insert the output into a table variable it just runs indefinitely, and I can't find the problem in the code. I can't really post the query or the output (this is a data mining task with healthcare data and there are strict privacy rules) but I will describe the situation as best as I can.

The query that runs fine takes these steps:
Declares datetime variables for StartDate and EndDate
Declares a table variable (an ID to ID crosswalk) with 2 fields and inserts 691,969 records with a select query
Declares another table variable (simple list of codes) with 1 field and inserts 465 records directly
Finally, there is a union select that pulls 3 fields each from 3 different tables, each inner joined to the crosswalk table variable and where Date is between StartDate and EndDate and the code is in the code list table variable.

This query returns 53,463 records in about 50 seconds.

When I try to insert this output into another table variable, it doesn't throw an error, it just runs - I have let it go over 26 hours before just to see what would happen - it just keeps executing...

Am I pushing my luck with all the table variables? I'm stumped.
Post #1427763
Posted Wednesday, March 06, 2013 10:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 22,518, Visits: 30,269
Here is my suggestion. Drop the table variables in favor of properly indexed temporary tables. Let us know how it works.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1427764
Posted Wednesday, March 06, 2013 11:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
I'll second that. I almost exclusively favor traditional temporary tables over the use of table variables. Performance is one reason, which you may be experiencing first hand, and ease of use when debugging is another major one. To be fair, there are a couple scenarios where I will use a table variable over a temporary table but they are corner-cases.

Here is a great article comparing and contrasting the two classes of object:

Comparing Table Variables with Temporary Tables By Wayne Sheffield


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1427780
Posted Friday, March 08, 2013 10:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:35 AM
Points: 1,172, Visits: 2,413
opc.three (3/6/2013)
I'll second that. I almost exclusively favor traditional temporary tables over the use of table variables. Performance is one reason, which you may be experiencing first hand, and ease of use when debugging is another major one. To be fair, there are a couple scenarios where I will use a table variable over a temporary table but they are corner-cases.

Here is a great article comparing and contrasting the two classes of object:

Comparing Table Variables with Temporary Tables By Wayne Sheffield


The key part of the article @opc.three cited is this, I think:

1) SQL cannot build statistics against a table variable, like it can against temporary tables. This means that the execution plan for table variables will always treat the table as if it has 1 row. Obviously, this means that it might not use the best execution plan.


You have a table variable that has over 600K rows. SQL Server constructs an execution plan for your query based on an estimation of 1 row in that table variable. Operations that are efficient for 1 row may be extremely inefficient for 600K rows. Eliminating the table variables would probably shave a significant amount of time off your 50-second query response, not to mention the effect on the variant with the final INSERT into another table variable.

I would venture a guess that adding the final INSERT to another table variable caused SQL Server to make that one additional "bad" choice about the execution plan ("bad" only because it is based on an inaccurate estimate of 1 row) that tips the whole thing over into "forever" response times. Please post a reply to let us know how you resolve the issue - it's nice to see our suggestions work for someone, and even when they don't, we may learn something, too!


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1428681
Posted Friday, March 08, 2013 1:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 06, 2013 10:18 AM
Points: 5, Visits: 49
That was an excellent article - I did some research a while ago about the best way to approach this project, but I don't remember seeing the "one row assumption" difference in execution plans explained so well.
So I have gone through and altered the query to use temporary tables instead of table variables - not only does the query actually complete, it completes in 18 seconds! Thank you so much, everyone!

I'm off to re-think several other queries now...
Post #1428765
Posted Wednesday, July 31, 2013 6:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:14 AM
Points: 5, Visits: 10
Let me start off by saying, I agree that temp tables are much more efficient that variable tables; and try to exclusively use temp tables. That said, you cannot use temp tables in table-valued functions, so what should I do now?

I am struggling with either caching the data as part of my hourly job, or finding another way to access the live data. I know what you are thinking, just use a stored procedure. I wish the answer was to use a stored procedure, but Tableau doesn't play well with stored procedures.

Does anyone have a suggestion?
Post #1479443
Posted Wednesday, July 31, 2013 7:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:35 AM
Points: 1,172, Visits: 2,413
christopher_rogala (7/31/2013)
Let me start off by saying, I agree that temp tables are much more efficient that variable tables; and try to exclusively use temp tables. That said, you cannot use temp tables in table-valued functions, so what should I do now?

I am struggling with either caching the data as part of my hourly job, or finding another way to access the live data. I know what you are thinking, just use a stored procedure. I wish the answer was to use a stored procedure, but Tableau doesn't play well with stored procedures.

Does anyone have a suggestion?


You'll probably get a better response if you start a new thread in the appropriate topic and include details about what exactly you're trying to do. The members of this forum tend to be incredibly good at suggesting ways to accomplish tasks without using temp tables/table variables.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1479501
Posted Wednesday, July 31, 2013 8:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
christopher_rogala (7/31/2013)
Let me start off by saying, I agree that temp tables are much more efficient that variable tables; and try to exclusively use temp tables. That said, you cannot use temp tables in table-valued functions, so what should I do now?

I am struggling with either caching the data as part of my hourly job, or finding another way to access the live data. I know what you are thinking, just use a stored procedure. I wish the answer was to use a stored procedure, but Tableau doesn't play well with stored procedures.

Does anyone have a suggestion?

The need to use a table variable implies you're writing multi-statement table valued functions. My answer to that is, don't, use inline table valued functions where possible.

Regarding Tableau, that's quite a limitation for a reporting product with such a good reputation. Can you switch to using SSRS?



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1479521
Posted Wednesday, July 31, 2013 10:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 14,825, Visits: 27,302
christopher_rogala (7/31/2013)
Let me start off by saying, I agree that temp tables are much more efficient that variable tables; and try to exclusively use temp tables. That said, you cannot use temp tables in table-valued functions, so what should I do now?

I am struggling with either caching the data as part of my hourly job, or finding another way to access the live data. I know what you are thinking, just use a stored procedure. I wish the answer was to use a stored procedure, but Tableau doesn't play well with stored procedures.

Does anyone have a suggestion?


Surely it will work with query structures other than multi-statement UDFs? That can't be the only choice, although it is probably the worst choice.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1479593
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse