Options

Clarification on FINDSET operation - number of records read

Mike_HWGMike_HWG Member Posts: 104
From MSDN:
The FINDSET operation optimizes reading records from SQL Server by establishing a stream of records between Microsoft Dynamics NAV and SQL Server. While the stream is open, no other activity occurs between Microsoft Dynamics NAV and SQL Server. Before the records are read, Microsoft Dynamics NAV has no information about how many records are available to read. However, Microsoft Dynamics NAV must allocate enough memory to accommodate all records that it will read for the FINDSET operation. The stream does not allow it to read records in groups. Microsoft Dynamics NAV allocates memory for a preset number of records and then begins reading the records. You can change the value of this preset number by changing the Record Set value in the New Database or Alter Database window

Does this mean that the number of records read is equal to the entire table, or is it equal to the filtered down result? I'm assuming it's the filtered set, but I want to be sure.
Michael Hollinger
Systems Analyst
NAV 2009 R2 (6.00.34463)

Answers

  • Options
    DenSterDenSter Member Posts: 8,304
    When the FINDSET command is executed, NAV does not know how many records will be in the record set. What it then does is grab enough memory space to hold the maximum number of records, as is defined in the 'Record Set" parameter (which you can find under file, database, alter, on the Advanced tab). The number of records is within the filters, not the whole table.

    So let's say you have a record variable for Item Ledger Entry, called ILE. Say you have a filter on the Item number, and there are 20 records for that item. Say your 'Record Set' parameter is set up with a value of 50. NAV will grab enough memory for 50 records, and it doesn't need all of it.
  • Options
    Mike_HWGMike_HWG Member Posts: 104
    Excellent, thanks. My situation was:

    ILE has 200 records total
    I have a filter on item number, there are 20 records for that item
    Record Set is equal to 50

    Conclusion:
    NAV will allocate memory for 50 records if I use FINDSET. Since 20< 50, FINDSET is the optimized choice.


    :thumbsup:
    Michael Hollinger
    Systems Analyst
    NAV 2009 R2 (6.00.34463)
  • Options
    DenSterDenSter Member Posts: 8,304
    I'm not really sure if there is much of a difference between FINDSET and FIND('-') when reading just 20 records. Usually performance optimization doesn't become important until there are many many many more records involved than that.
  • Options
    Mike_HWGMike_HWG Member Posts: 104
    :lol: That number was just for example. I'm actually in process of hunting down optimization issues that is reading our ILE for customer/item matches within a week of sales... about 47 million combinations on 14 million records... our report has averaged a 4 hour runtime each week for the past 3 months, but exploded to 2.5 days this week :-# While I'm looking at what the culprit is, I might as well do some optimization tuning along the way, right?

    All in a day's work... :|
    Michael Hollinger
    Systems Analyst
    NAV 2009 R2 (6.00.34463)
  • Options
    kinekine Member Posts: 12,562
    if you are looping through this big set of records, I think best way is to use some SQL select or some view and connect it into NAV to proces the data. But it depends on what you are doing. With too many records, any loopy-loopy processing will take too much time. But SQL is used to work with many records in big datasets, thus it could help to create some good query...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    Mike_HWGMike_HWG Member Posts: 104
    Well, for some reason our server started behaving again and the export is back down to its normal runtime... I haven't changed a thing :-k

    One of the things we haven't done with this export that I'm looking into doing is temporary tables.
    With another similarly-coded export, we load a separate 'Reporting ILE' table. Here's a basic overview. Note that the export needs to be in the format of Items that had sales, Customers that had sales, Sales information per item/customer combination.
    Filter the ILE for Sales within the date range
    FOR EACH filtered ILE record 
    BEGIN
         IF (the ILE."Item No." is a qualified Item) AND (the ILE."Source No." is a qualified Customer) THEN
              Copy the ILE record to the Reporting ILE
    END
    
    FOR EACH unique item in the Reporting ILE record
         Export each item with pertinent information
    
    FOR EACH unique customer in the Reporting ILE record
         Export each customer with pertinent information
    
    FOR EACH Reporting ILE record
         Export each sales detail, grouped by customer/item combination
    

    Transitioning to this code (instead of reading the whole darn ILE each time) will cut the time down by probably 3/4 from what we've seen.

    Our ultimate goal is to just transfer the whole thing to a SQL subscription report. Unfortunately, our SQL gurus are tied up with other projects at the moment and I'm just knowledgeable to do some basics until I can get into some winter classes.
    Once we get the code transferred, I'm expecting the export will run in sub - 15 minute times =P~
    Michael Hollinger
    Systems Analyst
    NAV 2009 R2 (6.00.34463)
Sign In or Register to comment.