Recordset performance, clone? filter? or both?

Posted by: wsmith-dd on 4 August 2017, 2:43 pm EST

  • Posted 4 August 2017, 2:43 pm EST

    I have several questions relating to performance with recordsets and Active Reports. Currently I have a landscape report of which I have attatched a PDF copy with dummy data as an example and visual reference. The report consists of a main report that sets up and fills in the header information, name, dates, graphic, and column headings, it then calls for detail data. Each record is identified by a page association number which allows for 1 to 6 records per page. The problem is a record has so many data points (60+ that are displayed) that they cannot all fit on a single line, so it has been divided into four subsections, each with it's own subreport.

    Right now I am running 1 DataControl per report and feed each an SQL string to generate a recordset, the main recordset is basically a list of all the pages that need printed, association numbers plus header data. Each subreport grabs an association limited recordset for each page, 15-20 datapoints, for 4 mini-recordsets per page, so as you can imagine I get a performance hit anytime I start to run up the page count.

    Currently this report can get it's data from two different sources.
    A) Internal company network via a software application that connects to a central SQL2000 server for all data and recordsets, which contains all data for all customers.
    B) External via another software application that connects to a local Access Jet 4.0 database, which would be limited to a customer or two's data.

    1) Cloning?
    Would I gain performance by calling 1 sub-recordset per page with a full 60+ datapoint count, clone it and then pass the clones to the subreports?

    2) Filtering?
    Would I gain performance by calling recordsets that were not page/association limited at the start of a report generation, then filtering for each page?

    3) Both?
    Should I be doing both Filtering and Cloning?

    4) Another angle entirely that I am missed so far to date?
  • Replied 4 August 2017, 2:43 pm EST

    I believe this issue would be more accurately answered by someone more experienced in recordsets.  I do not know which method is faster to generate.

    I can tell you that accessing your external datasource in any case will cause a significant performance hit.  The method that requires the fewest external queries will produce a report the most quickly.  While I do not have any solid data, I also believe that this factor will dwarf any performance difference between cloning and filtering. 

    If you have any futher questions on this or discover any concrete answers to these questions, please let me know.

    Thanks again for your continued assistance on the forums.
  • Replied 4 August 2017, 2:43 pm EST

    5) I thought I had mentioned this, but reading over my previous post I didn't. I was thinking of passing the recordsets to the DataControl.Recordset, would this count as external and have that significant negative performance hit?

  • Replied 4 August 2017, 2:43 pm EST

    Since a recordset is already populated with data, this does not count as an external data source.  It does not need to call out to a database during report generation, so it is a good approach to use. 
  • Replied 4 August 2017, 2:43 pm EST

    I am now an official fan of the Filter property of a ADODB recordset, I am able to gain a very significant performance improvement in the speed that reports are displayed to the screen, so much so that I shudder to think I even thought about the other method, let alone put it into production.

  • Replied 4 August 2017, 2:43 pm EST

    Great :)  Thanks for the info!
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels