Execution time with order by clause

Question

Execution time with order by clause

sqlquery-101401

SSCarpal Tunnel

Points: 4951
More actions
April 18, 2005 at 4:42 am

#164047

hi
i have one simple top 100 select query ,which is running fine but when i give order by condition it is taking hell lot of time.
i have updated the statistics (by update statistics tablename)also but no relief.
please suggest something.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Are the column(s) you are ordering by indexed?

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 2

Basically it is fetching through view and yes order by column is with clustered index(primary key)

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

If you could post some DDL that would probably make it easier to look at this.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 4

this is the view 'Interface'

select top 100 percent log.InterfaceID

, log.Component

, log.XmlMessage

, log.ErrorCode

from interface log (nolock)

Left Outer Join ErrorMessage err (nolock)

on log.errorCode = err.AppErrorCode

order by log.lastmodifieddate desc

and i am using

select top 100 * from interface order by interfaceid desc

and interfaceid is primary key

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 5

I think problem is with the view

as it is ordering by a date which is not a index key I supose

My Blog: http://dineshasanka.spaces.live.com/

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 6

Why are you using TOP 100 PERCENT and ORDER BY LastModifiedDate in the view?? That means that the entire table must first be sorted on LastModifiedDate for the view to return it's results correctly. Then the sort on InterfaceID can take place, but the clustered index is of no use now since the input is already sorted in a different order. Remove the top and order by in the view and everything will go fast as lightning (well..)

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 7

thanks chris for your suggestion ,yes you are correct,but the issue is i cant modify the existing code.

but very thankful to you all

noeld SSC Guru Points: 96590 More actions · Answer 8

If you can't follow Chris Suggestions try to order the Cluster index in descending order like

Create clustered idx_name on interface (interfaceid desc) ...

I really would advice againt order by in views but this is just to try a hack!

BTW: You have also indexes on errorCode, AppErrorCode and lastmodifieddate ... right?

hth

* Noel

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 9

Can you throw away the view and just access the underlying tables directly?

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 10

Godd sugestion

if have to use a view due to secuirty reasons , try to create another view which will allign with your job!

My Blog: http://dineshasanka.spaces.live.com/