CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
SQL Querys - how many ?

 



pu_nk
journeyman

Jan 25, 2001, 10:45 AM

Post #1 of 4 (253 views)
SQL Querys - how many ? Can't Post

I just have a quick question about connecting to a mySQL DB.

Is it very hard on the database if i use
SELECT, INSERT, DELETE a few times in a sub ?

I am writing a program and am accessing the table a lot.. Will this limit the performance of the program?
I guess what i am asking is how fast is it really, not in a speed term but will it harm my program ?

Drew



kencl
User

Jan 25, 2001, 2:39 PM

Post #2 of 4 (249 views)
Re: SQL Querys - how many ? [In reply to] Can't Post

That depends on a couple of factors. Are you connecting and disconnecting each time? If so, that's very wasteful. Are you calling prepare() and execute() each time (wrong way) or calling prepare() for each statement then batching the execution with a single call to execute() (right way)? If, however, a statement is dependant on the last one being executed first, then you have to call execute() more often. However you do it, connect and disconnect only once, and do as few execute()s as possible.

How often you do it doesn't matter. You gotta do what you gotta do. PERL and the OS take care of how fast it can happen by just stacking the tasks in a queue until there are system resourses available to perform the task.

>> If you can't control it, improve it, correlate it or disseminate it with PERL, it doesn't exist!


pu_nk
journeyman

Jan 25, 2001, 2:52 PM

Post #3 of 4 (248 views)
Re: SQL Querys - how many ? [In reply to] Can't Post

Thanks for the tip!

I am not connecting and disconnecting every time.
I just do that one time in the file, but i am using execute() a few times.

So are you saying that i can just prepare() all the queries and then on my last query i can execute() it ?


Do you have any examples on how to batch it up?

Drew




kencl
User

Feb 4, 2001, 12:48 AM

Post #4 of 4 (235 views)
Re: SQL Querys - how many ? [In reply to] Can't Post

That depends on whether or not any given query relies on a previous query being performed or not. Here's an example where each query is independant, but the statement itself is identical. First the wrong way:

Code
@trees = qw /oak, pine, cedar, fir/; 
foreach $TreeType (@trees) {
$sth = $dbh -> prepare("INSERT INTO treetable (treetype) VALUES($TreeType)");
$sth -> execute();
}

We just did 4 prepares, 4 executes and 4 destroys (12 calls to the database server)

Since these 4 insertions are independant of each other, a better way which is "typically significantly faster" according to Tim Bunce (the primary author of the DBI module), would be to use a placeholder, like this:

Code
$sth = $dbh -> prepare("INSERT INTO treetable (treetype) VALUES (?)"); 
foreach $TreeType (@trees) {
$sth -> execute($TreeType);
}

This time, we've done 1 prepare, 4 executes and 1 destroy ( 6 calls to the database server :).

Anyway, good luck. If you're doing much DB programming, I highly recommend that you get a hold of Alligator Descartes' & Tim Bunce's "Programming the Perl DBI", published by O'Reilly. It's one of the best written programming manuals I've ever read.

>> If you can't control it, improve it, correlate it or disseminate it with PERL, it doesn't exist!

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives