Inventory Valuation to SQL report Performance

ara3n · 2009-02-10

Hello
I decided to write the Inventory Valuation report on sql server. I translated the report into sql query. The data returned is correct and I've run multiple test and compared the numbers as of certain dates. I did all the testing in Cronus and then tried to run the report on bigger database. The query was going 100% cpu past 20 minutes and I decided to cancel it.

The query returns, Item No., Location code, Qty, QuantityAddorSubtract, InventoryValue. The reason for QuantityAdd orSubtract is because it is ASOFDate so

SELECT [Item No_],[Location Code],cast([Quantity] as float) as Qty, 
	(SELECT cast(isnull(sum(IAE.[Quantity]),0) as float) 
	from [Kronus5Sp1$Item Application Entry] as IAE ,[Kronus5Sp1$Item Ledger Entry] as I2 where IAE.[Posting Date] >= '09/03/09' 
	and I2.[Posting Date] <= '09/03/09' and 
	((I.[Positive] = 1 and I.[Entry No_] = IAE.[Inbound Item Entry No_] and 
	IAE.[Outbound Item Entry No_] <> 0 and  IAE.[Item Ledger Entry No_] <> I.[Entry No_] and  IAE.[Item Ledger Entry No_] = I2.[Entry No_]) 
	OR 
	(I.[Positive] = 0  and I.[Entry No_] = IAE.[Outbound Item Entry No_] and  IAE.[Item Ledger Entry No_] = I.[Entry No_]
	and IAE.[Inbound Item Entry No_] = I2.[Entry No_]))) as QuantitytoAddorSubtract,
 
 (SELECT  cast(isnull(sum([Cost Amount (Expected)]+ [Cost Amount (Actual)]),0) as float)
  from [Kronus5Sp1$Value Entry] as V where V.[Posting Date] <= '09/03/09' and
   [Item Ledger Entry No_] = I.[Entry No_]) as InvValue from [Kronus5Sp1$Item Ledger Entry] AS I 
   where I.[Posting Date] <= '09/03/09'

The bottleneck is the QuantityAdd or Subtract field

SELECT IAE.[Entry No_],IAE.Quantity --cast(isnull(sum(IAE.[Quantity]),0) as float) 
	from [Kronus5Sp1$Item Application Entry] as IAE ,[Kronus5Sp1$Item Ledger Entry] as I2 ,[Kronus5Sp1$Item Ledger Entry] as I
	where IAE.[Posting Date] >= '02/03/08' and 
	((I.[Positive] = 1 and I.[Entry No_] = IAE.[Inbound Item Entry No_] and I2.[Posting Date] <= '02/03/08' and
	IAE.[Outbound Item Entry No_] <> 0 and  IAE.[Item Ledger Entry No_] <> I.[Entry No_] and  IAE.[Item Ledger Entry No_] = I2.[Entry No_]
	and I2.[Item No_] = I.[Item No_]) 
	OR 
	(I.[Positive] = 0  and I.[Entry No_] = IAE.[Outbound Item Entry No_] and  IAE.[Item Ledger Entry No_] = I.[Entry No_]
	and IAE.[Inbound Item Entry No_] = I2.[Entry No_]and I2.[Posting Date] <= '02/03/08' and  I2.[Item No_] = I.[Item No_]))

Is there another way to query the data without using Item application entry?

Has anybody written the Inventory Valuation report on sql reporting services?

David_Singleton · 2009-02-10

ara3n wrote:

Is there another way to query the data without using Item application entry?

No, the way the expected cost is designed, its the only way.

ara3n · 2009-02-10

The item application is only used to calculate quantity as of a date. Cost amount Actual + Cost Amount Expected come from Value Entry and have nothing to do with Item application Entry.

So I'm basically looking for a way to calculate Quantity as of a certain date. Is as easy as just adding the quantity fields filtered on Posting date?

David_Singleton · 2009-02-10

ara3n wrote:

The item application is only used to calculate quantity as of a date. Cost amount Actual + Cost Amount Expected come from Value Entry and have nothing to do with Item application Entry.

So I'm basically looking for a way to calculate Quantity as of a certain date. Is as easy as just adding the quantity fields filtered on Posting date?

Unfortunately you will get close this way, but never exact, especially if the cusotmer has backdated purchase invoices (Purchase invoice posting date set to a date BEFORE inventory adjustment was run for that document) and if they have a lot of expected costs.

It depends on how exact they want it.

ara3n · 2009-02-10

MS Needs to change the way Item Application Entry implemented.

They should change the Invoice quantity and Remaining quantity field in Item Ledger to flowfield.
And add table underneath that will have an entry for every application for that Item Ledger.
This will make aging and adjust cost routines much faster to run and calculate.

Alex_Chow · 2009-02-10

ara3n wrote:

MS Needs to change the way Item Application Entry implemented.

Off topic, but there's a lot of things MS needs to change. But they're all wrapped up in the new screens for NAV2009.

ara3n · 2009-02-10

I'm sure they have different groups that concentrate on different areas. So they are not all working on the RT client screens. They need to work on improving the performance. Make table structure changes to improve performance.

David_Singleton · 2009-02-11

ara3n wrote:

I'm sure they have different groups that concentrate on different areas. So they are not all working on the RT client screens. They need to work on improving the performance. Make table structure changes to improve performance.

What's really needed is to throw out the costing engine and completely replace it. I have proposed to the PG that they split it into three separate engines, where you would have say Value entry table for FIFO/Specific costing, a new table for Average and a new table for standard and a whole separate mechanism for tracking expected costs anther for Serial numbers and lot number costing etc. That way you would have a completely different code base for each cost mechanism. Right now they find a bug in average cost, and the fix ends out hitting all the other costing methods this is why it has grown out of control.

But I think it will be at least two versions before we see costing fixed, and there is no point them doing a short term fix if a proper re-write is on the way.

davmac1 · 2009-02-11

Average costing is unworkable with internal serial number tracking with any large quantities.

This sounds like a great idea to me.
Maybe they can hire a few more people instead of laying them off.

ara3n · 2009-02-11

I've fixed the problem another way, I split the sql statement into two statement. I run the sql statement for Inbound entries first copy them to temporary Item ledger, then run the sql statement for Outbound entries.

The length of each sql statement is around 900 characters. Each statement now runs in 1.5 minutes.
It's amazing that originally it ran for 40 minutes. Now they run under 3 minutes running them separately.

The COM 1024 limit interface is a big shortcoming for NAV.
This is something else they need to fix, my guess it will take 2 to 5 years.

The report now runs at about 3 minutes. It takes on standard nav about 30 minutes. Now the new nav report takes 3 minutes.
I should post a blog on this.

Inventory Valuation to SQL report Performance

Comments

Categories