| Author |
Topic |
|
ayamas
Aged Yak Warrior
552 Posts |
 Posted - 2009-02-16 : 07:56:46
|
| I have a table containing about 400000 records.There are a set of calculations done on this table along with 5 different tables.On a particular criteria the no of rows returned are 940 while for other one only 3 rows are returned.I need to use aggregate function for the records returned.The execution time is about 4 minutes for 900 records & 6 seconds for 3 records when I use a table variable.But when I use a temporary variable the execution time is 40 seconds for 900 records & 20 seconds for those 3 records.Why is that?I am using the aggregate function on a date column.I am putting them into the temporary table as for next level of calculations as I need to join it to 3 more tables & then update the temporary table. |
|
|
jbp_j
Starting Member
24 Posts |
Posted - 2009-02-16 : 08:09:16
|
| hi,temporary table increases the Execution time.because they store in temp database.Operations with temp db increases the Execution time. |
 |
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2009-02-16 : 08:13:03
|
| But I cannot understand the reason why a table variable containing 900 records on which a simple aggregate function is executed takes 4 minutes. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-16 : 08:34:15
|
quote:
Originally posted by ayamas
But I cannot understand the reason why a table variable containing 900 records on which a simple aggregate function is executed takes 4 minutes.
Because Table variable will use internal temp table after certain load. Use temp table if you are dealing with lots of records. Table variable doesn't maintain statistics and therefore can choose bad execution plan. It is good only for less records. Use index in temp table to reduce tempdb size and faster output. |
|
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2009-02-16 : 08:46:14
|
| Thanks for the reply.But the problem is that I cannot estimate first hand the number of records.It can be 9 or 900 or even 9000 in my temporary.But my problem is when the no of records are in single digits or double the table variable runs 4 to 5 times much faster than a temporary table. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-16 : 08:49:18
|
quote:
Originally posted by ayamas
Thanks for the reply.But the problem is that I cannot estimate first hand the number of records.It can be 9 or 900 or even 9000 in my temporary.But my problem is when the no of records are in single digits or double the table variable runs 4 to 5 times much faster than a temporary table.
Because it works in Memory for less records. When records are more, it looks for Disk space consuming TempDB database. Lazy writes flushes the records from cache to disk and performance is slow. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-02-16 : 09:48:33
|
Hi ayamas,How often is this piece of code going to executed? if it's not too often then it sounds like the temp table is the way to go. The difference for small datasets is:temp table | variable20 | 3 Small Dataset = 6 2/3 slower40 | 240 large Dataset = 12 * tasterOf course this assumes that there is an even split of tasks (it's equally likely to return a big data set as a small one).Now you say quote:
I am using the aggregate function on a date column.I am putting them into the temporary table as for next level of calculations as I need to join it to 3 more tables & then update the temporary table.
Why not just use a derived query instead -- I recently had an issue with temp tables causing recompilation of query plans which was vastly helped by using a derived table rather than storing intermediate results in a temp table.Post the code and someone will be able to suggest something I'm sure.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-16 : 10:05:59
|
quote:
Originally posted by jbp_j
hi,temporary table increases the Execution time.because they store in temp database.Operations with temp db increases the Execution time.
Absolutely NOT true. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-16 : 11:31:30
|
Table variables doesn't have statistics. And Query Optimizer will always assume there is one and only one record in the table variable, no matter the reality is.This is one reason why Query Optimizer sometimes chooses a suboptimal plan for table variables.Post you code. Maybe there is something we can fix?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-17 : 00:24:36
|
| hi ayamas,see this link it may be useful http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx |
|
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2009-02-17 : 01:29:02
|
quote:
Originally posted by Peso
Table variables doesn't have statistics. And Query Optimizer will always assume there is one and only one record in the table variable, no matter the reality is.This is one reason why Query Optimizer sometimes chooses a suboptimal plan for table variables.Post you code. Maybe there is something we can fix?
E 12°55'05.63"N 56°04'39.26"
Thanks guys for your valuable information.But the problem is I cannot post the whole code because it really really is very complex & its difficult to explain the same.I will just post the problem part.SELECT InstrumentID, CASE WHEN EndDateTimeFromAlarm=EndDateTimeFromReading THEN NULL ELSE EndDateTimeFromAlarm END as EndDateTime INTO #FinalLowAlarm FROM ( SELECT L.InstrumentID, MAX(EndDateTime)as EndDateTimeFromAlarm, MAX(ReadingDateTime)as EndDateTimeFromReading FROM @LowAlarmTable L INNER JOIN InstrumentReadingDetails ON InstrumentReadingDetails.InstrumentID=L.InstrumentID INNER JOIN InstrumentReadingMaster ON InstrumentReadingMaster.InstrumentReadingMasterID=InstrumentReadingDetails.InstrumentReadingMasterID GROUP BY L.InstrumentID ) T GROUP BY InstrumentID,EndDateTimeFromAlarm,EndDateTimeFromReading UPDATE L SET L.enddatetime=#FinalLowAlarm.EndDateTime FROM @LowAlarmTable L INNER JOIN #FinalLowAlarm ON #FinalLowAlarm.InstrumentID=L.InstrumentID WHERE L.Rowid=1As you can see I have a @LowAlarmTable which currently is a table variable on which a aggregate function is executed.Even if I do a simple SELECT MAX(EndDateTime)as EndDateTimeFromAlarm FROM @LowAlarmTable L (for 900 records)But for 3 records it takes about 6 seconds(I guesss even that also is high).But if the same is replaced with temp table #LowAlarmTable it runs in 40 seconds for 900 records but for 3 records it takes about 25 minsit takes more than 4 mins at this stage no matter what kind of aggregate function I use also I cannot use a derived table because I need the resultset to do a union join with another table |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-02-17 : 04:45:33
|
well 1st of all: this is bad:quote:
SELECT InstrumentID,CASE WHEN EndDateTimeFromAlarm=EndDateTimeFromReading THEN NULL ELSE EndDateTimeFromAlarm END as EndDateTime INTO #FinalLowAlarm
this will make a heap and it will do it very slowly. What are you using SELECT INTO. INSERT performs much better and you can set up your temp table with an index - which would hopefully help joins later on.This doesn't make much sense to me:quote:
As you can see I have a @LowAlarmTable which currently is a table variable on which a aggregate function is executed.Even if I do a simple SELECT MAX(EndDateTime)as EndDateTimeFromAlarm FROM @LowAlarmTable L (for 900 records)But for 3 records it takes about 6 seconds(I guesss even that also is high).But if the same is replaced with temp table #LowAlarmTable it runs in 40 seconds for 900 records but for 3 records it takes about 25 minsit takes more than 4 mins at this stage no matter what kind of aggregate function I use also I cannot use a derived table because I need the resultset to do a union join with another table
There's no reason you can't use a derived table to join or union.relly I think you should post the whole code. or at least the definition for @LowAlarmTable and what's going to be in it.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
Table Variable & Temporary Table