| Author |
Topic |
|
ramyaraman
Starting Member
10 Posts |
 Posted - 2008-06-30 : 10:28:01
|
| I have several complex select queries which selects from 2-3 tables, with 3-4 inner,right outer joins, and also 5 functions that are being called from that select query for almost 200000 rows. The total rows that get return is 2 million. Currently my query takes 50 minutes to return this. Is this normal? What should be the average time to return 2 million row on a decent serverI believe it is taking too much time. Having index is not improving perfomance. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-30 : 10:32:21
|
| Did you see execution plan if anything is costly? |
 |
|
|
ramyaraman
Starting Member
10 Posts |
Posted - 2008-06-30 : 10:34:35
|
| I don't see anything costly on execution plan. It is using my indexes. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-30 : 10:40:12
|
quote:
Originally posted by ramyaraman
I don't see anything costly on execution plan. It is using my indexes.
is it using index scans or seeks? also what are other steps involved? probably you could concentrate on steps which has greater % of query cost? |
|
|
|
ramyaraman
Starting Member
10 Posts |
Posted - 2008-06-30 : 10:49:52
|
| I do see it is doing Index Seeks. Also it uses Lazy Spool. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-30 : 10:52:19
|
quote:
Originally posted by ramyaraman
I do see it is doing Index Seeks. Also it uses Lazy Spool.
which is the costliest step? |
|
|
|
ramyaraman
Starting Member
10 Posts |
Posted - 2008-06-30 : 11:04:51
|
| Index Seek for a clustered index cost me 48% |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-30 : 11:11:38
|
quote:
Originally posted by ramyaraman
Index Seek for a clustered index cost me 48%
sorry apart from seeks is there anything taking significant cost? |
|
|
|
ramyaraman
Starting Member
10 Posts |
Posted - 2008-06-30 : 11:30:07
|
| I have two sorts that takes up 20%,25% and 1 Table spoll/eager spool 13% |
|
|
|
maninder
Posting Yak Master
100 Posts |
Posted - 2008-06-30 : 11:30:10
|
| What are your Server Specs.Version/Edition(Developer/Standard/ENT)HArdware Specs? (Processor/Memory)Are all these tables in one Database?Any Table partition?Maninder |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-30 : 11:33:54
|
quote:
Originally posted by ramyaraman
I have two sorts that takes up 20%,25% and 1 Table spoll/eager spool 13%
Sort can be a costly operation. Have you analysed the cause of these sorts? |
|
|
|
ramyaraman
Starting Member
10 Posts |
Posted - 2008-06-30 : 11:36:29
|
| This is sql2000 Enterprise Edition on windows 2003 Standard editionIt has 4GB of RAM and AMD Athlon 64*2 Dual Core Processsor 3800+ 2.01 GHZOn the same db with no table partition |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-30 : 11:58:50
|
| Can you put that query in Database Tuning Advisor and see Improvement Percentages? Do it in your machine ? |
|
|
|
ramyaraman
Starting Member
10 Posts |
Posted - 2008-06-30 : 12:02:13
|
| I did use index tuning wizard and no recommendations |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-30 : 13:38:57
|
quote:
Originally posted by ramyaraman
I have two sorts that takes up 20%,25% and 1 Table spoll/eager spool 13%
It means it has to create Temp table for it . Is your TempDB optimized and also Indexes and stats optimized? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-30 : 13:43:17
|
quote:
Originally posted by ramyaraman
The total rows that get return is 2 million. Currently my query takes 50 minutes to return this. Is this normal? What should be the average time to return 2 million row on a decent server
You will never be able to return 2 million rows quickly (measuring in seconds at least). Why do you need to return so much data? No human can process that much data. Is the data being fed into another system? Could you explain your process?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
Nominal time for complex Select Query