Issue Performance of ADO

knm

Hi,
I'm currently trying to create a inventory valuation report for NAV2009R2 W1 Classic Client.
It may sound simple, but since the company has humongous number of records in the item ledger entries and value entries, running the out-of-the-box inventory valuation report will take hours to complete.

So using Rashed's direction mentioned in following web-site, I have tried to create a SQL Stored Procedure that is called from the classic client.
http://mibuso.com/blogs/ara3n/2009/07/0 ... amics-nav/

I have prepared the codes so that it runs in following orders.
1) Run Report
2) Create ADO Connection
3) Call SQL Stored Procedure
4) SP will calculate the inventory value, and insert into staging table in NAV
5) NAV Report will collect the data from staging table, and create valuation report.

The logic works fine if I restrict my target data to few items, but when I try to run for all the items, and locations, I'm not getting any response from the Stored Procedure, and NAV client simply freezes.
(Just for your information, when I run the SP from SMSS, I get the result in 1 hour, but one from classic client has been running more than 2 hours...)

Is there certain setup that I need to make when calling Stored Procedure?
Below is a sample of the code that I'm using in NAV.

IF ISCLEAR(ADOConnection) THEN CREATE(ADOConnection);
ConnectString := 'Provider=SQLOLEDB.1;Password=%1;' +
                 'Persist Security Info=True;' +
                 'User ID=%2;Initial Catalog=%3;Data Source=%4';
ADOConnection.ConnectionTimeout:=0;
ADOConnection.CommandTimeout := 0;
ADOConnection.Open(STRSUBSTNO(ConnectString,
                              password,
                              user,
                              DB."Database Name",
                              Server."Server Name"));
ADOConnection.BeginTrans;
ADOConnection.Execute(STRSUBSTNO('exec [dbo].[' + COMPANYNAME + '$InsertInventoryValuation] ' +
                               '''%1'', ''%2'', ''%3'', ''%4'', ''%5'', ''%6''',
                                DateFrom,//Date From
                                DateTo,//Date To
                                ItemFrom,//Item From
                                ItemTo,//Item To
                                LocationFrom,//Location From
                                LocationTo//Location To
                                ));
ADOConnection.CommitTrans;
CLEAR(ADOConnection);

Thank you in advance for your assistance!

bbrown

Design your stored procedure to handle a smaller dataset. Such as 1 item. Then build your report around that. I've used this approach in many places with very favorable results.

knm

bbrown,

Thank you for your feedback.
So are you suggesting to call the Stored Proc for each item, using the loop via C/AL? I wanted to get an idea on what you meant by

Then build your report around that.

Thank you.

bbrown

knm wrote:

bbrown,

Thank you for your feedback.
So are you suggesting to call the Stored Proc for each item, using the loop via C/AL? I wanted to get an idea on what you meant by

Then build your report around that.

Thank you.

Yes, that is what I mean.