PostgreSQL performance

[Robert_Huber]

Hello PostgreSQL users

While developing a timetable parser, we are running into performance issues. So I need to get a “feeling” what to expect (or not) from PostgreSQL. Currently we have version 8.2.5 on a Mac OX X server 10.4.11 (PPC, 1.5 GB memory).

Running just a select * from

takes around 410’000 ms, i. e. nearly 7 minutes! The table contains roughly 1.3 Mio records.

For what I would have expected, this is a long time for such a stmt.

We also use Sybase iAnywhere which seems to be much faster. I should say that these comments are at the moment not hard facts, speak measure based, but what we “see” while developing.

I would be very happy to hear about others experience concerning PostgreSQL performance. May be there are parameters to set better than the defaults one, may be we need more memory because PostgreSQL needs in general more memory than Sybase etc.

Thanks for any comments, Robert

PS: BTW, the currently slow performance in our setup is not limited to big tables, also quering, updating, inserting of records into tables with some 10’000 of records is not as we expect.

PS2: Table and indexes of the 1.2 Mio table:

TrackIT=# \d+ timetable_positions
Table “public.timetable_positions”
Column | Type | Modifiers | Description
---------------------------------±----------------------------±----------±------------
connection_company_id | integer | |
connection_station_id_arriving | integer | |
connection_station_id_departing | integer | |
connection_vehicle_code | character varying(50) | |
creation_date | timestamp without time zone | |
creation_user_id | integer | |
creation_user_name | character varying(100) | |
id | integer | not null |
modification_date | timestamp without time zone | |
modification_user_id | integer | |
modification_user_name | character varying(100) | |
source | character varying(200) | |
station_id_arriving | integer | |
station_id_departing | integer | |
temporary_marked | integer | |
time_of_arrival | integer | |
time_of_departure | integer | |
transport_company_id | integer | |
transport_number | integer | |
transport_variant | integer | |
version | character varying(200) | |
vehicle_code | character varying(100) | |
is_longest_transport_variant | boolean | |
Indexes:
“timetable_positions_pkey” PRIMARY KEY, btree (id)
“timetable_positions_1_ix” btree (time_of_departure)
“timetable_positions_fkey_1_ix” btree (station_id_arriving)
“timetable_positions_fkey_2_ix” btree (station_id_departing)
“timetable_positions_fkey_3_ix” btree (connection_company_id, connection_station_id_arriving, connection_station_id_departing, connection_vehicle_code)
“timetable_positions_fkey_4_ix” btree (vehicle_code)
“timetable_positions_fkey_5_ix” btree (transport_company_id, transport_number, transport_variant)
Has OIDs: no

[swingman]

Hi Robert,

Looking at your table, you are pulling in about 1 to 1.3 GB of data…
Why do you need to do that?

Just tried on a local table with 245,000 records from PGAdmin;

SELECT * FROM table, 7secs
SELECT id FROM table, 0.4 secs

EXPLAIN SELECT gives me “Seq Scan on sessions (cost=0.00…9882.56 rows=248056 width=206)”

2GHz MacBook, 2GB Ram with about 20 apps running…

[ROCLASI]

Hi Robert,

Lets start with the obvious first before we dig into this deeper.

Do you have autovacuum running ? (see the properties of the server in pgAdmin III). The vacuum process is a garbage collection process.
To quote the PostgreSQL documentation:

VACUUM reclaims storage occupied by deleted tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it’s necessary to do VACUUM periodically, especially on frequently-updated tables.

If you don’t have autovacuum running then enable it in the postgresql.conf (also requires to have stats_start_collector and stats_row_level enabled).
In the mean time you could do a vacuum by hand. You can read more about this in the excellent PostgreSQL documentation.

P.s. in PostgreSQL 8.3 (it’s released!) autovacuum is now enabled by default.

Hope this helps.

[Robert_Huber]

Hi Christian

Thanks for your feedback, see my comments below.

swingman:
Hi Robert,

Looking at your table, you are pulling in about 1 to 1.3 GB of data…
Why do you need to do that?

Funny question, that’s our job

Seriously, this table is just this big, i. e. that’s the data in that table. And before you ask: it cannot be split up into smaller parts (for logical reasons, not technically speaking). Actually, it will be even bigger.

swingman:
Just tried on a local table with 245,000 records from PGAdmin;

SELECT * FROM table, 7secs
SELECT id FROM table, 0.4 secs

EXPLAIN SELECT gives me “Seq Scan on sessions (cost=0.00…9882.56 rows=248056 width=206)”

2GHz MacBook, 2GB Ram with about 20 apps running…

I get “Seq Scan on sessions (cost=0.00…9882.56 rows=180867.25 rows=1373725 width=599)”

… for what it’s worth? I don’t see much info in this stmt except it’s a sequential scan, do you?

Best regards, Robert

[Robert_Huber]

Hi Robert

Checking the obvious things is always good to start with .-) Yes, I did do run vacuum, for the moment manually, as I also read about the new features in v8.3.

Vacuum did have a minimal effect in my case, not seeing any noticable difference.

Regards, Robert

ROCLASI:
Hi Robert,

Lets start with the obvious first before we dig into this deeper.

Do you have autovacuum running ? (see the properties of the server in pgAdmin III). The vacuum process is a garbage collection process.
To quote the PostgreSQL documentation:

VACUUM reclaims storage occupied by deleted tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it’s necessary to do VACUUM periodically, especially on frequently-updated tables.

If you don’t have autovacuum running then enable it in the postgresql.conf (also requires to have stats_start_collector and stats_row_level enabled).
In the mean time you could do a vacuum by hand. You can read more about this in the excellent PostgreSQL documentation.

P.s. in PostgreSQL 8.3 (it’s released!) autovacuum is now enabled by default.

Hope this helps.

[ROCLASI]

I’ve been doing some testing and got some interesting results.
This is what I did. First I created your table:

CREATE SEQUENCE seq_timetable_positions_id INCREMENT 1; 
CREATE TABLE timetable_positions (
	connection_company_id integer,
	connection_station_id_arriving integer,
	connection_station_id_departing integer,
	connection_vehicle_code character varying(50),
	creation_date timestamp without time zone,
	creation_user_id integer,
	creation_user_name character varying(100),
	id integer PRIMARY KEY not null DEFAULT nextval('seq_timetable_positions_id'::regclass),
	modification_date timestamp without time zone,
	modification_user_id integer,
	modification_user_name character varying(100),
	source character varying(200),
	station_id_arriving integer,
	station_id_departing integer,
	temporary_marked integer,
	time_of_arrival integer,
	time_of_departure integer,
	transport_company_id integer,
	transport_number integer,
	transport_variant integer,
	version character varying(200),
	vehicle_code character varying(100),
	is_longest_transport_variant boolean
) WITHOUT OIDS; 

CREATE INDEX timetable_positions_1_ix ON timetable_positions USING btree (time_of_departure); 
CREATE INDEX timetable_positions_fkey_1_ix ON timetable_positions USING btree (station_id_arriving); 
CREATE INDEX timetable_positions_fkey_2_ix ON timetable_positions USING btree (station_id_departing); 
CREATE INDEX timetable_positions_fkey_3_ix ON timetable_positions USING btree (connection_company_id, connection_station_id_arriving, connection_station_id_departing, connection_vehicle_code); 
CREATE INDEX timetable_positions_fkey_4_ix ON timetable_positions USING btree (vehicle_code); 
CREATE INDEX timetable_positions_fkey_5_ix ON timetable_positions USING btree (transport_company_id, transport_number, transport_variant);

I inserted some dummy data that I thought would be representable size-wise:

INSERT INTO timetable_positions (connection_company_id,connection_station_id_arriving,connection_station_id_departing,connection_vehicle_code,
creation_date,creation_user_id,creation_user_name,modification_date,modification_user_id,modification_user_name,
source,station_id_arriving,station_id_departing,temporary_marked,time_of_arrival,time_of_departure,transport_company_id,
transport_number,transport_variant,version,vehicle_code,is_longest_transport_variant) VALUES (
1000,2000,3000,'ABCDEFGHIJKLMNOPQRST',NOW(),4000,'Robert Ivens',NOW(),5000,'Robert Ivens',
'1234567890abcdefghijklmnopqrstuvwxyz',6000,7000,1,12,16,8000,123456789,987654321,
'version character something','ABCDEFGHIJKLMNOPQRSTUVWXYZ',true);

Then I re-inserted the content of this table into itself a couple of times. So that the table grew exponentially to 2097152 rows. So over 2 million rows.
Then I ran VACUUM FULL and VACUUM ANALYZE to make sure the database was clean and the parser had all it’s statistics.

Then in PSQL I ran SELECT * FROM timetable_positions;.
This took 55 seconds to show the data.

Then I tried Servoy 3.5.3 with the following method:

var dStart   = new Date(),
	sQuery    = "SELECT * FROM timetable_positions;",
	ds        = databaseManager.getDataSetByQuery("trackit",sQuery, null, -1);
var dEnd     = new Date();

application.output(dEnd-dStart);

I ran out of memory really quick so I gave Servoy Developer 1024MB of RAM to work with. Still ran out of memory. So I am not sure what you want to do with such large datasets.
Anyway…I reduced the resultsets a bit and came up with theses numbers:

Foundset   Milliseconds
      1          2
     10          5
    100         23
   1000        293
  10000        630
 100000      10255
 500000     129179
1000000   Out Of Memory

If you run top in the terminal to see what process has what CPU load you’ll see that it is Servoy and/or the JDBC driver that is the bottleneck.
With the foundset of 500,000 records postgres was showing CPU load for only 12 seconds so all the rest of the time seems to be used for receiving the data.

I also see that the more you run those large queries in Servoy the slower things get. Re-launching Servoy Developer makes things speedy again.
So something is going on.

Mac Book Pro 2.6Ghz/4GB
Servoy Developer
Version 3.5.3-build 516
Java version 1.6.0_01-41-release (Mac OS X)

Hope this helps.

[Robert_Huber]

Hi Robert

Thanks very much for your trial. I feel sorry that I seem to have led you (and may be others) to the wrong route. I just mentioned the select * from … query to say even with that simple query PostgreSQL is already slow (in our environment). I did the query in pgAdmin3, that’s it. It was ment to just show in an easy way the found problems.

What we are really doing is (in short):

• Reading timetable data into the table timetable_positions
• Parsing this table, i. e. making a syntactical analysis
• Matching timetable data with data already in the db (in various tables)
• Making inserts and updates in various tables (including the timetable_positions table itself), based on the result of the syntactical analysis of timetable_positions

The surprise is that we have this already done with a FileMaker 6 solution. In our new Servoy solution, one whole run takes about 30 hours, whereas in FileMaker it took around 12 hours. I have to say that we do a bit more (as usual) in the new Servoy solution, but surelely not THAT much more .-)
So I have to find what’s wrong in this picture.

Thanks for your help again, Robert

ROCLASI:
I’ve been doing some testing and got some interesting results.
This is what I did. First I created your table:

CREATE SEQUENCE seq_timetable_positions_id INCREMENT 1; 

CREATE TABLE timetable_positions (
connection_company_id integer,
connection_station_id_arriving integer,
connection_station_id_departing integer,
connection_vehicle_code character varying(50),
creation_date timestamp without time zone,
creation_user_id integer,
creation_user_name character varying(100),
id integer PRIMARY KEY not null DEFAULT nextval(‘seq_timetable_positions_id’::regclass),
modification_date timestamp without time zone,
modification_user_id integer,
modification_user_name character varying(100),
source character varying(200),
station_id_arriving integer,
station_id_departing integer,
temporary_marked integer,
time_of_arrival integer,
time_of_departure integer,
transport_company_id integer,
transport_number integer,
transport_variant integer,
version character varying(200),
vehicle_code character varying(100),
is_longest_transport_variant boolean
) WITHOUT OIDS;

CREATE INDEX timetable_positions_1_ix ON timetable_positions USING btree (time_of_departure);
CREATE INDEX timetable_positions_fkey_1_ix ON timetable_positions USING btree (station_id_arriving);
CREATE INDEX timetable_positions_fkey_2_ix ON timetable_positions USING btree (station_id_departing);
CREATE INDEX timetable_positions_fkey_3_ix ON timetable_positions USING btree (connection_company_id, connection_station_id_arriving, connection_station_id_departing, connection_vehicle_code);
CREATE INDEX timetable_positions_fkey_4_ix ON timetable_positions USING btree (vehicle_code);
CREATE INDEX timetable_positions_fkey_5_ix ON timetable_positions USING btree (transport_company_id, transport_number, transport_variant);

I inserted some dummy data that I thought would be representable size-wise:

INSERT INTO timetable_positions (connection_company_id,connection_station_id_arriving,connection_station_id_departing,connection_vehicle_code,
creation_date,creation_user_id,creation_user_name,modification_date,modification_user_id,modification_user_name,
source,station_id_arriving,station_id_departing,temporary_marked,time_of_arrival,time_of_departure,transport_company_id,
transport_number,transport_variant,version,vehicle_code,is_longest_transport_variant) VALUES (
1000,2000,3000,‘ABCDEFGHIJKLMNOPQRST’,NOW(),4000,‘Robert Ivens’,NOW(),5000,‘Robert Ivens’,
‘1234567890abcdefghijklmnopqrstuvwxyz’,6000,7000,1,12,16,8000,123456789,987654321,
‘version character something’,‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’,true);

Then I re-inserted the content of this table into itself a couple of times. So that the table grew exponentially to 2097152 rows. So over 2 million rows.
Then I ran VACUUM FULL and VACUUM ANALYZE to make sure the database was clean and the parser had all it's statistics.

Then in PSQL I ran SELECT * FROM timetable_positions;.
This took 55 seconds to show the data.

Then I tried Servoy 3.5.3 with the following method:

var dStart = new Date(),
sQuery = “SELECT * FROM timetable_positions;”,
ds = databaseManager.getDataSetByQuery(“trackit”,sQuery, null, 100000);
var dEnd = new Date();

application.output(dEnd-dStart);

I ran out of memory really quick so I gave Servoy Developer 1024MB of RAM to work with. Still ran out of memory. So I am not sure what you want to do with such large datasets.
Anyway...I reduced the resultsets a bit and came up with theses numbers:

Foundset Milliseconds
1 2
10 5
100 23
1000 293
10000 630
100000 10255
500000 129179
1000000 Out Of Memory

If you run top in the terminal to see what process has what CPU load you'll see that it is Servoy and/or the JDBC driver that is the bottleneck.
With the foundset of 500,000 records postgres was showing CPU load for only 12 seconds so all the rest of the time seems to be used for receiving the data.

I also see that the more you run those large queries in Servoy the slower things get. Re-launching Servoy Developer makes things speedy again.
So something is going on.

Mac Book Pro 2.6Ghz/4GB
Servoy Developer
Version 3.5.3-build 516
Java version 1.6.0_01-41-release (Mac OS X)

Hope this helps.

[ROCLASI]

Hi Robert,

I think you need to give us more info on what you are doing exactly but let me give you a couple of general pointers.
PostgreSQL uses implicit transactions when you don’t use one yourself. So if you do 1000 inserts/updates without a transaction PostgreSQL will be doing 1000 transactions for you.
So if you put it all in 1 transaction then you loose a LOT of overhead.
Also did you increase your shared memory in your OS? This also helps.

Hope this helps.

[Robert_Huber]

Hi Robert

Is it correct that you are saying I should use a transaction to minimize overhead?

I read in this post

http://forum.servoy.com/viewtopic.php?p … e0e7bfb752

about setting/changing shared memory.

Is that what you are referring to?

Regards, Robert

ROCLASI:
Hi Robert,

I think you need to give us more info on what you are doing exactly but let me give you a couple of general pointers.
PostgreSQL uses implicit transactions when you don’t use one yourself. So if you do 1000 inserts/updates without a transaction PostgreSQL will be doing 1000 transactions for you.
So if you put it all in 1 transaction then you loose a LOT of overhead.
Also did you increase your shared memory in your OS? This also helps.

Hope this helps.

[ROCLASI]

Robert Huber:
Hi Robert

Is it correct that you are saying I should use a transaction to minimize overhead?

When doing a lot of inserts/updates then yes, you should.

Robert Huber:
I read in this post

http://forum.servoy.com/viewtopic.php?p … e0e7bfb752

about setting/changing shared memory.

Is that what you are referring to?

Correct.

[ROCLASI]

When I read my reply back I see I was not completely correct. So let me rephrase.

PostgreSQL auto-commits each statement you send to the database. This includes SELECT statements as well.
So each statement is actually wrapped in a transaction. This is just how PostgreSQL internally works.
To disable this auto-commit you simply start a transaction yourself and commit it when you are done with your process.

Of course in your case this would have the added benefit that when something happens during your inserting/updating/analyzing all those tables you won’t be stuck with partially loaded/updated data. You just rollback.

Hope this explains it a bit better.

[Robert_Huber]

Hi Robert

There is definitly something “going on” when running a Javascript method which takes a long time, in my case several couple of hours. But we still couldn’t figure out what’s going on, i. e. what’s the problem.

After about 2 hours starting the script (which basically makes updates on a table (1,5 Mio records) and inserts records in two other tables), it is getting much slower. As an idea the first hour it can process around 80’000 records, whereas some hours later it is down to processing only 10’000 to 20’000 records, with conditions unchanged.

I wonder if anyone has an idea and if this is related to PostgreSQL (8.2.5) or to Servoy Developer (which seems to be the case). After just restarting Servoy Developer, the processing is fast again but after an hours or two it’s slow again. Memory leak in Servoy Developer (3.5.5)?

I would like if Servoy could give any hint. Someone else must have had such problems or do all only have little data. I mean 1,5 Mio records is still not THAT much, is it.

Thanks for any hints, Robert

PS: By the way, where do you increase the Servoy Developer memory?

) in Servoy Developer,

ROCLASI:
I’ve been doing some testing and got some interesting results.
I ran out of memory really quick so I gave Servoy Developer 1024MB of RAM to work with. Still ran out of memory. So I am not sure what you want to do with such large datasets.
Anyway…I reduced the resultsets a bit and came up with theses numbers:

Foundset   Milliseconds

  1          2
 10          5
100         23

1000 293
10000 630
100000 10255
500000 129179
1000000 Out Of Memory

If you run top in the terminal to see what process has what CPU load you'll see that it is Servoy and/or the JDBC driver that is the bottleneck.
With the foundset of 500,000 records postgres was showing CPU load for only 12 seconds so all the rest of the time seems to be used for receiving the data.

I also see that the more you run those large queries in Servoy the slower things get. Re-launching Servoy Developer makes things speedy again.
So something is going on.

Mac Book Pro 2.6Ghz/4GB
Servoy Developer
Version 3.5.3-build 516
Java version 1.6.0_01-41-release (Mac OS X)

Hope this helps.

[ROCLASI]

Robert Huber:
PS: By the way, where do you increase the Servoy Developer memory?

On the Mac you need to edit Servoy.app/Contents/Info.plist and change the VMOptions value.
To open up the package you right-click on it and select ‘Show Package Contents’.

On Windows you edit the servoy_developer.bat and change the -Xmx value.

Btw are you using databaseManager.saveData() when do you do your inserts? And what about using a single database transaction ?
Also you can track Servoy’s memory usage via the About window. Would be interesting to see what it does during such a process.

Hope this helps.

[Robert_Huber]

Hi Robert

Ok, it seems strongly to be a Servoy Developer (memory leak?) problem. I assigned 400M to Servoy Developer. Look at my About window series only after about 2 hours. It starts with ca. 45 MB and is later at 330 MB. And when it’s at max. it get’s really slow.

I think I have to file a bug report.

Thanks for help, Robert

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?

ROCLASI:

Robert Huber:
PS: By the way, where do you increase the Servoy Developer memory?

On the Mac you need to edit Servoy.app/Contents/Info.plist and change the VMOptions value.
To open up the package you right-click on it and select ‘Show Package Contents’.

On Windows you edit the servoy_developer.bat and change the -Xmx value.

Btw are you using databaseManager.saveData() when do you do your inserts? And what about using a single database transaction ?
Also you can track Servoy’s memory usage via the About window. Would be interesting to see what it does during such a process.

Hope this helps.

[patrick]

Robert, could you give us some more detail of what your methods do and how they do it? I have had some memory issues when walking over large tables, but there is mostly a way around it. So a code example or a description of what the methods do would be helpful.

[ROCLASI]

Hi Robert,

Robert Huber:
Hi Robert

Ok, it seems strongly to be a Servoy Developer (memory leak?) problem. I assigned 400M to Servoy Developer. Look at my About window series only after about 2 hours. It starts with ca. 45 MB and is later at 330 MB. And when it’s at max. it get’s really slow.

I think I have to file a bug report.

That might not be a memory leak but normal memory usage depending on how you coded your method.
Are you working with datasets and are these large ? If so you might want to work with OFFSET and LIMIT in SQL to process batches of records.

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 ?

[birgit]

Hi Patrick

Thank you very much for your offer to analyse our parser. I’m very much interested in possibilities to avoid memory leaks.

Our parser reads the timetable of the whole public transport system in Switzerland. The timetable is a textfile with about 1’200’000 rows. This file we insert row by row into our table timetable_positions and fill attribute ‘source’. Then we iterate over this table and parse the source. We read information about stations, connections, transport numbers, transport offers. This information is filled into the database: We update the table timetable_positons and create rows in related tables if neccessary.

Following the main structure of the parser:

// variable definitions:
var record = null;
var source = "";
var nextSource = "";
var currentId = -1;
...

// parser can start at any line:
controller.find();
id = '>=' + globals.tttFromId;
controller.search();

// iterate over the found entries in timetable_position
for (var i = 1; i <= controller.getMaxRecordIndex(); i++)
{
	record = foundset.getRecord(i);
	source = record.source;
	nextSource = foundset.getRecord(i + 1).source;
	...
	
	if (!source)									// source is empty. New zugfahrt will begin
	{	
		...
	}
	else // Zugfahrt
	{
		if (source.match("^*Z"))				// Zuginformation
		{
			// identifies a transport:		
			transportNumber = parseInt(utils.stringMiddleWords(source, 2, 1), 10);
			transportCompanyId = parseInt(utils.stringMiddleWords(source, 3, 1), 10);
			transportVariant = parseInt(utils.stringMiddleWords(source, 4, 1), 10);
				
			record.transport_number = transportNumber;
			record.transport_company_id = transportCompanyId;
			record.transport_variant = transportVariant;
			
			// Insert new record into table transports.
			// The other attributes name, stationIds and times will be filled in by other rows
			record.timetable_positions_source_for_transports.newRecord();			
		}	
		else
		{	
			// Set foreign key for transport	
			record.transport_number = transportNumber;
			record.transport_company_id = transportCompanyId;
			record.transport_variant = transportVariant;
			
			if (source.match("^*G"))				// Zuggattung
			{
				var zugart = new forms.TttMethods.Zugart();
				zugart.vehicleCode = utils.stringMiddleWords(source, 2, 1);
				zugart.stationIdDeparting = utils.stringMiddleWords(source, 3, 1);
				zugart.stationIdArriving = utils.stringMiddleWords(source, 4, 1);
				vehicleCodes.push(zugart);
			}	
			else if (source.match("^*A"))				// Angebot(e)
			{
				...	
			}
			else // Normal line with station, departure time and arriving time
			{
		
				record.station_id_departing = parseInt(utils.stringLeftWords(source, 1), 10);
				record.timetable_positions_departing_location_terminated_by_stations.timetable_date = infoLine.timetableDate;
				...	
							
				if (isFirstLine) // Fill arriving attributes and attributes in transports record
				{
					record.station_id_arriving = parseInt(utils.stringLeftWords(nextSource, 1), 10);
					
					if (foundset.getRecord(i + 2).source) // Next line is not last line of block. So time of arrival is 3. string from right
					{
						...
					}
					record.timetable_positions_source_for_transports.station_id_departing = record.station_id_departing;
					record.timetable_positions_source_for_transports.time_of_departure = record.time_of_departure;
					
					isFirstLine = false;			
				}
				else ...
				
				// Set the foreign key to connections, if possible:
				var relToDepStation = record.timetable_positions_departing_location_terminated_by_stations;				
				var relToArrStation = record.timetable_positions_arriving_location_terminated_by_stations;
				if (!relToDepStation || relToDepStation.getSize() == 0)
				{
					...
				}
				...
				else
				{
					globals.tttStationIdA = relToDepStation.station_id_substitution;
					globals.tttStationIdB = relToArrStation.station_id_substitution;
					globals.tttVehicleTypeName = record._timetable_positions_to_vehicles.vehicle_type_name;
					
					record.connection_station_id_departing = globals.tttStationIdA;
					record.connection_station_id_arriving = globals.tttStationIdB;
					record.connection_company_id = transportCompanyId;
					record.connection_vehicle_code = record.vehicle_code;
						
					if (!record.timetable_positions_basis_for_connections || record.timetable_positions_basis_for_connections.getSize() == 0)
					// Connection does not yet exist
					{					
						// Does a track exist for the two stations?
						if ((!_a_tracks || _a_tracks.getSize() == 0) && (!_b_tracks || _b_tracks.getSize() == 0)) // Track does not exist. Create it.
						{
							_a_tracks.newRecord();
							trackCreated = true;
							var result1 = forms.TttMethods.log("Parser", "Line " + currentId + ": Track created for key " + globals.tttStationIdA + ", " + globals.tttStationIdB + ", " + globals.tttVehicleTypeName, currentId);
							_a_tracks.timetable_creation_date = infoLine.timetableId;
							_a_tracks.timetable_modification_date = infoLine.timetableId;
							_a_tracks.is_ignorable = 0;
							...
							
						}
						// Create connection
						...
						
					}
					
					...
				}
			}
		}
	}
	
	if (currentId%20) databaseManager.saveData();
}

