Re: Using DBI, better option than importing into @array
- From: Jason <jwcarlton@xxxxxxxxx>
- Date: Mon, 30 Jul 2007 23:34:44 -0000
Yeah, I imagine so. The killer is that you are submitting a full
selectall inside a loop. You should generally NEVER do that. By
constructing a different SQL statement each time (because you hard-
code the id in the SQL) it means the statement will never be found in
your database server's cache, so the server must compute an execution
plan over and over again. Instead, you should always use the prepare
method (outside of the loop) and then an execute (inside the loop).
OK, I'll give that a shot.
The array is almost surely also a problem, but I'm not fully
understanding your intent. It would be very helpful to see a few
sample rows (or mock-up rows) from each table. I think you should be
doing a table straight join to avoid the need to pre-load the array,
but it would be far easier to make a recommendation if I saw some
sample data and you gave a specific example of what you want to do
with this data.
It's really a simple message board, just with a lot of rows. So the
subjects table is
Categories: id - lastmodified - subject
Actual row: 17090 - 20070730192222 - This is a test
The posts table would be like:
Categories: id - subject - postdate - username - email - comment
Actual row: 17090 - This is a test - 20070730192222 - Jason -
jwcarlton@xxxxxxxxx - This is a test comment.
In retrospect, I can't quite remember why I'm duplicating the
information into the subjects table; I think it was at the suggestion
of someone in another NG. I'm sure that the goal was to make the "view
subjects" section load faster by reading 17,000 rows instead of
600,000.
Would it be better to get rid of the "subjects" table altogether, and
just create an index with the ID field?
On other points related to style (and having nothing to do with
whether these points should be used in your instance, because I don't
think they should):
for ($count=0; $count < 20; $count++) {
($id, $lastmodified, $subject) = split(/\|:\|/, $filenames[$count]);
It's more Perlish to do a simple iterative loop like this:
for (0..20) { #Perl ain't C!
But your intent seems to be just to do an array slice. If you want to
take an array slice, don't create a counter and increment it; just
slice the array directly, such as:
foreach my $line_of_the_array ( @array[0..20] ) {
I guess my background is showing through, isn't it? LOL I'm using the
counter for a few other things, too, though; in this case, I use it to
determine the background color of the table row (it alternates based
on whether $count is divisible by 2).
I don't think that I've seen your foreach version before, though. I'll
have to remember that for future reference.
Oh, and, also:
$dbh->quote($id)
If you are sure your $id cannot be tainted (such as it has an integer
type imposed by the database) then you don't need to quote it (and
doing so further slows you down).
This confused me, too. When I didn't use quote(), it gave me an error;
something along the lines of "$id cannot be null." But it in the
database as a number, every time, so I couldn't understand why it did
that. Using quote() solved the problem, but I don't know why.
- J
.
- Follow-Ups:
- Re: Using DBI, better option than importing into @array
- From: usenet
- Re: Using DBI, better option than importing into @array
- From: Brian Blackmore
- Re: Using DBI, better option than importing into @array
- References:
- Prev by Date: Re: Using DBI, better option than importing into @array
- Next by Date: Re: Using DBI, better option than importing into @array
- Previous by thread: Re: Using DBI, better option than importing into @array
- Next by thread: Re: Using DBI, better option than importing into @array
- Index(es):
Relevant Pages
|
