Long time to perform loadAllRecords

Classic Servoy
1

I have a table with about 55000 records. If I do a search on a form (find(), search()) with no matching result an I cancel the search, it needs about 110 seconds (!) to perform the loadAllRecords(). In this time, the UI is blocked.
There is no difference whether I do it with foundset or controller.

Is this a normal behavior?

2

Gabriel,

Is this different from the initial load of your form?

Have a look at the Performance Data tab on the admin page.
Your slow queries should show up there.

Maybe your indexes are not optimal for these queries.

Rob

3

Hello Rob

Yes, it is different. The initial load works fine.

I had a look to the Performance Log.
There ist 192 times the same select statement with type ‘load foundset’. the only differences in the statements are the alias names of the main and relation tables.
Do you have a idea, why this select will be performed so often?

4

Gabriel,

Not without looking at the solution.
What does the load-all-records code in your solution?

Rob

5

Hello Rob

I use the Servoy-Framework. I call this method from the popmenu of the form.
This is the code:

function MeineLeistungenAktuelleWoche() {

	var aktRecID = leistungen_id;	// ID der aktuellen Leistung merken

	if (controller.find())	{
		
		var aktDatum = new Date();	// Aktuelles Datum
		
		var datAnfang = globals.Base_DatumBOW(aktDatum);	// Erster Tag der Woche
		var datEnde = globals.Base_DatumEOW(aktDatum); 		// Letzter Tag der Woche
		
		// String für Filter zusammensetzen
		var datFilter = (datAnfang.getDate()  < 9 ? "0" : "") + (datAnfang.getDate()  + 1).toString() + "." + 
		                (datAnfang.getMonth() < 9 ? "0" : "") + (datAnfang.getMonth() + 1).toString() + "." + 
		                 datAnfang.getFullYear().toString() + 
		                 "..." + 
		                (datEnde.getDate()    < 9 ? "0" : "") + (datEnde.getDate()  + 1).toString()   + "." +
		                (datEnde.getMonth()   < 9 ? "0" : "") + (datEnde.getMonth() + 1).toString()   + "." +
		                 datEnde.getFullYear().toString() + 
		                 "|dd.MM.yyyy";
		
		mitarbeiter_id = "=" + globals.AktUserUI;
		leistungsdatum = datFilter;

		controller.search();
		
		if (foundset.getSize() == 0) {

			application.beep();

			controller.loadAllRecords();

		}
	}
}
6

Gabriel,

I can’t see anything wrong with the code and can’t explain why this would be so slow.

You can try to analyze this some more by using the profiler which is built in with Servoy 6.

Alternatively, if you clear all performance data in the admin page just before the loadAllRecords call, you can see the actual queries resulting from that call.

Hope this helps,

Rob