Hope this helps to explain the program.

Regards
Birgit

[ROCLASI]

Hi Birgit,

I don’t see databaseManager.startTransaction() at the beginning and databaseManager.commitTransation() at the end.
Are you using that at all? As already explained in this thread this will improve your performance significantly when using PostgreSQL.

[patrick]

My eperience is that it helps to clear the foundset once in a while. What happens if you iterate over a very large foundset is that this foundset gets loaded in 200 records “chunks” and is becoming bigger and bigger. In those cases I usually try to find a way to only load a certain amount of records at a time and use that as a foundset. Once done with that, I clear the foundset and load the next reasonable amount of data. One way could be to query a minimum and maximum primary key (if that is an integer) and load data by querying for certain pk ranges.

[birgit]

Hi Robert (Ivens)

Thanks for the answer!

I did try the transaction already earlier, as you suggested . I just double checked again:

At the beginning of the loop I inserted a ```
databaseManager.startTransaction();

and at the end a

databaseManager.commitTransaction();

Then I did break before the commit and evaluated the following: 

% databaseManager.hasRecordChanges()
false
% databaseManager.commitTransaction();
false

I assume I'm beyond transaction control if I iterate over the foundset and change values via Servoy relations. As you can see, I don't use rawSQL (INSERT, UPDATE, DELETE) and I don't use a GUI. Auto commit on or off does not have any effect.

My current picture of how data is written back to the db:
- Either I have a GUI and write data to a data provider. With auto commit on, these values are written to the database whenever a fields has lost the focus (and no check fails). With auto commit off I have to define a transaction and a commit point myself (SAVE button e.g.) and a rollback point.
- Or I use rawSQL and change the database with my own commands. These are automatically commited by PostgreSQL as you mentioned before, right?
- Or I have NO GUI and no SQL statements. I use the foundset and change values of the foundset (and of related records).

Can anybody confirm my picture? Or let me know if and where I'm wrong?

Best regards