1001 Report on SQL

Hi, One of our clients has an inventory of about 40,000 Items. The Inventory evaluation report takes ages to run. Probably 18 hours. The database version is SQL and client is 310. The client has recently been converted to SQL database from native database. Has anyone experienced the same problem before, and any suggestions to optimize the report would be highly appreciated. Thanks, Sharan.

The report is pretty straigh forward, it just goes through the Item Value table, and adds up whats there (or what was there) at a certain date. You could be having isues with the way the flow fields are calculating in SQL. The report is very flexible, and it may be to your advantage to concider writing a simpler one for this client. DO you have a lot of adjusting entries? How many Itel Ledger entries are there, and how many value entries. Do you have manufacturing, and are you regularly adjusting costs? … just a few questions to get started.

Hi David, No. of Item ledger entries are :1350869 And value entries are :1496870 They do not use manufacturing but they adjust the cost quiet often. I have made sure the keys are in the same order as the SETRANGE and SETFILTER. so that the totals can be calculated easily by SQL. Thanks, Sharandeep.

Find out which operations are slow. Simply. Run the report with the client monitor started (or if you like the SQL Profiler) for a short period - perhaps 10 mins or so. Stop the monitor and filter on Elapsed Time > 500 for example (that is 500 milliseconds - this is high). You should then get a picture of what is running slowly. Choose an operation and show all records again. You can see the key/filter being used for the slow operation. This should help you to see if there is bad key usage. This is just a begining - the troubleshooting tool helps with all this, but I’m afraid trying to guess these things just won’t get you anywhere.