I wrote on this subject a few months ago and a comment was made that perhaps a solution could be found by creating appropriate views and stored procedures.
I have in several instances tried the problem is the sheer volume of information that the SQL db is asked to manipulate.
Concrete example, the customer had built up a database of 24 million inventory transactions, there were 2,5 million lines in inventdim, inventsum had 6 million lines, the only active dimensions were the configuration on the inventory side and the warehouse, location and palett on the storage side. No serial numbers or Batch numbers so reasonably limited usage of inventdimid's. They used FIFO costing so inventsettlements were not too bad around 16 Million.
We ran a valuation report as at 3 months in the past. The report was unable to complete after 100 hours of processing time. The server was a quad processor with 32 Gb of RAM and a dedicated 15 K RPM raid 0+1 array with 24 disks in it, no penny pinching there.
The problem lies in the volume of data.
A consultant that helped design the above data model came and installed his solution which basically takes the data from inventtrans + Inventdim + inventsettlement and creates a flat table from the result. 12 hours of processing time. Afterwards the valuation report was printed in less than 10 minutes.
I think the above illustrates my problem with the current data model, having said all this many customers will never notice the issue as they do not have huge amounts of transactions. But as we all know over time systems grow and well this one does not have an archiving tool :-).
/Sven
Subscribe to:
Post Comments (Atom)
6 comments:
Was every primary dimension a financial dymension? and wat was the last time the close the inventory?
Primary Dimensions, Config and Warehouse were both financial dimensions and the data was FIFO closed (best option performance wise). Initially they did a Avg Closing but we deleted this and did a Fifo close to improve performance :-).
Nevertheless Benny Olesens solution that basically flattened the data out in a table solved the performance issue completely as the database is much better at managing a single flat table than complicated multi large table relations :-).
/Sven
We have:
14,2 million inventdimids
60,8 million inventory transactions
192 million lines in inventsettlement
we are not running on major performance problems - our reporting data is loaded in ca. 5 hours. but then we have oracle 10g and this example shows me once again why we do not want MS SQL Server...
It was an oracle 8i DB :-)
But on an RS6000 and we had a hard time optimizing some of the query plans.
Just out of interest how many open Inventsum's do you have ?
You are obviously using Avg Costing :-) based on your data, how is ledgerTrans / LedgerBalanceTrans and DimTrans doing ?
/Sven
It was an oracle 8i DB :-)
But on an RS6000 and we had a hard time optimizing some of the query plans.
Just out of interest how many open Inventsum's do you have ?
You are obviously using Avg Costing :-) based on your data, how is ledgerTrans / LedgerBalanceTrans and DimTrans doing ?
/Sven
I'm not an Oracle expert, I know everything from MS SQL. But some general questions. How is the disk IO of your database? is the averige waiting queue above 2? In that case you have disk IO trouble. Is the debug option on AOS and client turned of? (doubles performance sometimes)
Post a Comment