Ok. That's really basic database stuff:
Normally every Select-statement you send to a database has 3 steps:
1. Prepare
ClientLib sends Statement to Database which does different checks:
- Syntax
- do tables exists
- ...
2. Execute
ClientLib sends database information to execute the statement
3. - n. Fetch
Fetch row each single row from the database.
You could avoid the overhead of preparing when using a MySQL-Version
which supports Server-Prepared-Statements and using the so called
placeholders.
Take the following:
-------------------
---------------8<---------------
for( $i in ('Hans','Sep') ) {
$dbh->selectall_arrayref(SELECT * FROM users WHERE username = '$i');
}
---------------8<---------------
Your database always has to prepare the statement before executing it.
But you could avoid it like this:
---------------8<---------------
my $sth = $dbh->prepare("SELECT * FROM users WHERE username = ?");
for( $i in ('Hans','Sep') ) {
$sth->execute($i);
$sth->fetchall_arrayref();
}
---------------8<---------------
If you now want to remember whether a statement across the current
connection already has been prepare you let DBI decide that for you by
replacing $dbh->prepare() by $dbh->prepare_cached(). This way the first
time your apache-child prepares the statement it will remember that and
never bother the server again.
As a side effect you don't have to think about SQL-Injection.
But I please try to trace your application and see what takes long if
you are doing many textual searches in your DB i could swear it's the
database you have to tune and not your Application.
Tom
philge philip schrieb:
> i loaded Apache::DBI module and used it in script...but it didnt solve
> my problem..
>
> if u can please can u explain little more what u mean by
>
> Query
> - Use Apache::DBI+prepare_cached
>
> - Use Prepared Queries with Placeholders
>
> sincerely
> philge
>
>
>
> */Tom Schindl <[EMAIL PROTECTED]>/* wrote:
>
> You need to find out what's taking long:
>
> - Connection 2 database
> - Use Apache::DBI => Connection cached
>
> - Query
> - Use Apache::DBI+prepare_cached
>
> - Use Prepared Queries with Placeholders
>
> - Tune MySQL-indices maybe you need FULLTEXT-Indices
> which are only available in MyISAM-Tables
> http://dev.mysql.com/doc/refman/5.0/en/create-table.html
>
> - Tune MySQLs query cache
>
> - large scripts (mod_perl as you already did :-)
>
> I'd say that you should insert tracing calls and see what is slow.
>
> From your report it seems that the connection/query takes long and not
> your perl-programm.
>
> Tom
>
> philge philip schrieb:
> > thanks a lot for your reply.we understood everything is fine in our
> > server.we are able to run the script u gave.we are developing a
> database
> > and our aim is to improve the speed of the cgi scripts fthat
> > retreive data from our mysql database.we redirected out query to
> scripts
> > we copied to /perl directory but we are taking the same 13 seconds to
> > retrive the data from our database.its a text based search..can
> you give
> > your opinion on this.is there any tutorial we can follow to
> improve our
> > scripts.please help us when you are free.
> >
> > Sincerely
> > Philge
> >
> > */Tom Schindl /* wrote:
> >
> > It seems you are using old documentations:
> > philge philip schrieb:
> > > Following are the scripts I tried to run.For the first script I
> > got the result and it shows mod_perl is installed and running but in
> > the second script am unable to load the apache module.I have also
> > attached the part of the my .conf file.please help me�
> > >
> > > *Program 1:*
> > >
> > > print "Content-type: text/plain\n\n";
> > >
> > > print "Server's environment\n";
> > >
> > > foreach ( keys %ENV )
> > >
> > > {
> > >
> > > print
> > > "$_\t$ENV{$_}\n";
> > >
> > > }
> > >
> > > **
> > > *Result:*
> > > Server's environment
> >
> > [snipp]
> >
> > > MOD_PERL mod_perl/2.0.2
> > >
> >
> > Perfect.
> >
> > >
> > >
> > > *Program 2:*
> > >
> > > use Data::Dumper;
> > > my $r = Apache->request( );
> > > $r->send_http_header('text/plain');
> > > print Dumper(\%ENV);
> > >
> > > *Internal server error:*
> > > Can't locate object method "request" via package "Apache"
> (perhaps you
> > > forgot to load "Apache"?) at mod_perl2.2.pl line 2.
> > >
> > >
> > >
> >
> > That's simply wrong:
> >
> > ----------------8<----------------------
> > use Data::Dumper;
> > use Apache::RequestUtil;
> > use Apache::RequestRec;
> >
> > my $r = Apache2::RequestUtil->request;
> > $r->content_type('text/plain');
> > print Dumper(\%ENV);
> > ----------------8<----------------------
> >
> > See:
> >
> > http://perl.apache.org/docs/2.0/api/ModPerl/Registry.html
> > or better if you are on prefork-mpm
> > http://perl.apache.org/docs/2.0/api/ModPerl/RegistryPrefork.html
> >
> > >
> > > Perl .conf file
> > >
> > > * *
> > > * *
> > > LoadModule perl_module modules/mod_perl.so
> > > LoadModule apreq_module modules/mod_apreq2.so
> > >
> > > Alias /perl /var/www/perl
> > >
> > >
> > > SetHandler perl-script
> > > PerlResponseHandler ModPerl::Registry
> > > PerlOptions +ParseHeaders
> > > Options +ExecCGI
> > >
> >
> > Correct.
> >
> > Tom
> >
> >
> >
> ------------------------------------------------------------------------
> > Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
> > >>
>
>
>
> ------------------------------------------------------------------------
> Yahoo! Messenger with Voice. Make PC-to-Phone Calls
> <http://us.rd.yahoo.com/mail_us/taglines/postman1/*http://us.rd.yahoo.com/evt=39663/*http://voice.yahoo.com>
> to the US (and 30+ countries) for 2¢/min or less.
