Hi,
In general what are the causes of SQL select statement slow downs - other than
the size of the data? I realize this is a broad question but read on please.
Here's where I'm coming from:
I have program that loops though several thousands of records. The loop
contains a select statement that confirms that I don't have a dup and then
inserts a record into a different table. IOW's the statement is checking
against a static table. The size of the table I'm checking does not change
during the loop. I'm sure many have done something similar.
The loop performs very well for the first 500 - 800 items. Then starts
slowing down. And somewhere around 1200 really slows to a crawl. To the point
it does not complete in a 12 hour period. But when I take several of the
statements and execute them using pgAdmin - they take almost no time (0.001 if
memory serves)
for rec in check_set_data:
select pkid from check_table where check_field = rec.field_name
if found skip
else insert into new table.
I'm using 8.4 on openSUSE 11.3 multi-processor, 8 GB of ram.
Johnf