Re: Using DBI, better option than importing into @array
- From: "J. Gleixner" <glex_no-spam@xxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 30 Jul 2007 17:33:04 -0500
Jason wrote:
The most current problem is SPEED! From sheer lack of knowledge, I'm
importing the subjects table into an array in the beginning of the
script, then using a for loop throughout the program to access that
array. But the program is running pretty slow, and I'm sure that the
bottleneck is with this array.
You probably want to look at the SQL 'limit' command. Fetching 17000
rows is a complete waste of time because there's no way you're going
to be able to efficiently display that much data.
Here is the code that I'm using:
# Push subjects into Perl array
my $filelist = $dbh->selectall_arrayref("SELECT `id`, `lastmodified`,
`subject` FROM $forum_subjects ORDER BY lastmodified DESC");
my @filenames;
for my $row (@$filelist) {
my ($id, $lastmodified, $subject) = @$row;
push(@filenames, $id . "|:|" . $lastmodified . "|:|" . $subject);
}
No need to do that. You have $filelist, iterate through it below,
instead of creating @filenames.
# In the "view subject" section, loop through last 20 indexes of
@filenames
# 0 and 20 are dynamic in the real script
No, use 'limit' and 'order by' to get the last 20 'id' fields.
for ($count=0; $count < 20; $count++) {
($id, $lastmodified, $subject) = split(/\|:\|/, $filenames[$count]);
my $topiclist = $dbh->selectall_arrayref("SELECT `id`, `subject`,
`postdate`, `username`, `email`, `comment` FROM $forum_posts WHERE
id=" . $dbh->quote($id) . " ORDER BY postdate ASC");
Look into placeholders and bind_columns and you don't need any of
those '`'.
my $sql = qq{ SELECT id, subject, postdate, username, email, comment
FROM $forum_posts
WHERE id=?
ORDER BY postdate ASC};
You could probably do it all in one query. There are plenty of
sites that discuss using DBI and many on MySQL, give those
a try too.
.
- 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
|
