Re: DBI - need for speed

You'd probably see a faster result by selecting a unique value in 1 SQL query for each record (if groupnumber is unique per each record, select that) store it into a hash:

$r{$groupnumber} = 1;
[download]

Then test the hash for the value as opposed to a single query for each record.

if ($r{$groupnum}) { next; }
else {
 ## Add record
}
[download]

You'll do one query at the start of your program and save the time for each query.

-techwolf

Comment on Re: DBI - need for speed Select or Download Code

Replies are listed 'Best First'.
Re: Re: DBI - need for speed by with.a.twist (Novice) on May 14, 2002 at 14:30 UTC
I tried this method, but found a small hindrance. Every time I add a record; I have to "re-populate" my hash. The possibility of having duplicate group numbers in my SRCFILE is high. This is minimal though. I can reduce the number of DB hits significantly by using this method. On another note. I worked with one of our DBA's and loaded these records directly (and manually) into the database. We found "sub-second" response times (and that's FAST!). Once I tried to automate the DB load with a korn script it brought me back up to the same performace as my original perl script. I think I'll stick with the query-hash-evaluate-insert-query-hash method. Thanks for everyones help on this.	[reply]

Replies are listed 'Best First'.

Re: Re: DBI - need for speed
by with.a.twist (Novice) on May 14, 2002 at 14:30 UTC

On another note. I worked with one of our DBA's and loaded these records directly (and manually) into the database. We found "sub-second" response times (and that's FAST!).

Once I tried to automate the DB load with a korn script it brought me back up to the same performace as my original perl script. I think I'll stick with the
query-hash-evaluate-insert-query-hash method.

Thanks for everyones help on this.

[reply]