speed of inserts

[dakota81]

I'm in the middle of taking an MS Access App and putting it into postgres, and for one particular form, in the worst case scenario, roughly 200 records need to be inserted into a table.

MS Access running on P233's & all data and work files on a Netware server (P200 cpu), accomplishes the task in <1sec.

Postgres on a linux server and an Athlon 1.13GHz nForce chipset, accomplishes the task in anywhere from ~20sec min to ~90sec max.

Harddrives are comparible, it's not disc speed.

SELECT queries run at about the same speed, I can't tell a speed difference even when performing joins and returning hundreds of rows.

Are there any general hints to optimizing tables in postgres for insertions? Is there any hope?

 

[Leland123]

Hi dakota81,

This dosn't sound right, because postgres shoud be very fast. It could be the network causing the problem or something going on in making the connection to the postgres database. In Postgres it is necessary to make a connection and authenticate, so I'm thinking something is going on with that.

In a local network, or in make a connection over the internet using a high speed connection, you should be able to select or insert 20,000 rows in between 3 to 5 sec.

LelandJ

Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox

 

[dakota81]

I thought it was very odd too; The new app I'm writing is in ColdFusion, and I put in lines to output a timestamp at different locations. Maybe it is the connection & authenication process that's slowing down, because the SELECT query is run just one, but I'm running a lot of INSERT queries.

I had never thought of that possibility.

 

[dakota81]

I did another test; this is not a speed issue between colfusion & postgres, I ran a loop of 1000 individual SELECT queries & that took <2 sec.

A loop running 100 individual INSERT queries is minimum 20sec, and more often than not, takes up to 40 or 50 seconds.

Something's not right, and the users of this application are getting angry, is there anything I can do?

 

[Leland123]

Hi dakota81,

Are you disconnecting after each insert? You might try using persistent connections. Also, you might try connection pooling.

You might also try fine tuning your PostgreSQL backend. Tuning a PostgreSQL backend is a broad subject and so pervasive as to touch on about every area of a system. I couldn't begin to cover the subject here, but you could visit the PostgreSQL web site. They have a number of email lists you can join. You could learn a lot from just reading the email, but you could also submit question to the PostgreSQL team that write, update, and maintain the PostgreSQL database.

Tom Lane is the PostgreSQL project leader and an employee of Redhat Linux. The team member stay very busy, so be sure and research the problem all you can, including reading the FAQs, before asking a question on the mailing list.

To maximize speed make sure all unnecessary activities are disabled like like detail transaction logging, etc.

Here is a link to a search engine on my web site that allow the PostgreSQL 7.3.3 Doc to be searched. You could use it to research tuning a PostgreSQL database and how to configure persistent connections and connection pooling.

http://www.smvfp.com//search_pgsql.html

Regards,

LelandJ
connection pooling.

Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox

 

[dakota81]

Thankfully to say we got the issue resolved. After enough griping and complaining finally got the server's administrator to look into the server more closely, had the Seti Client running taking up most of the cpu time. Once that process was killed, postgres started to function in much more realistic time. Now the routine that used to take a minute and a half is down to about 2 seconds.