Hi Dhiraj, 

You can try using the non-datapiped item valuation report.

the ODBC reports are labelled "icvalun1 , icvalun2, icvalun3, etc"...

To enable the non-datapiped (ODBC) form, you need to edit the ICRPT.ini file located in the ICXX program folder.

edit the ini in Notepad.. and search for ICVAL01

edit the following

Change 

type=DATAPIPE to type=ODBC

and Crystal=ICVALU01 to Crystal=ICVALUN1

Save the file and then close and re-open the IC Item Valuation screen.

Repeat the same for the other reports 

ICVAL02, etc

Regards,

DCrampton

Hi DCrampton,

Thank you for the response.

Please note that i have tried the above as you had advised. However, i do not notice any difference in the time it takes to generate the report before and after making these changes.

Is there anything else i can try?

Do i have to reboot the server after making the changes?

I haven't looked at the existing report but if I recall it starts at 0 and works it's way through all of the transactions up to your cutoff date. David can correct me if I'm wrong.

If you're good at Crystal, the faster way to write this report is to write a report based on ICILOC which has "today's" values. Then, use a sub-report to sub the net change in the ICHIST table back to the date you want to report on. Be sure to pass ItemNo, AcctSet, Location, FiscYear, and FiscPeriod to the sub-report as that will allow SQL to find that set of records quickly. (Edit: Acctset isn't required as there is a key on Itemno, Location, Fiscyear, Fiscperiod)

Most of the time you're going back to a period of time that's a few months ago or maybe to the beginning of the year. Rather than processing 5.5 years of transactions per item, you can just process 0.5 years of transactions which is 1/10 of the processing power required so your report will run 10 times faster.

If you can leverage the power of your database engine, you'll have a report in your hands very quickly.

Hi Dhiraj Kumar its very strange many of your clients are experiencing performance problems simultaneously.  Would it be correct to say that the Item Valuation report has become slower over time at these clients or did it happen suddenly?  If it happened suddenly, have these clients moved to different infrastructure recently, like migrated to cloud servers or has anything changed in the network connections between clients and the SQL database server?

Alternatively, in addition to looking into what DCrampton recommends, I would consider adding new indexes to your clients' company databases.  When you run a script to identify indexes that can improve performance, the SQL server will return recommendations for queries that are running slowly.  I would expect the server to identify slow response times for the queries that generate the Item Valuation report, provide indexes for those queries, and as a result significantly improve performance of the report.  What I would try is:

1. Organise for a time at one of your clients for everyone to log out of Sage 300 (maybe after hours if required).
2. Take a backup of the database.
3. Time how long the Item Valuation report takes to run.
4. In the Microsoft SQL Management Studio, open a new Query Window, ensure the Target Database (where the report runs slow) is listed at the top-left then paste in and run this script.
5. In the results in the last column you will see a series of "Create Index" lines.  Copy and paste these lines one at a time into a new Query window and execute them to create the new indexes.  If you copy and paste and execute them all at once, it may not execute all lines if one returns a duplicate.
6. Now run this script in another new query window to rebuild all the indexes including the ones just created.
7. Now see if the report runs faster.

Let me know how you go.  Remember you should have at least as much memory installed on the SQL server as the size of the production databases combined + 8 gigs for the OS, app and general breathing room.

Ensure the network connection between the SQL server and the Sage 300 RDS or workstation clients sustains over 800mbit read/write and the latency is always <1ms and that its a single hop between the two.

Finally even if the above manages to help, please develop a relationship with a local on-the-ground Sage 300 consultant or business partner as they deal with these types of issues on the ground, can provide invaluable advice and save you a lot of trouble.

Good luck!

This is great advice!