Clarification on FINDSET operation - number of records read

Mike_HWG · 2012-09-10

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.

DenSter · 2012-09-10

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.

Mike_HWG · 2012-09-10

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:

DenSter · 2012-09-10

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.

Mike_HWG · 2012-09-10

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...

kine · 2012-09-10

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...

Mike_HWG · 2012-09-10

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~

Clarification on FINDSET operation - number of records read

Answers

Categories