Query runs 1min 54 sec first time then 1 sec

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query runs 1min 54 sec first time then 1 sec

Author

Topic

shebert
Yak Posting Veteran

85 Posts

Posted - 2009-09-25 : 09:57:18

This Query runs 1min 54 sec first time then 1 sec the next.
I think it has to build an execution plan the first time?
is there any way to speed up the first time it runs?

SELECT TOP (1) T1.Test_Instance_ID,T1.Wafer_KEY
FROM Test_Instance T1 INNER JOIN
Wafer ON T1.Wafer_KEY = Wafer.Wafer_KEY
WHERE T1.Test_Station_Name = 'OWT04' and
T1.Start_Time BETWEEN '20090909 03:42' AND '20090923 13:29'
and (T1.Wafer_KEY ='{26DC06D0-BB97-4FF1-AB0C-2482AC00C76C}')
ORDER BY T1.Test_Data_DateStamp DES

Could it be there are too many indexes on test_instance table?
Are there guidelines for indexing tables?
indexs below:

Unique/Non Clustered
ix_1 = test_instance_id

Non Unique/Non Clustered
ix2 = wafer_key
ix3 = startTime
ix4 = package_id
ix5 = test_instance_id and package
ix6 = test_instance_id and wafer_key
ix7 = test_instance_id and wafer_key and testData_timestamp
ix8 = test_instance_id and wafer_key and testStation_name
ix9 = test_instance_id and testData_timestamp and testStation_name
ix10 = assembly_lot
ix11 = assembly_lot, start_time,package_id and test_instance_id
ix12 = test_instance_id and Test_definition_id
ix13 = test_instance_id and Test_definition_id and start_time
ix14 = test_instance_id, testData_timestamp and testStation_name

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-25 : 10:48:23

Is this sql server 2005? because "DES" in your order by will error out. Assuming that was a cut/paste issue...

Check the actual execution plans (first run vs. second run). If they are the same then the difference in exec times is likely becuase of data caching.

for SELECTs too many indexes may not hurt you too much. But there is a lot of redundancy. for instance ix6 is not necessary since ix7 and ix8 both start with the ix6 keys.

Just based on this one query the clustered index choice might be better on Start_time since that seems to be the only "range" based criteria. You didn't include any indexes on Wafer. I assume that has indexes/PK as well?.

look at the execution plan for which components have the highest cost, which tables/indexes are being scanned rather than using a seek. With all those index choices the optimizer may not be production the ultimate plan available.

Be One with the Optimizer
TG

shebert
Yak Posting Veteran

85 Posts

Posted - 2009-09-25 : 11:14:43

Thank you very much.
DES was cut/paste issue
there is 2million+ rows in test_instance
500k in wafer
3 indexes on wafer
ix1 =pk unique on Wafer_key
ix2 =nunique on wafer_number
ix3 =nunique on wafer_key and wafer_number

Its Proably data caching.
do you think a clustered index on just start_time could help?

54% goes to (sort top N sort)
15% index seek wafer on wafer_key
15% index seek test_instance on start_time
15% index seek test_instance on station_name

on the index seeks does the output list kinda tell me which index it uses?
how can I tell what index it looks at?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-25 : 12:01:00

It looks like the major part of the problem is the sort on [test_data_timestamp]. Even though that is included in a couple indexes it is always a secondary to test_instance_id which is unique so (i think) those indexes are useless for the ORDER BY.

Maybe try changing the order by temporarily to [test_instance_id] just to see if that improves performance. If it does then I'd probably try removing the redundant indexes then adding one for [test_data_timestamp]

Be One with the Optimizer
TG

shebert
Yak Posting Veteran

85 Posts

Posted - 2009-09-25 : 12:12:21

Thansk for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources