FILEREAD as Lookup Table

[Bolt]

Yes, once code found, it JMPs to FILECLOSE et al.
Structure.EndOfFile is what I was looking for in Code not found scenarios.
I suppose .FilePointer = .FileSize would have done the trick as well.

[Bolt]

I am trying to implement this concept of looking up in a very large table 10K+ lines, and it (obviously) takes a while to find data in the later rows.

This time, the lookup "column" data is all numeric, but much larger than a DWord can hold, like UPC codes.  The numbers will not be consecutive.  What are some tricks I can use to speed it up?  Sort by first column, create indexed steps, etc?  How do I do such things on a BRX?

[Bolt]

...if the codes are in order, or you can put them in order, at most 9 queries should find the record if random access is possible.

How would you do this with 9 queries?

[Controls Guy]

How would you do this with 9 queries?

I understood your reply #9 to say there were 289 rows, and 2^9 = 512 > 289.

So, assuming the records have been sorted in order, you check the 50% record, or in this case, either 256 or 145.   if the value you're looking for is less than the retrieved value, then go to 25%, or 72.   If >, then go to 75%, or 217.   Based on that comparison go half way to the endpoint or the previous test point, and so on.   Since 2^9 > 289, it is guaranteed that you'll find the desired register in at most 9 attempts.    Now since you're saying 10,000 records, it might take as many as 14, if they're sorted.

[Bolt]

Gotcha.  I am now re-applying the concept to a much larger project, and need to optimize.  I follow your split in half, check if high or low, I just struggle to apply it to ladder logic.

How do I count the number of rows the file contains to do the divisions?  Do I need to do that with how many Bytes the file is large?  That would kind of require each row to be of the same length, a restriction I'd rather not put on the data source at this time.

[Controls Guy]

Is the data static or is it always changing?

It may not be practical to physically sort a table that large quickly enough if it's constantly changing, and a sorted table, premised on the idea that the tabular data is cast in stone, or at least changing only infrequently, is necessary for my 2^n query bound.

Another approach might be to create an index of pointers to the relative rank of the table records.

[Bolt]

The data is changing on an interval, daily at a minimum, 2 or 3 time daily at most.  When the data is changed, it would be possible to take some time to (re)generate an index, subdivisions, etc.

[Controls Guy]

And what's the record format again?   Very large numbers stored as text?

What are you doing with the data once you retrieve it?    Do you have to do math on it?   Display on HMI?  Communicate it via comms?

Would 32-pt float precision be adequate?   Are the numbers signed or unsigned?

Generating a sorted index on 10K strings or floats (or DIY 48 or 64-bit integers) should be quite feasible in a BRX.

You could even do RANK() in Excel and make the index part of what you import, depending on how the data comes to you.   Or even presort the data outside the PLC and go back to using the 2^n search.

[Bolt]

And what's the record format again?   Very large numbers stored as text?

15 digit numbers, followed by a (alphanumeric) code, in a CSV file

What are you doing with the data once you retrieve it?    Do you have to do math on it?   Display on HMI?  Communicate it via comms?

Enable output(s) based on looked-up code

Would 32-pt float precision be adequate?   Are the numbers signed or unsigned?

Unsigned, float not accurate enough

Generating a sorted index on 10K strings or floats (or DIY 48 or 64-bit integers) should be quite feasible in a BRX.

I'm all ears (eyes).  48 bit would not be enough, 64 would.  This would be all this BRX needs to do, so space shouldn't be an issue (famous last words...)

You could even do RANK() in Excel and make the index part of what you import, depending on how the data comes to you.   Or even presort the data outside the PLC and go back to using the 2^n search.

It would be all automated, no human interaction, let me dig into what the data source can pre sort/filter.  The data can be sorted by column, so should not be a problem there.  The 2^14 search is fast enough for me, just not sure how to implement it.

[franji1]

If you actually need to MODIFY the "database", random CSV will not work.  I thought the database was static (error lookup).

CSV only works for complete import/export of the entire database.  Files are like RAM, so you just cannot just insert a record 28 characters long, or replace one of 20 characters with one that is 22 characters.  Flash ROM/file systems don't work that way.  This is why databases are fast and $$$ and LOTs of code.

The only way this could work is if you had a FIXED record format.  Think COBOL 1968.  Every record is a FIXED 40 characters (64, whatever).  Hence, record at 40, 80, 120, etc.  You would need to maintain a list of DELETED records in a separate file.  And a list of KEY->INDEX values in a third file.  You could maintain the KEY->INDEX in the RAM drive, or even a UDT block, that you can easily regenerate on a moment's notice (but the MAIN TABLE will NOT need to be rewritten).  Deleted records would also need to be flagged in the primary file (think FAT file system), this way you could re-generate the DELETED records quickly by looking at that flag one time at startup (the same time you re-generate the Key->index map).

it's do-able.  What's cool about Do-more is you can tweak the .TimeSlice to trade off calendar time with scan time.  Higher .TimeSlice (in microseconds), the more database processing you get done per PLC scan, at the price of bumping the PLC scan.  A lower .TimeSlice means less time doing database work every scan, so your PLC scans are faster, but your database operations take longer calendar-time-wise.

[Controls Guy]

Sounds like this is a new project, if I'm reading his post correctly.

[Controls Guy]

It would be all automated, no human interaction, let me dig into what the data source can pre sort/filter.  The data can be sorted by column, so should not be a problem there.  The 2^14 search is fast enough for me, just not sure how to implement it.

Start at 5000.  Depending on too high / too low, then check 2500 or 7500, then 1250, 3750, 6250, 8750, etc.   Just keep dividing the range in half and the half with your value becomes the new range.   Rinse and repeat till you test and hit the target value.

[Bolt]

If you actually need to MODIFY the "database", random CSV will not work.  I thought the database was static (error lookup).

Sorry, I'm starting a new project with the old code from last year.  That works great to this day, looking up error codes.  Perhaps I should have started a new post...

CSV only works for complete import/export of the entire database.  Files are like RAM, so you just cannot just insert a record 28 characters long, or replace one of 20 characters with one that is 22 characters.  Flash ROM/file systems don't work that way.  This is why databases are fast and $$$ and LOTs of code.

The only way this could work is if you had a FIXED record format.  Think COBOL 1968.  Every record is a FIXED 40 characters (64, whatever).  Hence, record at 40, 80, 120, etc.  You would need to maintain a list of DELETED records in a separate file.  And a list of KEY->INDEX values in a third file.  You could maintain the KEY->INDEX in the RAM drive, or even a UDT block, that you can easily regenerate on a moment's notice (but the MAIN TABLE will NOT need to be rewritten).  Deleted records would also need to be flagged in the primary file (think FAT file system), this way you could re-generate the DELETED records quickly by looking at that flag one time at startup (the same time you re-generate the Key->index map).

By "changing" the data, I mean at a certain interval, a new CSV file will be pushed to the BRX.  It will overwrite the previous one, containing the new sorting allocations.

it's do-able.  What's cool about Do-more is you can tweak the .TimeSlice to trade off calendar time with scan time.  Higher .TimeSlice (in microseconds), the more database processing you get done per PLC scan, at the price of bumping the PLC scan.  A lower .TimeSlice means less time doing database work every scan, so your PLC scans are faster, but your database operations take longer calendar-time-wise.

Yep, that's part of it.  I used the code in DmSim, and it found the 16,000th random record in well under 2 seconds.  Slapped the same code into a BRX running many other things, and it took over 4 minutes.  I didn't tweak the slice there, as I didn't want to mess with the running processes.  100 us out of 5 ms is not much processing time  I think much of my Sim success was also related to the PC's greater processing capacity?

[Bolt]

Start at 5000.  Depending on too high / too low, then check 2500 or 7500, then 1250, 3750, 6250, 8750, etc.   Just keep dividing the range in half and the half with your value becomes the new range.   Rinse and repeat till you test and hit the target value.

But how do I actually jump to the 5,000th row?  Would I need to fix my CSV's row length so simple math can be used to determine the starting offset?  I'm trying to keep this as flexible as possible.  Also, the 10,000 number was arbitrary, it will change over time, 6,000, 8,000, etc.

[Controls Guy]

Sorry, forgot yoy were doing file ops as opposed to having the whole table in BRX memory.  More suggestions tomorrow.