Re^3: How to improve MYSQL search performance of perl?

I see why it is so slow: you are effectively for every search opening a connection, doing the search for 1 item and then destroying the connection. All this connecting and disconnecting is very time-consuming.

You should put your connection stuff in an initialization subroutine, then prepare your SQL-statement once, using place-holders as follows: " my $sth = $dbh->prepare('select topic FROM table1 WHERE uri LIKE ?');" (added benefit: you don't have to worry about quoting!) and then hand off the $sth-variable and the search-argument to your search-subroutine which calls the execute-method with the search string as its parameter:

my ($statement_handle, $search_argument)=@_;
$statement_handle->execute($search_argument); ...
[download]

Do you get the idea!

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Comment on Re^3: How to improve MYSQL search performance of perl? Select or Download Code

Replies are listed 'Best First'.
Re^4: How to improve MYSQL search performance of perl? by nan (Novice) on Aug 24, 2005 at 17:11 UTC
Hi, I can see what you mean but the result seems not improved and I received Internal Server 500 Error. I don't know if it is because I have hundreds of queries needed to be executed one by one or whatever. Initially, when I search by just one query, the time spent is no more than 30 secs but if I have 10 or 20 queries, the time spent is much more than 30 * 10/20. Do you have any ideas? Many many thanks! Nan	[reply]
Re^5: How to improve MYSQL search performance of perl? by CountZero (Bishop) on Aug 24, 2005 at 18:29 UTC
The "`Internal Server 500`"-error is probably due to the fact that your script generates an error and terminates, thus bringing down that part of your Apache server with it. What do you find in the error-log of Apache? Do you check all your DBI-methods for errors? Use CGI::Carp so you are sure all your errors are logged to the error-log and can be checked later. Can you run your script outside of the Apache-server and if so what is the result? CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply] [d/l]
Re^6: How to improve MYSQL search performance of perl? by nan (Novice) on Aug 25, 2005 at 15:56 UTC
Hi, If I run the script outside of the Apache-server, it's ok but just very slow. The error log says "Unrecognized escape \\h passed through at F:/httpserv/Top/cgi-bin/search.pl line 18" but line 18 is database connection: my $dbh = DBI->connect('DBI:mysql:diet', $ref) \|\| die "Failed to connect: $DBI::errstr"; thanks again, Nan	[reply]