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

Miklos_Hollender

(@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?

Marije_Brummel

Welcome to 5 SP 1.

Did you use the SQL index property?

I've created some unbelievable weird indexes on SP1 to get it running, indexes agains all basic knowledge in the NAV world.

Good for our business but inconvenient for people who just implement the old knowledge...

Don't worry about VMWare, should work like a charm and if any issues, look in VMWare settings, not NAV/SQL

Miklos_Hollender

Thanks. Didn't use it - it's already on max. selectivity, compared to the query. So you say there are some special tricks for 50SP1/SQL2005?

stryk

Hi!
Well, actually you NAV and SQL versions are somewhat old; especially your NAV is affected by some severe bugs ...

For recent NAV versions you might check out this one: http://dynamicsuser.net/blogs/waldo/archive/2009/03/04/platform-updates-overview-3-70-b-nav2009-rtm-updated.aspx

With NAV your SQL Build should be at least SP2 Update 4 Build 3200, with this Update MS has introduced several fixes specifically for NAV, and this version is required to use RECOMPILE hints properly. But actually already SP3 Update 2 has been released; see http://dynamicsuser.net/blogs/waldo/archive/2009/03/04/platform-updates-overview-3-70-b-nav2009-rtm-updated.aspx

Regarding your specific problem:
First of all, is it sure the "SQL Index" of that key "Source Document No_","Line Identifier","Quote Quantity","Entry No_" is maintained?
You could also try to simply copy the "Key" content to the "SQL Index" property (if this is NOT the PK).
Any changes then?

Kind regards,
Jörg

Miklos_Hollender

Thanks, I'll try these.