Superfast omit of many records

Questions, tips and tricks and techniques for scripting in Servoy

Superfast omit of many records

Postby Peter de Groot » Wed Feb 20, 2013 9:40 am

Something I want to share,

One of our users was complaining that the printing of some selected records takes very long to complete, user needs to print 4 out of 256 records...

The code we use to do this is:

Code: Select all
var v_fs, v_form;

v_fs   = forms.exp_poLinesHeatnrList.foundset.duplicateFoundSet();

for (var i = v_fs.getSize(); i > 0; i--) {
   var v_rec = v_fs.getRecord(i);
   if (!v_rec.checkbox) { //checkbox not checked
      v_fs.omitRecord(i);
   }
}

Doing it this way it takes about 21 seconds to omit the not selected record from v_fs :oops: ,

but when doing it like this:
Code: Select all
var v_fs, v_form, v_arrSelected;

v_fs          = forms.exp_poLinesHeatnrList.foundset.duplicateFoundSet();
v_fs.multiSelect   = true;
v_arrSelected   = new Array();

for (var i = 1 ; i <= v_fs.getSize(); i++) {
   var v_rec = v_fs.getRecord(i);
   if (v_rec.checkbox != 1) {//checkbox not checked
      v_arrSelected.push(i);
   }
}
v_fs.setSelectedIndexes(v_arrSelected);
v_fs.omitRecord();


The omit is done nearly instantaneously :shock:

regards,

Peter
User avatar
Peter de Groot
 
Posts: 215
Joined: Thu Jan 10, 2008 8:38 pm
Location: Not sure...

Re: Superfast omit of many records

Postby jasantana » Wed Feb 20, 2013 12:25 pm

Good one. Thanks for sharing.
Best regards,
Juan Antonio Santana Medina
jasantana@nephos-solutions.co.uk
Servoy MVP 2015
Servoy 6.x - Servoy 7.x - Servoy 8.x - MySQL - PostgreSQL - Visual Foxpro 9
User avatar
jasantana
 
Posts: 555
Joined: Tue Aug 10, 2010 11:40 am
Location: Leeds - West Yorkshire - United Kingdom

Re: Superfast omit of many records

Postby jcarlos » Thu Feb 21, 2013 10:18 am

Thank you for sharing too :-)
jcarlos
 
Posts: 578
Joined: Thu May 04, 2006 8:55 pm
Location: Palo Alto, California USA

Re: Superfast omit of many records

Postby Manolo_Etec » Thu Feb 21, 2013 5:52 pm

Thanks for sharing too. :D
Manuel López
Etec Programación SL
manuel@etecsoft.com
http://www.etecsoft.com
User avatar
Manolo_Etec
 
Posts: 66
Joined: Fri Sep 12, 2008 3:58 pm

Re: Superfast omit of many records

Postby Harjo » Thu Feb 21, 2013 9:27 pm

be aware, that if you omit more than 200 records this way, Servoy will (auto) create a temp table first, because of performance.
So be sure that you have create table rights on that server, or else you'll get a database permission error

it would be nice if we could tell Servoy, to point to a specific server, for that, because in our case we have one database where we only have read access to.
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: Superfast omit of many records

Postby pbakker » Fri Feb 22, 2013 1:54 pm

It would be nice if we could tell Servoy, to point to a specific server

The temp table needs to be in the same database as the database against which the SQL is fired, so the temp table can be joint in the SQL statement.

If the temp table would reside in a different database, there would be no point of having the temp table in the first place

As for the technique: Maybe I'm missing the point, but I think what would be even faster would be looping over the original foundset to find all your "selected" records, get the PK Value(s) of each record and insert it into JSDataSet, then create an new foundset and load it with the JSDataSet: This will require no database interaction at all.

Paul
pbakker
 
Posts: 2822
Joined: Wed Oct 01, 2003 8:12 pm
Location: Amsterdam, the Netherlands


Return to Methods

Who is online

Users browsing this forum: No registered users and 7 guests