Help with query performance - 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 2005 Forums

Transact-SQL (2005)

Help with query performance

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-12-09 : 11:06:46

Hi,

I have this query

SELECT TOP 30 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [3year10knotmaintainedCH] ASC) AS Row, * FROM vwGenerateGrid3
WHERE [type]='car' ) As tbl WHERE row >= 31 AND row <= 60
ORDER BY [3year10knotmaintainedCH] ASC

Its taking about 30 seconds to run, is there anything obviously wrong? What kind of indexing should I be looking at to improve things?

Thanks

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-12-09 : 11:49:40

One suggestion is to make your code more readable. I posted your query below in a more readable fashion, which won't help you directly. But when the next reader comes along, they may be able to better understand your query now and be in a better position to help you!

SELECT TOP 30 
  * 
FROM 
  (SELECT ROW_NUMBER() OVER (ORDER BY [3year10knotmaintainedCH] ASC) AS Row, * 
   FROM vwGenerateGrid3 WHERE [type]='car' ) As tbl 
WHERE 
  row >= 31 AND row <= 60 
ORDER BY 
  [3year10knotmaintainedCH] ASC

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-09 : 11:52:00

Nothing is majorly wrong that should have a drastic impact on speed, but you can clean itup a little by just using


SELECT * 
FROM 
(
SELECT ROW_NUMBER() OVER (ORDER BY [3year10knotmaintainedCH] ASC) AS Row, * 
FROM vwGenerateGrid3 
WHERE [type]='car' 
) tbl WHERE row >= 31 AND row <= 60 
ORDER BY [3year10knotmaintainedCH] ASC

As far as indexing goes, you will want to index vw.[3year10knotmaintainedCH] and vwGenerateGrid3.Type, then INCLUDE all columns you are returning (Try to only return the columns you need, rather than using "*" if the table is large.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources