Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Performance questions (SQL-statements)

Reply
Thread Tools

Performance questions (SQL-statements)

 
 
Piet L.
Guest
Posts: n/a
 
      02-26-2005
I'm dealing with some performance problems.
I work with a hudge database (mysql) and have to perform queries on
it, depending of the parameters that the users give.
The intention is to build a real time website, automatically generated
according to the wishes of the users.

To illustrate the problem:
- here is an exctracting out of the database:
BOOK
book_id
title
publication_date

BOOK_AUTHOR
book_id
person_id

LIST_AUTHORS
person_id
name
firstname
email

- here is an example of the query I execute:
" show all the books written by author 124"
(Remark: a book can have more than one author
So I want something like:
book_title 1
author1_name, author2_name
book_title 2
author3_name, author1_name, author3_name, author4_name
)

The query is:
"SELECT b.*, ba.* from book b, book_author ba
LEFT JOIN list_authors l
ON ba.person_id = l.person_id
WHERE b.book_id = ba.book_id
AND ba.person_id = 124"

- I use XML::HANDLER::YAWRITER to write the result to a file
my $handler = XML::Handler::YAWriter->new(AsFile => "books.xml");
my $generator = XML::Generator:BI->new(
Handler => $handler,
dbh => $dbh
);

- Then I use xslt to transform it to html, I do this as follows:
my $xslt = XML::XSLT->new ("./books.xsl");
print $xslt->serve("./books.xml");

The problem is thus that this takes (some) minutes to execute all of
this (even if there are a few records as result),
I think the problem is with the JOIN, but I am not sure.
Can someone help me out,
I am not using the write modules,
is my structure not good for what I want to accomplish?
Are there any good books/tutorials/examples on the internet for
generating such an automatic site?

Also a question extra, Should I use CGI or isn't this neccessary?

THanks

PL.
 
Reply With Quote
 
 
 
 
Sherm Pendley
Guest
Posts: n/a
 
      02-26-2005
Piet L. wrote:

> The problem is thus that this takes (some) minutes to execute all of
> this (even if there are a few records as result),
> I think the problem is with the JOIN, but I am not sure.


Don't waste time trying to theorize about what the problem might be - find
out for sure what it is. Run your query by itself, without all the other
stuff. Does it take a long time? If so, ask - in a MySQL group, not here -
about how to optimize the query.

If it's not the query, do the same thing with the other steps. When you get
to a point where you add a step, and the total time goes from seconds to
minutes, you've found which step is the bottleneck.

> Also a question extra, Should I use CGI or isn't this neccessary?


Ask your users. Do they want a web interface where they can fill in a form?
If so, that means CGI.

sherm--

--
Cocoa programming in Perl: http://camelbones.sourceforge.net
Hire me! My resume: http://www.dot-app.org
 
Reply With Quote
 
 
 
 
Gregory Toomey
Guest
Posts: n/a
 
      02-26-2005
Piet L. wrote:

> I'm dealing with some performance problems.
> I work with a hudge database (mysql) and have to perform queries on
> it, depending of the parameters that the users give.
> The intention is to build a real time website, automatically generated
> according to the wishes of the users.
>
> To illustrate the problem:
> - here is an exctracting out of the database:
> BOOK
> book_id
> title
> publication_date
>
> BOOK_AUTHOR
> book_id
> person_id
>
> LIST_AUTHORS
> person_id
> name
> firstname
> email
>
> - here is an example of the query I execute:
> " show all the books written by author 124"
> (Remark: a book can have more than one author
> So I want something like:
> book_title 1
> author1_name, author2_name
> book_title 2
> author3_name, author1_name, author3_name, author4_name
> )
>
> The query is:
> "SELECT b.*, ba.* from book b, book_author ba
> LEFT JOIN list_authors l
> ON ba.person_id = l.person_id
> WHERE b.book_id = ba.book_id
> AND ba.person_id = 124"
>
> - I use XML::HANDLER::YAWRITER to write the result to a file
> my $handler = XML::Handler::YAWriter->new(AsFile => "books.xml");
> my $generator = XML::Generator:BI->new(
> Handler => $handler,
> dbh => $dbh
> );
>
> - Then I use xslt to transform it to html, I do this as follows:
> my $xslt = XML::XSLT->new ("./books.xsl");
> print $xslt->serve("./books.xml");
>
> The problem is thus that this takes (some) minutes to execute all of
> this (even if there are a few records as result),
> I think the problem is with the JOIN, but I am not sure.
> Can someone help me out,
> I am not using the write modules,
> is my structure not good for what I want to accomplish?
> Are there any good books/tutorials/examples on the internet for
> generating such an automatic site?
>
> Also a question extra, Should I use CGI or isn't this neccessary?
>
> THanks
>
> PL.


I do something quite a bit more complex here:
http://www.float.com.au/scgi-bin/bet...eport=advanced

and hints on how to do it here:
http://www.gregorytoomey.com/index.p...d=19&Itemid=28
The results are returned generally in <1 sec.

It comes down to experience with query optimisation (for me two decades working with databases).

gtoomey
 
Reply With Quote
 
xhoster@gmail.com
Guest
Posts: n/a
 
      02-26-2005
http://www.velocityreviews.com/forums/(E-Mail Removed) (Piet L.) wrote:
>
> To illustrate the problem:
> - here is an exctracting out of the database:
> BOOK
> book_id
> title
> publication_date
>
> BOOK_AUTHOR
> book_id
> person_id
>
> LIST_AUTHORS
> person_id
> name
> firstname
> email
>
> - here is an example of the query I execute:
> " show all the books written by author 124"
> (Remark: a book can have more than one author
> So I want something like:
> book_title 1
> author1_name, author2_name
> book_title 2
> author3_name, author1_name, author3_name,
> author4_name )
>
> The query is:
> "SELECT b.*, ba.* from book b, book_author ba
> LEFT JOIN list_authors l
> ON ba.person_id = l.person_id
> WHERE b.book_id = ba.book_id
> AND ba.person_id = 124"


Almost never should you use b.* notation in production code. Spell
out the columns that you want.

Why do you think the left join is necessary? Indeed, why is any join
against list_authors necessary when you never retrieve any of the fields
on that table?


> The problem is thus that this takes (some) minutes to execute all of
> this (even if there are a few records as result),
> I think the problem is with the JOIN, but I am not sure.


If you think your problem is with your join (which is executed on MySQL),
and not with your Perl, then why did you post it to a Perl group?


> Also a question extra, Should I use CGI or isn't this neccessary?


It is not *necessary*, but it is not clear what you think the alternative
to using it would be.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
 
Reply With Quote
 
Eric Schwartz
Guest
Posts: n/a
 
      02-28-2005
Sherm Pendley <(E-Mail Removed)> writes:
> Piet L. wrote:
>> Also a question extra, Should I use CGI or isn't this neccessary?

>
> Ask your users. Do they want a web interface where they can fill in a form?
> If so, that means CGI.


There's always Apache::Request under mod_perl, which isn't CGI. But
for what's going on here, yeah, that's probably the way to do it.

However, if the OP *was* doing mod_perl, then the output of any
long-running process could be cached in memory (this could become
potentially expensive, space-wise, though).

-=Eric
--
Come to think of it, there are already a million monkeys on a million
typewriters, and Usenet is NOTHING like Shakespeare.
-- Blair Houghton.
 
Reply With Quote
 
Eric Schwartz
Guest
Posts: n/a
 
      02-28-2005
(E-Mail Removed) writes:
> (E-Mail Removed) (Piet L.) wrote:
>> The query is:
>> "SELECT b.*, ba.* from book b, book_author ba
>> LEFT JOIN list_authors l
>> ON ba.person_id = l.person_id
>> WHERE b.book_id = ba.book_id
>> AND ba.person_id = 124"

>
> Almost never should you use b.* notation in production code. Spell
> out the columns that you want.


Oh yeah. This gives you two things:

1) Saves time if one of the extra columns returned happens to be, say,
a BLOB containing a JPG of the author.
2) Lets you confidently index by column number in the returned row(s).

> Why do you think the left join is necessary? Indeed, why is any join
> against list_authors necessary when you never retrieve any of the fields
> on that table?


Look closer-- list_authors is aliased to 'l', which is matched to
ba.person_id.

-=Eric
--
Come to think of it, there are already a million monkeys on a million
typewriters, and Usenet is NOTHING like Shakespeare.
-- Blair Houghton.
 
Reply With Quote
 
