Bad performance of item ledger entries on sql server

JanM

Hi
We installed a native db (NAV 4 SP3) on sql server 2005, optimized sift levels and changed code for more performance.
In few cases like on Item Ledger Entry the sift optimazation had no effect, the code cannot be changed there. The performance to show the entries is very bad, waitingtime of some minutes! The key is on:
Item No, Variant Code, Drop Shipment, Location Code, Posting Code
In sql profiler the following query has over 70000 reads and a long duration:

declare @p1 int
set @p1=180150653
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'SELECT  * FROM "SYMA400"."dbo"."SYMA-SYSTEM AG$Item Ledger Entry" WHERE (("Item No_"=@P1)) AND  "Item No_"=@P2 AND "Variant Code"=@P3 AND "Drop Shipment"=@P4 AND "Location Code"=@P5 AND "Posting Date"=@P6 AND "Entry No_"<=@P7 ORDER BY "Item No_" DESC,"Variant Code" DESC,"Drop Shipment" DESC,"Location Code" DESC,"Posting Date" DESC,"Entry No_" DESC OPTION (FAST 10)',@p3 output,@p4 output,@p5 output,N'@P1 varchar(20),@P2 varchar(20),@P3 varchar(10),@P4 tinyint,@P5 varchar(10),@P6 datetime,@P7 int','0.04.001','','',0,'','1753-01-01 00:00:00:000',714237
select @p1, @p3, @p4, @p5

It cannot be just a sql problem, the form hangs for some minutes, longer than every fulltext search would have.
It seams that from native to sql some parts of NAV are not rewritten or cannot be optimized. How we can analyze that? Can we see on the query where the problem is?

Thanks for help.
Jan

kriki

-Did you do an indexrebuild?
-try with Client Monitor to check what SQL does. I think it might do a clustered index scan.
-If this last is the case, you need to try with index-hinting.

garak

Do you have run this query on SQl Query analyzer? Is there the same time affect?
Starts there also the Exec. Plan and Client Statistic (Small Icons right side EXECUTE). _Is there an full table scan or Index Seek?

Or is it faster on SQL Query Analy, and bad on navision :?:
If yes, is clustered index on? have you run, for exampl, evry Sunday, an indexrebuild?

Regards

stryk

Hi!

First: SIFT Optimization has no impact on reading data from the Ledger. SIFT Optimization - how did you do it precisely? - primarily speeds up the write transactions into the Ledger, and reading data from SIFT tables for FlowFields.

Anyway ...
How many records are in "Item Ledger Entry"?
Is the SQL Index maintained for key "Item No, Variant Code, Drop Shipment, Location Code, Posting Date"

An "Index Rebuild" will not fix this problem, this should actually be part standard maintenance proceedures. Primarily the Index Statistics have to be updated daily! And you definitely DON'T want to use "Index Hinting" on the "Item Ledger Entry" table, as this will screw your system's peformance completely.

If this is indeed a "Parameter Sniffing" problem - bad Execution Plan online, good Execution Plan ad-hoc - you may implement a Plan Guide
for this query:

EXEC sp_create_plan_guide 
  @name =  N'MyGuide1',
  @stmt = N'SELECT  * FROM "SYMA400"."dbo"."SYMA-SYSTEM AG$Item Ledger Entry" WHERE (("Item No_"=@P1)) AND  "Item No_"=@P2 AND "Variant Code"=@P3 AND "Drop Shipment"=@P4 AND "Location Code"=@P5 AND "Posting Date"=@P6 AND "Entry No_"<=@P7 ORDER BY "Item No_" DESC,"Variant Code" DESC,"Drop Shipment" DESC,"Location Code" DESC,"Posting Date" DESC,"Entry No_" DESC OPTION (FAST 10)',
  @type = N'SQL',
  @module_or_batch = NULL,
  @params = N'@P1 varchar(20),@P2 varchar(20),@P3 varchar(10),@P4 tinyint,@P5 varchar(10),@P6 datetime,@P7 int',
  @hints = N'OPTION (RECOMPILE)'

Bat nonetheless, you should consider upgrading (technically) to 4.00 SP3 Update 7 (Build 25307) and SQL Server 2005 SP2 incl. cumulative Update 4 or higher.

Well, actually by default all NAV tables have a Clustered Index, thus the "Item Ledger Entry" table, too: "Entry No.".
Without a thorough analysis this is more guessing than knowing, but in some cases it could be feasible to change the CI here to "Item No., Posting Date" - but HANDLE WITH CARE! The optimal CI depends on several other things, but maybe you've got the chance to test!

Is the C/AL code executeing a FIND('+') here, or did you force the DESCENDING order?
If you use the FIND('+') here: do you really process all records from the filtered range? Or do you simply want to fetch only the last record from this range? In this case you should use the FINDLAST statement.
If you're deadling with the whole result-set, you should replace the FIND('-') with a FINDSET (parameters depending on further processing).

Hope this helps a little!

kriki

stryk wrote:

Without a thorough analysis this is more guessing than knowing, but in some cases it could be feasible to change the CI here to "Item No., Posting Date".

Just curious:
1) wouldn't it be better "Item No., Posting Date,Entry No."? This because "Item No., Posting Date" is not unique, so SQL puts an extra field (that serves only to make the records bigger) to make it unique.
2) And how about "Item No., Entry No."? Most of the time, "Posting Date" is not used. This index is small AND unique and a lot of times "Item No." is used to filter on and when opening the Item Ledger Entries form, this index is perfect. Another index might than be "Item No., Posting Date" for use with a filter on posting no. This CI I already used once on a SQL2005 64 bit and it fixed the problems I had.