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