Loop through related records in different vendor databases

Classic Servoy
1

As a follow up to my post two days ago regarding relations between different vendor databases for anyone else who needs to do this:

I mentioned then that I was thinking of just looping through the related records since it appeared that I couldn’t use a ‘ConvertFoundSet’ method. One thing I found though, which of course again makes sense, is that you can’t do the ‘normal’ looping of ‘foundset.getRecord(i)’. That method clearly works on just a ‘straight’ SQL query and the records never have to be shown. So that meant that I would have to loop using 'foundset.setSelectedIndex(i). In this method you actually ‘loop’ through (and show) every record in your total foundset (not just your 200 shown records) and I was thinking that this would be very slow. I wasn’t asking the method to do very much (it was a one-to-one relationship and if the relationship existed, set the contents of two columns in my form table to their related counterparts). Nevertheless I had just over 10’000 records and thought it would be a while. But somewhat to my surprise it took seconds to complete. So if anyone else has to do something similar ever don’t be put off using a straight Servoy method to do it. As Larry David would say: “Pretty, pretty, pretty good!”

This was my method:

var totalRecCount = databaseManager.getFoundSetCount(foundset);
for (var i = 1; i <= totalRecCount ; i++)
{
    foundset.setSelectedIndex(i);
    if (databaseManager.hasRecords(onc_np_to_patients))
    {
        in_lpp = 'in lpp';
        patient_id = onc_np_to_patient.patient_id;
    }
}

I then went on and did a similar loop through but this time to the MUCH larger, related MS SQL Server db. Here there are over 1’000’000 records AND I wanted to do a count of the related records and set a field in my ‘parent’ record to that number. Not surprisingly this took a lot longer. It was still though very doable: about half an hour and just around 15% of the records had a match. Very, very cool if you ever have to do such a thing.

I know its kind of an esoteric, oddball thing to need to do for most developers (relate records in two or more entirely different databases across an entire table) but if anyone ever needs to do it, Servoy is pretty good in doing it! :) Certainly it is plenty fast enough that I am going to use it in a method for my datamanagers to run on the weekly reports we get (the 10’000 records were a year’s worth), saving them hours of dataentry without errors.

John

2

Hi John, the reason for this speed is that you remain in memory. Like when you use a foundset instead of the controller there is no GUI stuff involved.

I don’t remember when but I do remember that I once wrote an article about it (and it was in my presentation in Amsterdam).

3

Actually Marcel in this case I don’t think it is in memory. In normal situations where you can make a ‘real’ relationship within the same database (and then use foundset.getRecord(i)) then it is all in memory and there is no GUI involved. But in this case there isn’t a ‘real’ relationship as it is between two totally unconnected databases so each record has its own ‘relationship’ purely through Servoy, I think. Servoy might hold the foundset of 200 records in memory but I don’t think even that is done for ‘connecting/relating’ to the other database.

I think Servoy is literally looping through each record more or less one at a time and sending a query for each one. In running the method I can watch the sidebar scroller slowly working its way down the page and the GUI being redrawn with each group of records being processed (I deliberately left the form very bare for less work and it appeared as though Servoy was processing about 4 or 5 at a time - it was certainly redrawing about 4 or 5 at a time). In the first example with the much simpler one-to-one relationship it happened too fast to even see and all 10’000 records were processed in a few seconds.

Again I’m not sure about this but I think that is what is happening. I think that’s why ‘foundset.getRecord(i)’ didn’t work in this case. What happened when I used that was that if I started on a record that had a ‘relationship’ with the other database (i.e. that record had a common key with the other database) then the getRecord function worked for that one record. But then it would loop through all the other records but without ‘discovering’ those records relationships. The getRecord function does indeed process everything in memory, there is no GUI involvement at all and that is the ‘preferred’ way to do loops in almost all cases. Generally you don’t need or want GUI involvement when you are performing looping functions. SetSelectedIndex(i) on the other hand I think always does involve the GUI but even there in most situations it happens too fast to ‘see’ that the GUI is doing anything.

Anyway I’m not sure but I think that’s what is going on. But maybe I just want to think that is what is happening because it is kind of cool if it is the case that Servoy can do it that fast. :D

John