PostgreSQL performance

Classic Servoy
21

Hi Robert (Ivens)

Sorry, I meant (as you) the background of form parts.

Best regards, Robert

ROCLASI:
Hi Robert,

Robert Huber:
PS: With 3.5.5 the background bug (white instead of Mac’s grey (striped in Tiger)) is back :-(
But now on all elements. Did you also notice that?

I only noticed the form background color. You are seeing issues with form elements as well ?

22

Hi Patrick

I’m pretty sure what you wrote is the answer to our problem. I somehow expected the foundset to be replaced with the next 200 records. But of course in the GUI I can see that the next 200 records are added to the current foundset. So this is probably our “leak”.

I did rewrite the code, so that I always load 200 records into the foundset and clear it before loading the next 200. I tested it with about 20’000 records but cannot really confirm that memory usage says stable so far. So I startet the “big” process now and let you know about the result.

Thanks a lot for the hint!
And regards

23

That’s exactly what I meant. The only problem I had when doing things like this was on a table with large blobs. For some reason, memory was not released in this case. Nobody really understands why… So if your table has no blobs, this should work now.

24

Hi Patrick

Memory usage still grows :(. And we have no blobs.
Here is the current implementation of the loop:

while (true)
{
	controller.find();
	id = '>=' + startId;
	controller.search();
	...	
	for (var i = 1; i <= 200; i++)
	{
	...
	}
	databaseManager.saveData();
	foundset.clear();
	startId += 200;
}

Additionally I assume find() and search() after 200 records might slow down the application. Is this true? Should I use a bigger step (like 10000)?
What is your experience?

Regards!

25

Hi Birgit,

  • You say you placed databaseManager.startTransaction() at the beginning of the loop. I hope that is before the loop because you want to use 1 transaction for the whole method.
    Ditto for databaseManager.commitTransaction(), this should be after the loop.
    This way you get 1 database transaction instead of 1.2 million. I.e. less overhead.

  • When you use databaseManager.startTransaction() you are starting a transaction on the database itself. Everything you do on it afterwards will be in that transaction. And since Servoy talks to the database in SQL (even though you use Servoy objects) this all will be in that same transaction.

  • databaseManager.hasRecordChanges() is used for when you don’t use database transactions but Servoy ‘transactions’ (i.e. autoSave off). So it would make sense it returns false.

Hope this explains things better.

26

That memory usage grows is normal. How far does it grow?

27

From 50MB up to 350MB. Then we restart the app from where it was.

28

But 350 MB is nothing! Just give your Servoy up to 512 MB and you are fine.

29

Hi,

I still think it will make sense making the back-end doing some of this work… Instead of processing the records one-by-one from Servoy… can you do some of the linking and creation of records as SQL queries?

We had to import 1,000,000+ records from FileMaker, we cleaned the data, replaced all the FileMaker pks which were strings and created integer new ones for Servoy and linked up data in about 45 tables all in something like 30 minutes (can’t remember now, but it was minutes, not hours) using the Servoy RawSQL plugin…

30

Patrick: From 50MB to 350 it took an hour. 500MB will be reached within the next two hours. And swapping starts. But at least we gain another two hours. I think Robert mentioned to me that the cheapest will be to buy more memory. What you suggested… :?

Christian: Just filling up tables by itself we did as well, using SQL. We did it with over 100 tables for the last application. That’s not a problem. Even filling this attribute ‘source’ of the table ‘timetable_positions’ is done with INSERT statements. But now I have to fill in foreign key attributes I find while reading and interpreting the source attribute. So I have to check, if relations exist and if I have to create new records in related tables. This is why I make use of Servoy relations defined for each record in my foundset. And even for the interpretation of the source attribute I make use of string and int operations of Servoy.

Thank you both for your answer. And regards!