[Solved] - Comparing array values with...

Woooohooooooo !!

Found a solution to do the comparison in less than 30 seconds

I'll post it tonight once at home.

Cheers

Mathias

Hello my friends,

As promised, I'll detail the solution I found, just in case someone else meets the same problem one day.

WHAT WAS THE PROBLEM

The problem was to compare a list of 25.000+ entries with another list of more or less 25.000 entries to find new ones.
One list comes from a web page, the other one from a local database, and the goal was to determine if the list from the web contained new elements.

FIRST APPROACH OF THE PROBLEM
The first approach (which worked) was the following :
1- retrieve the data from the web
2- split the huge coma separated string into an array of n elements
3- loop through the elements of the array with, for each one, a query to the database to see if the element was found or not

something like :

var
    i, known : Integer;
begin
    for i := 0 to Length(sku_array) - 1 do
        begin
            known := SQLExecute('SELECT COUNT(id) FROM asset WHERE asset_sku = "'+sku_array[i]+'"');
                if known = 0 then <some code> else <some other code>;
        end;
end;

This works fine, the only problem being that this process took a long time to complete.
On my machine (core i7, 16Go of Ram, SSD hard drive), it took this morning 852 seconds- almost 15 minutes.....

SECOND APPROACH : COMPARING DATA IN MEMORY
To speed up the process, I imagined comparing data in memory and, instead of launching 25.000+ queries to the database (one for each element of the array), use just one big query to get in memory all the elements of the database to compare with the elements of the array already in memory. That should be quicker right ?
So the idea was :
1- retrieve the data from the web and split the string to get an array of 25.000+elements
2- create a dataset of 25.000+ elements with just one query on the local database
3- while the query still add elements (not Eof) compare each element of the array with the result of the query
This gave me something like :

var
    sku_array : Array of String;
    Results : TDataSet;
    i : Integer;
begin
    SQLQuery('SELECT asset_sku FROM asset',Results);
        While not Results.Eof do
            begin
                for i := 0 to Length(sku_array) - 1 do
                    begin
                        if sku_array[i] = Results.FieldByName('asset_sku').AsString then <some code> else <some other code>;
                    end;
                Results.Next;
            end;
    Results.Free
end;

Result ? The solution was worth than the problem because I create a loop of 25.000 iterations checking 25.000 elements which gives me a total of 625.000.000 checks to perform !!! Each of the 25.000 elements of the array was checked 25.000 times against the local data and, even in memory, this takes a huge amount of time. Honestly I killed the process each time before the end, so I have no timing to give you as a comparison....
The main problem of this approach ? I should have written a function to stop the loop once a match have been found, to avoid checkinh useless results.

FINAL APPROACH : THE SOLUTION WAS...
Idealy, I wanted to use native functions that would do the comparison loops without my coding them. Something like
if element in array then...
But the IN array does not seem to be implemented yet (Dmitry will correct me if I'm wrong) so I used something else.
1- get the data from the web, which gives me a coma separated string
2- transform that simple string into a TStringList with : OnlineList.CommaText := string
3- create a dataset of local results with just one query
4- fill a second TStringList with the results of the query
5- now I have 2 TStringList, time to compare their elements but, instead of looping again through the results, use a native function : IndexOf().
This function of the TStringList class (which is a sub-class of the TSrings class) gives you the index in a list of a string (array and lists are numbered from 0 to n). If no index is found, meaning the string is not present, then the function returns -1
This gave me :

begin
    SQLQuery('SELECT asset_sku FROM asset',Results);
    localList := TStringList.Create;
        while not Results.Eof do
            begin
                localList.add(Results.FieldByName('asset_sku').AsString);
                Results.Next;
            end;
         Results.Free;

    onlineList := TStringList.Create;
    onlineList.CommaText := <some code to get the online string>;
    try
        try
            for j := 0 to onlineList.count - 1 do
                begin
                    if localList.IndexOf(onlineList.strings(j)) = -1 then <some code because this is a new element>);
                end;
        except
            ShowMessage(Exceptionmessage);
        end;
    finally
        localList.Free;
        onlineList.Free;
    end;
end;

Same amount of online data : 25.000+ elements
Same amount of local data : more or less 25.000 elements as well
Treatment and comparison time to find all the new elements of the online list : 27 seconds on the same machine.

I'm very pleased because this is nearly a 30 times decrease in treatment time and now a acceptable process to run and perform that check on a daily basis.

Hope this was interesting and clear enough to understand.
Dmitry, do not hesitate to comment or correct if you find any flows or better approach.

Cheers

Mathias

Thanls Dmitry

Was there another solution according to you ?

Hi Vasco,
There may be some specific reasons why you can't do it this way - but rather than use 2 tables (pre-invoice and final-invoice - both presumably with a similar structure), could you not have just one table (invoice) and then use a status indicator to show if it is pre-invoice or final-invoice (or any other status you may want)?  The attachement is a very quick example of how it could work.
Just a thought.
Derek.