relation.deleteAllRecords() really slow

Classic Servoy
1

I have the following code

	//remove events from database	
application.output(new Date()+' 1');
	aktiviteit_to_aktiv_2_medew.deleteAllRecords();
application.output(new Date()+' 2');
	var queryDossiers = "SELECT dossier_id FROM aktiv_2_dossier WHERE aktiviteit_id = "+aktiviteit_id;
	var datasetDossiers = databaseManager.getDataSetByQuery(controller.getServerName(), queryDossiers, null, -1);
	var aantDossiers = 	datasetDossiers.getMaxRowIndex();
	var woordKomma = '';
	var medewID, nogeenquery,nogeendataset;
application.output(new Date()+' 3');

The result of the output statements

Tue May 08 2007 10:21:50 GMT+0200 (CEST) 1
Tue May 08 2007 10:21:54 GMT+0200 (CEST) 2
Tue May 08 2007 10:21:54 GMT+0200 (CEST) 3

aktiviteit_to_aktiv_2_medew.deleteAllRecords() Only has to remove 1 or 2 records in this particular case. It takes about 4 to 5 seconds to do. If there are no records to delete there is no delay. The total amount of records available in table aktiv_2_medew is 24.
Is this a bug? This results in a quite annoying delay for the clients.

Servoy Developer
Version R2 2.2.7-build 339
Java version 1.5.0_11-b03 (Windows 2003)

2

does the target table have relations that also needs to be checked/deleted?

Then it needs to load all those relations to also try to delete those

What you can do is that right before the moment you do the delete you first go to the admin pages and then the performance tab and delete all the queries that are done. Then press delete and look after it is done what queries are generated for youre delete and how long they did take.

3

jcompagner:
does the target table have relations that also needs to be checked/deleted?

Yup, it does…it removes records from a table uren with 600.000 records. But still…5 seconds? to remove 2 records in table of 24, and 2 records in a table of 600.000?
According to the statistics Servoy does twice a select-query to table uren which about 03:406 seconds and twice a delete-query of 01:250 seconds…which acounts for the 5 seconds.
Why the Select-query?

Any other faster suggestions? I dont like using RAW-sql becoz of the flushallclients thing.

4

please make sure that you have the right indexes set on the query params (for delete mostly pk but could be fk).

5

jcompagner:
please make sure that you have the right indexes set on the query params (for delete mostly pk but could be fk).

Trying to work on that…but my Sybase Index Consultant keeps crashing on me :shock:

Eitherway thankx for pointing me into the right direction