Performance questions (SQL-statements)

[Piet L.]

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.

 

[Sherm Pendley]

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--

 

[Gregory Toomey]

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/beta/viewipo.cgi?report=advanced

and hints on how to do it here:
http://www.gregorytoomey.com/index.php?option=content&task=view&id=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

 

[xhoster]

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

 

[Eric Schwartz]

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

 

[Eric Schwartz]

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

 

[xhoster]

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).


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

 

[Eric Schwartz]

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

 

[Piet L.]

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

 

[JayEs]

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??

 

[Keith Keller]

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.

Why are you still asking here? It's not a perl problem, apparently, so
you should be asking an SQL group or a group for your particular
backend.

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

Why are you asking here? It's not a perl problem, so you should be
asking a) your sysadmin, or b) in a group about your webserver.

--keith