Postgres/Perl performance help needed

[Marcus Brubaker]

On Tue, 2004-05-25 at 16:16, Madison Kelly wrote:
> Hi all,
> 
>    The intrepid "Asker of questions" is back (so sorry!).
> 
>    I am hoping I can pick the brains of those of you here who play with 
> postgresql a lot. Maybe you can help me configure the server to be more 
> efficient or suggest a different way to approach my code.
> 

<snip>

One thing which should help is to prepare your select/update/insert
statements before even entering the function, then reusing them with
different bound values.  (See the DBI documentation for more on bound
values.) E.G.

> =- Begin perl code snippet -=
> 
> $starttime=time;

$select_sth = $DB->prepare("SELECT null FROM file_dir WHERE 
file_src_uuid=? AND file_parent_dir=? AND file_name=?;") || die
"$DBI::errstr";

> $real_dir=$mntdir;
> # Set the 'file_exist' flag to 'false' and reset exiting files to 'true'.
> $DB->do("UPDATE file_dir SET file_exist='f' WHERE 
> file_src_uuid='$file_src_uuid'") || die "$DBI::errstr";
> # Jump to our file list sub routine
> &list_files($real_dir, $exclude_list_num, $relative_dir);
> $endtime=time;
> $totaltime=($endtime - $starttime);
> 
> sub list_files
> {

<snip>

Replace these lines:

> 				$DBreq=$DB->prepare("SELECT null FROM file_dir WHERE 
> file_src_uuid='$file_src_uuid' AND file_parent_dir='$relative_dir' AND 
> file_name='$file_name'") || die "$DBI::errstr";
> 				$num=$DBreq->execute();

with:

$num = $select_sth->execute($file_src_uuid,$relative_dir,$file_name) ||
die "$DBI::errstr";

<snip>

> =- End perl code snippet -=

Try doing this for all of your statements, even the ones which don't
return anything (eg updates and inserts).

This should not only help speed up your code (so that the statements
aren't continually re-parsed) it should also reduce the risk of
malformed SQL statements since bound parameters are automatically
appropriately quoted and escaped.

Also, when you're doing your timing, be sure to remove your print
statements unless they're crucial to the functioning of the script. 
Setting up the print statement and printing to a console is a slow
operation.

Another thing to look at is if unnecessary string conversions are
happening.  E.G. if the field file_sr_uuid is an int and your statements
end up as "WHERE file_src_uuid='n'" there *may* be an unneeded string
conversion happening and you should instead do "WHERE file_src_uuid=n".

Hope this helps.

Regards,
-- 
Marcus Brubaker <marcus.brubaker-H217xnMUJC0sA/PxXw9srA at public.gmane.org>

--
The Toronto Linux Users Group.      Meetings: http://tlug.ss.org
TLUG requests: Linux topics, No HTML, wrap text below 80 columns
How to UNSUBSCRIBE: http://tlug.ss.org/subscribe.shtml