xhoster@gmail.com
Guest
Posts: n/a
 
      02-28-2005
Eric Schwartz <(E-Mail Removed)> wrote:
> (E-Mail Removed) writes:
> > (E-Mail Removed) (Piet L.) wrote:
> >> The query is:
> >> "SELECT b.*, ba.* from book b, book_author ba
> >> LEFT JOIN list_authors l
> >> ON ba.person_id = l.person_id
> >> WHERE b.book_id = ba.book_id
> >> AND ba.person_id = 124"

> >
> > Almost never should you use b.* notation in production code. Spell
> > out the columns that you want.

>
> Oh yeah. This gives you two things:
>
> 1) Saves time if one of the extra columns returned happens to be, say,
> a BLOB containing a JPG of the author.
> 2) Lets you confidently index by column number in the returned row(s).
>
> > Why do you think the left join is necessary? Indeed, why is any join
> > against list_authors necessary when you never retrieve any of the
> > fields on that table?

>
> Look closer-- list_authors is aliased to 'l', which is matched to
> ba.person_id.


Correct, but no columns from l are being returned. And under the
emminently reasonable assumption that person_id is the PK of l, then l
wouldn't be serving to determine the multiplicity of occurences of the
rows, either. I guess it is possible that person_id is not the PK of l, but
then the OP picked some really bad column names and it is entirely unclear
what he is trying to accomplish.



Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
 
Reply With Quote
 
Eric Schwartz
Guest
Posts: n/a
 
      03-01-2005
(E-Mail Removed) writes:
> Eric Schwartz <(E-Mail Removed)> wrote:
>> Look closer-- list_authors is aliased to 'l', which is matched to
>> ba.person_id.

>
> Correct, but no columns from l are being returned.


<snip> True enough, but now we're left clpm entirely and have gone
into comp.rdbms.suck.suck.suck.

-=Eric
--
Come to think of it, there are already a million monkeys on a million
typewriters, and Usenet is NOTHING like Shakespeare.
-- Blair Houghton.
 
Reply With Quote
 
Piet L.
Guest
Posts: n/a
 
      03-01-2005
OK, but even I change it like

"SELECT b.book_id, b.title, l.name, l.firstname
FROM book b, book_author ba
LEFT JOIN list_authors l
ON ba.person_id = l.person_id
WHERE b.book_id = ba.book_id
AND ba.person_id = 124"

I'm still having the same problem.

Also, another question:

How does it come that I'm not able to display xml/xslt transformation
in my webbrower? (Microsoft Internet Explorer). When I try to test
the script
I only get the source code, not the WSYSIWYG
 
Reply With Quote
 
JayEs
Guest
Posts: n/a
 
      03-01-2005

"Piet L." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> OK, but even I change it like
>
> "SELECT b.book_id, b.title, l.name, l.firstname
> FROM book b, book_author ba
> LEFT JOIN list_authors l
> ON ba.person_id = l.person_id
> WHERE b.book_id = ba.book_id
> AND ba.person_id = 124"
>

I am with the other people that answered. The left join seems useles. You
are trying to get every b.book_id where the ba.person_id = 124. In plain
english: Show every book for which the person with id 124 is the author.

I would say the following (untested) is more appropriate:

SELECT b.book_id, b.title, l.name, l.firstname
FROM book b, book_author ba, list_authors l
WHERE b.book_id = ba.book_id
AND ba.person_id = l.person_id
AND l.person_id = 124

You mention that a book can have multiple authors, but that is not important
in this query, only that an author can have written multiple books.
UNLESS... UNLESS you also want to show the other authors if a book has
indeed multiple authors. I would use a sub select in that case, but since I
don't know the proper mySQL syntax for that, I won't go there....


How off-topic was this??




 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Performance Tutorials Services - Boosting Performance by DisablingUnnecessary Services on Windows XP Home Edition Software Engineer Javascript 0 06-10-2011 02:18 AM
Performance-related questions about an ASP.NET page... Sammy ASP .Net 2 02-15-2005 02:00 PM
Re: Questions....questions....questions Patrick Michael A+ Certification 0 06-16-2004 04:53 PM
Web Form Performance Versus Single File Performance jm ASP .Net 1 12-12-2003 11:14 PM
Trivial performance questions Brian Patterson Python 25 10-20-2003 04:58 PM



Advertisments