Indexing - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Indexing

Author

Topic

beemd
Starting Member

14 Posts

Posted - 2013-12-09 : 12:40:01

I have the query below which is taking a long time to execute (nearly 5 minutes)

What indexes should I create to improve it?

Thanks

SELECT A.* FROM (SELECT M.vehicleref, M.manufacturer, M.model, M.derivative, M.additionalfreetext, M.updated, M.source, M.isspecial, M.hasstock, M.transmission, M.fuel, M.mpg, M.co2, F.term, F.milespa, F.maintained, F.ch, F.pch, 1 as siteskinid, ROW_NUMBER() OVER(Partition by M.vehicleref ORDER by F.CH) as RowNumber FROM vwMain_Latest M LEFT JOIN dbGlobalCache..tblMainForIntermate_FIGURES_NEW F ON M.vehicleref = F.vehicleref WHERE M.type='commercial' AND F.ch <= 3000 AND F.ch >= 0 AND F.ch>0 AND M.category IN (0)) A WHERE(A.RowNumber = 1) ORDER BY ch

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-09 : 12:45:03

Show us the output of this:

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT A.* FROM (SELECT M.vehicleref, M.manufacturer, M.model, M.derivative, M.additionalfreetext, M.updated, M.source, M.isspecial, M.hasstock, M.transmission, M.fuel, M.mpg, M.co2, F.term, F.milespa, F.maintained, F.ch, F.pch, 1 as siteskinid, ROW_NUMBER() OVER(Partition by M.vehicleref ORDER by F.CH) as RowNumber FROM vwMain_Latest M LEFT JOIN dbGlobalCache..tblMainForIntermate_FIGURES_NEW F ON M.vehicleref = F.vehicleref WHERE M.type='commercial' AND F.ch <= 3000 AND F.ch >= 0 AND F.ch>0 AND M.category IN (0)) A WHERE(A.RowNumber = 1) ORDER BY ch

And show us the execution plan as well as what indexes are on those tables already.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-09 : 12:57:50

As a side note, although you have specified a LEFT OUTER JOIN, because of the predicates in your where clause you can turned that into an INNER JOIN. So, you might want to change that also.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-09 : 16:01:28

Need to see the definition for view (I'm guessing, based on the name):

vwMain_Latest

beemd
Starting Member

14 Posts

Posted - 2013-12-10 : 04:44:56

Thanks for the help:

Here is the output:

(4816 row(s) affected)
Table 'tblMainForIntermate_NEW'. Scan count 5, logical reads 10297, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblMainForIntermate_FIGURES_NEW'. Scan count 5, logical reads 1349, physical reads 101, read-ahead reads 781, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 9202 ms, elapsed time = 75719 ms.

Current indexes are just vehicleref ASC on both tables

The vwMain_Latest is simply a view of "SELECT * FROM tblMainForIntermate_NEW"

Thanks

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-10 : 12:26:36

quote:
Originally posted by beemd
Current indexes are just vehicleref ASC on both tables

Is it a clustered index or nonclustered?

Clustered would be much better.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-10 : 12:38:21

Try this, it might help:



SELECT 
    M.vehicleref, M.manufacturer, M.model, M.derivative, M.additionalfreetext, 
    M.updated, M.source, M.isspecial, M.hasstock, M.transmission, 
    M.fuel, M.mpg, M.co2,
    F.term, F.milespa, F.maintained, F.ch, F.pch, 
    1 as siteskinid --, 1 AS RowNumber
FROM (
    SELECT vehicleref, term, milespa, maintained, ch, pch, 
        ROW_NUMBER() OVER(PARTITION by F.vehicleref ORDER by CH) as RowNumber
    FROM dbGlobalCache..tblMainForIntermate_FIGURES_NEW
    WHERE ch >= 0 AND ch <= 3000
) AS F
INNER JOIN vwMain_Latest M ON M.vehicleref = F.vehicleref
WHERE
    M.type='commercial' AND M.category IN (0)
ORDER BY
    F.ch

beemd
Starting Member

14 Posts

Posted - 2013-12-11 : 04:31:13

Changing to clustered indexes takes the query time to 4 seconds.

Thanks so much!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-11 : 10:06:59

You're welcome! ~5 mins to 4 secs is a nice improvement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources