What's wrong with this query? What makes it inefficient?

Miklos_HollenderMiklos_Hollender Member Posts: 1,598
edited 2009-04-16 in SQL Performance
(@P1 varchar(20))SELECT TOP 1 * FROM "customerdatabase"."dbo"."Company$CustomEntryTable"  WHERE (("Source Document No_"=@P1)) ORDER BY "Source Document No_","Line Identifier","Quote Quantity","Entry No_"

It looks perfect to me - the ORDER BY cause seems to suggest we are using the correct SETCURRENTKEY.

Still I got max_logical_reads like 71564.

Other stuff:

HW: inadequate I think (VMware Virtual SCSI - I don't think that's a good option) - I'll look into it but it's not related to max_logical_reads.

Client: 5.0SP1 Build 26084, SQL: 2005, 9.0.3042 - probably neither are the latest, but I'd like to update only if it is very clear it would help because the users are a bit angry, further downtime for server update / client update I only want to risk if it's really justified...

Server is very new so index stats and index fragmentation should not count yet (although a backup that ran for years without this was restored to there from SQL2000 so maybe yes?)

Index Hinting is turned off. RECOMPILE is not yet used, as I have no idea what that is and why and how it is to be used - any documentation?

Comments

Sign In or Register to comment.