Postgres/Perl performance help needed
Madison Kelly
linux-5ZoueyuiTZhBDgjK7y7TUQ at public.gmane.org
Wed May 26 00:33:55 UTC 2004
Marcus Brubaker wrote:
> 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,
Thanks! I will implement your suggestions now and report back. On the
string convertion thing, I single-quoted any string where the value
would be alph-numeric in some form and left ones that are integers (ie:
file_size) alone. Is that what you mean by avoiding string convertions?
Madison
--
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
More information about the Legacy
mailing list