I must create in one only operation from 20.000 to 30.000 records.
Servoy Server employs approximately 90 minuteren from a local Client on Server. Too many.
Exists a method in order to execute this operation more fastly?
Thanks in advance.
If your (O)RDBMS supports it I would suggest a stored procedure.
These kinds of massive actions you want to use on the server anyway.
So or by a sql script or a stored procedure.
Hope this helps.
which version of servoy are you using?
We have improved this massive inserts a lot in the latest builds.
Do you use a transaction?
Please check the db performance tab in the admin pages where you time is going to.
Use version RC7. Just that one on which you have made the improvements.
Yes. Use the transactions.
The Server gives back statistics with 1 ms to record but the Client, apparently, employs at least 200ms/Records.
A difference of times that I do not explain myself completely.
I was the one that mainly had problems using large foundsets and they are fixed, as far as I could test.
I think to see what goes wrong in your case, we need more information. What database are you using and how does the code look like (more or less)?
network latency and does it query for more data?
For example right before you are going to insert all those records, clear the db performance data. Then do you thing.
This way you can exactly see what that new records creating does.
For example right before you are going to insert all those records, clear the db performance data. Then do you
thing.
I have already made this in order to measure the performances well.
I think to see what goes wrong in your case, we need more information. What database are you using and how does
the code look like (more or less)?
We use MySql 4.0.20a NT-max version. Code that uses the method is following:
http://www.comune.nuoro.it/Servoy/ForumNewRecordCode.txt
(They are not successful to insert the Code in PhPBB because it gave back an error)
They are sure that the first objection will regard controller.setSelectedIndex() but also we have tried getRecord() without to obtain much large increment of the performances.
We have executed other tests. The time employed from the Server is:
Total Time (mm:ss:ms): 00:00:139
Count: 40
Avg Time (mm:ss:ms): 00:00:003
SQL Statement:"select voti.votiid from voti where voti.fkid_lastrilev = ? and voti.fkid_quesito = ? order by voti.votiid"
Total Time (mm:ss:ms): 00:00:047
Count: 43
Avg Time (mm:ss:ms): 00:00:001
SQL Statement:"insert into voti ( votiid, fkid_tornata, fkid_lastrilev, fkid_sezrilev, fkid_coaliz, fkid_lista, fkid_candid, voti_ottenuti, tipo_voto, flag_checkvoti, fkid_seztornata, flag_txtvoti, fkid_coalizballott, flag_newapparentam, num_collegio, voti_no_refer, sez_rilevata, voti_solo_capocoaliz, voti_nonass, fkid_quesito) values ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )"
The method we have modified it therefore:
http://www.comune.nuoro.it/Servoy/LastModifyNewRecord.txt
On the Client to have been medium of 12,5 second ones.
Hi
i changed youre code a bit:
if( application.getActiveClientCount(true) == 1 )
{
var NumSections = databaseManager.getFoundSetCount(rilev_tornata_to_rilevquesito);
var messaggio = plugins.dialogs.showInfoDialog( "Creazione record", "Verranno creati " + NumSections + " records.\nPremere OK per procedere.", "OK", "Annulla");
if ( messaggio == "OK" )
{
databaseManager.startTransaction();
var Inizio = application.getTimeStamp();
//Inizializzo la progress bar
elements.ProgressBar.visible = true;
elements.ProgressBar.value = 0;
elements.ProgressBar.string = 'Creazione record Voti...';
elements.ProgressBar.stringPainted = true;
application.updateUI();
var StepProgBar = 100/NumSections;
var theFoundset = forms.M1S5S1_DataEntry_Sezioni.foundset;
for ( j = 1; j <= NumSections; j++)
{
var Record = theFoundset.getRecord(j);
Record.sch_bianche = 0;
Record.sch_nulle = 0;
Record.sch_nonass = 0;
Record.check_sezione = "No Dati";
Record.check_error = "<html><body><center><font face='Arial' size='3' color='red'><b>No Dati</b></font></center></body></html>";
var relRecord = forms.M1S5S1_DataEntry_Sezioni.sez_rilev_to_votiref.getRecord(forms.M1S5S1_DataEntry_Sezioni.sez_rilev_to_votiref.newRecord(false,false));
relRecord.voti_ottenuti = 0;
relRecord.voti_no_refer = 0;
//Aggiorno la progress bar
if( j%50 == 0)
{
elements.ProgressBar.value = j * StepProgBar;
elements.ProgressBar.string = 'Record creati... (' + parseInt((j * StepProgBar)) + '%) - ' +
j + ' di ' + NumSections;
application.updateUI();
}
}
elements.ProgressBar.value = NumSections * StepProgBar;
elements.ProgressBar.string = 'Record creati... (100%)';
application.updateUI();
databaseManager.commitTransaction();
var Fine = application.getTimeStamp();
plugins.dialogs.showInfoDialog( "Fine Creazione",
"Sono stati creati " + (j-1) + " records in " + (Fine-Inizio) + " ms.",
"OK");
elements.ProgressBar.visible = false;
elements.btn_CreaVoti.enabled = false;
}
}
else
{
plugins.dialogs.showWarningDialog( "Avviso critico!",
"La Funzione non e utilizzabile perche vi sono " + application.getActiveClientCount(true) +
" client collegati",
"OK");
}
don’t know if this really speeds up the problem but i do find a few things strange
1> you also update a record (but i don’t see update statemenst in youre log, or is that record really never changed?
2> you did create a new related record but you didn’t change the selection, but you did set some data through that relation.. I think you meant to change the just created record yes?
3> also you started a transaction before the ask for ok.. so if that ok was not given you had a started transaction without an end..
4> i changed the calls to updateUI a bit. This really shouldn’t be called often!! this is expensive!
you also update a record (but i don’t see update statemenst in youre log, or is that record really never changed?
I have not brought back some voices that had times of zero ms.
you did create a new related record but you didn’t change the selection, but you did set some data through that relation.. I think you meant to change the just created record yes?
The relation in this case to be of type One to One.
also you started a transaction before the ask for ok.. so if that ok was not given you had a started transaction without an end..
True, but I assess that there is exclusively one client connected.
We will change this.
i changed the calls to updateUI a bit. This really shouldn’t be called often!! this is expensive!
They are aware of this in fact we update interface every 5% record creation in order to update the progress bar.
Thanks however