March 6, 2013 at 10:07 pm
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.
March 6, 2013 at 10:12 pm
Here is my suggestion. Drop the table variables in favor of properly indexed temporary tables. Let us know how it works.
March 6, 2013 at 11:17 pm
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[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2013 at 10:10 am
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[/url]
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
March 8, 2013 at 1:52 pm
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...
July 31, 2013 at 6:42 am
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? :sick:
July 31, 2013 at 7:59 am
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? :sick:
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
July 31, 2013 at 8:49 am
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? :sick:
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
July 31, 2013 at 10:36 am
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? :sick:
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply