OLE db report for LBI 9031

Sort:
You are not authorized to post a reply.
Author
Messages
Ruma Malhotra
Veteran Member
Posts: 412
Veteran Member

    We are in the process of converting a particular report from 9022 to 9031 that uses the prdistrib table and has been written in oledb.

    However this report is not completing refreshing and times out after 40 minutes of refreshing. if we break this report to go against a smaller set of data it completes fine.

    Is anybody willing to share any pointers or tips that I can use to get this report to complete refreshing. Remember this report is written in oledb and I would like to avaoid full table scans since the number of records in this table is around 15 million.

     

     

    Char
    Veteran Member
    Posts: 62
    Veteran Member
      If you'll paste what's in your record selection formula here, I'll tell you if it can be optimized - soemtimes that's the problem
      John Henley
      Senior Member
      Posts: 3348
      Senior Member
        Also, what is the purpose (and parameters) for the report?
        Thanks for using the LawsonGuru.com forums!
        John
        Jan
        Veteran Member
        Posts: 55
        Veteran Member

          I just ran into a similar issue last week.  If the cause is the same (at this point, I couldn't say if it is), then the problem isn't the sql but the bursting process.  You didn't mention if your report is bursting, so at this point, I'll assume it is.

          Our problem was that the bursting process (which was by company/accounting unit) was timing out internally on the LBI server.  That happened because the report was very large and because our financial liaisons had rights to see every accounting unit.  The bursting process timed out because of the way I cached the report in my LBI setup for that report.  On the Report Details page, check out your >Data Filtering>Bursting Options page -- you may have the Cache Files options set to Creation Time.  If you change this to View Time, the report won't burst until someone views it.  This means your report will publish quicker.  However,  if you do have some users who have a large set of viewing rights, their particular attempt to view the report may run into problems.  Users who have only a few rights should be able to view with no problems.

          Behind the scenes the problem was caused because our LBI server is running on a Windows Standard edition server -- which has a max of 4 gb of ram available.  The bursting process was running out of space.  Our I.T. group is now looking at a work-around for this limitation but hasn't come up with anything yet.

          Ruma Malhotra
          Veteran Member
          Posts: 412
          Veteran Member

            This is not a bursted report. i am just refreshing the report right now. I am using an index to filter the reprot where it uses 4 fields:

            1.company, acct_uint , account and date.

            Jan
            Veteran Member
            Posts: 55
            Veteran Member
              Hello

              Ppossibly the problem is still a resource issue on the server side. If the report is very large with filter and the refresh never completes, maybe the filtering process is causing problems on your LBI server. It might be worth while to check with your I.T. folks to see if they monitor what's going on with your process -- any why results are never returned to you.
              Ruma Malhotra
              Veteran Member
              Posts: 412
              Veteran Member

                This report has not been published to lbi yet. I am using the crystal designer to refresh the report.

                Char
                Veteran Member
                Posts: 62
                Veteran Member
                  If you're using something in record selectiom that won't pass in the where clause, the report will run longer than necessary. For example, datadiff won't pass dateadd will pass so if you'll paste your record selection, I can tell you if it will all pass. Also, you can tell by this - start the report running and look in the lower left corner of Crystal Reports and as it starts counting the two sets of numbers should stay the same like 100 of 100, 675 of 675, 2050 of 2050...... if they aren't even and it's more like 0 of 100, 1 of 675, 15 of 2050 - that's the sign that the report selection could be optimized
                  Ruma Malhotra
                  Veteran Member
                  Posts: 412
                  Veteran Member

                    the record selection is dst_acct_unit <> 80060. It is showing 4006 of 4006.

                    Char
                    Veteran Member
                    Posts: 62
                    Veteran Member
                      Nothing more you can do on the report side then. When you say "table scans" - simply using tables in a report won't do a true "table scan" - it uses the indexed fields very efficiently and Lawson is suprisingly well indexed for reporting. I'd expect to bring back four thousand rows in well under a minute if instead of using OLEDB you went right to the tables. I'm not a fan of OLEDB at all; the performance is terrible and anytime you have to use multiple commands because they don't have all possible links available then you really are doing a true table scan because the queries are separate. I doubt it would take more than a few minutes to link the tables you're using directly within a report and cut and paste the record selection - I think you'd be extremely pleased with the results.
                      Ruma Malhotra
                      Veteran Member
                      Posts: 412
                      Veteran Member
                        Thanks for the reply. I am not a fan of oledb also but this report was created about 4 years back when I did not know the performance issues between oledb and odbc. There was a lot of back and forth changes to get it right which is in the condition it is and because of the derived fields and validation time etc I cannot convert the report to odbc right away. that would be a whole big project in getting this report to work right.
                        Char
                        Veteran Member
                        Posts: 62
                        Veteran Member
                          I understand. If you want to outsource it, I'll convert it for you in less than a day's work if it's worth it to you
                          Ruma Malhotra
                          Veteran Member
                          Posts: 412
                          Veteran Member
                            I am in the final stages of getting the report to work. What I do see is on the filter or the index I would like to add a range like (600-620) and (700 to 720). what is the best way to acheive this in oledb. I want to set it up on the index or filter level.
                            You are not authorized to post a reply.