Hello all...
I'm doing something wrong, but am at a loss to figure out what it is.
I have a website (asp) that uses postgresql as the back-end for session
continuity and a few other things. The problem I'm having is that if I
do an insert (e.g. 260 rows) and then a select of the same rows, the
insert takes a fraction of a second and the select takes 10-15 seconds.
If I just do the select (without the insert first) it takes a small
fraction of a second. The table typically has about 10,000 rows, 10
columns (mostly numeric fields). Each row would average about 100bytes.
I'm only modifying 200-300 of rows at any given time.
My actual process is to, based on certain types of request, go to an
external data source (a special server) and get "live" (and
visitor-specific) information, then do one sql request of
"BEGIN;DELETE...WHERE;INSERT...[repeat 260 times];END" and another,
"SELECT...WHERE". At that point, the information is up-to-date for the
entire visitor's session. I've timed every step in the process, and
each individually takes less time then the delete/insert and select as a
set. In fact, if I put a 5 or even 10 second "Sleep" command between the
DELETE/INSERT and the SELECT, the net time doesn't increase at all.
Postgress is stopped internally doing something, and I don't know enough
to figure out what it is.
As a test I did a bulk import of the 500,000+ thousand rows (process
takes over 5 minutes including parsing about 30MB of inefficient
ASCI-text source files and everything else...quick, IMO) that could be
in the database at any given time, then disabled the delete/insert
command (but left everything else, including grabbing the data,
generating the SQL statement to execute, and the select). My *total*
process time (including datacomm) dropped by an average of 12 seconds to
a very acceptable 2 seconds end-to-end.
Postgress runing under cygwin/Win2K... :(
TIA,
