Reads high when changing sort order

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

General SQL Server Forums

New to SQL Server Programming

Reads high when changing sort order

Author

Topic

midavis
Starting Member

23 Posts

Posted - 2010-07-15 : 09:14:31

I have a query that I need to sort in a desc order but when I do the reads shoot up. I have tried to put an index on the field I am sorting by but I don't think that will help. I am actually casting this column to an int so I can sort it numerically. Anyone have an idea of how to get this fixed? If I sort Asc I get 2 reads, but if I do it desc I get 354 reads. An example of the query is below. BTW, I am selecting the top because I am trying to get the highest value I have stored atm.

ALSO, what is really weird is the table I am getting the reads high on is coming from another table than the one I am sorting on.

Query1 - sort desc

select TOP 1 @ID = ID
from Table1 cm
inner join Table2 c on c.CodeCatID = cm.codeCatID
where cm.CompanyID = @CompanyID and
c.CatCode = @CatCode AND
IsNumeric(cm.CodeDesc) = 1
ORDER BY cast(cm.CodeDesc as int) DESC

Table 'Table2'. Scan count 177, logical reads 354, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table1'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query 2 - Sort Asc

select TOP 1 @ID = ID
from Table1 cm
inner join Table2 c on c.CodeCatID = cm.codeCatID
where cm.CompanyID = @CompanyID and
c.CatCode = @CatCode AND
IsNumeric(cm.CodeDesc) = 1
ORDER BY cast(cm.CodeDesc as int)

Table 'Table2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table1'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 09:32:44

What result does this give?

select TOP 1	@ID = ID
from		Table1 AS cm
inner join	Table2 AS c on c.CodeCatID = cm.codeCatID
			and c.CatCode = @CatCode
where		cm.CompanyID = @CompanyID
		AND IsNumeric(cm.CodeDesc) = 1 
ORDER BY	cast(cm.CodeDesc as int) DESC

N 56°04'39.26"
E 12°55'05.63"

midavis
Starting Member

23 Posts

Posted - 2010-07-15 : 09:44:25

same result

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources