looping through the records slowly.

[ashutoslenka426]

Hi All,

I have some 1.5 lakh records in db . I am looping through each record and again doing find operation to find all the duplicate records . I am doing this operation in the batch processor . This operation is running very slowly . Why ? .How can I make it to run fast ? .

Code: Select all

var XXXFs = datasources.db.YYYYY.XXXX.getFoundSet();
XXXFs.loadAllRecords(); // loading the foundset.
for(recIndex = 1 ; recIndex <= XXXFs.getSize() ; recIndex++){ /
XXXRec = XXXFs.getRecord(recIndex);
}


This is in Servoy 7.4.2 and DB is postgres .

[Ruben79]

Maybe it's an option to let the database do the work?

Code: Select all

SELECT mycolumn,
COUNT(mycolumn) AS numoccurrences
FROM mytable
GROUP BY mycolumn
HAVING ( COUNT(mycolumn) > 1 )

[juan.cristobo]

It seems that forEach is very fast:

Code: Select all

var XXXFs = datasources.db.YYYYY.XXXX.getFoundSet();
XXXFs.loadAllRecords(); // loading the foundset.
XXXFs.forEach(function(rec, recIndex, foundset) {
        //every record is in rec variable
        //the current index is in recIndex variable
    }
);


[ROCLASI]

Hi Ashutos,

Querying one record at a time to loop through your records and do a comparison is always much slower than sending just 1 query to the database to do the same thing.
So Ruben's suggestion is the fastest option.
However since Servoy requires the PK value to fetch any record you do have to add the PK in your SELECT list. That of course kills the method to find any duplicates because you have to add the PK also to the GROUP BY (and PK's are by definition unique).
So you need to rewrite that query to find the duplicates and then find the corresponding PK's like so:

Code: Select all

foundset.loadRecords("SELECT myPK \
                    FROM myTable \
                    WHERE myValueColumn IN ( \
                        SELECT myValueColumn \
                        FROM myTable \
                        GROUP BY myValueColumn \
                        HAVING count(myValueColumn) > 1 \
                    )");

Essentially a one-liner
Hope this helps.