Options

Issue Performance of ADO

knmknm Member Posts: 170
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!

Comments

  • Options
    bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • Options
    knmknm Member Posts: 170
    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.
  • Options
    bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
Sign In or Register to comment.