Performance questions (SQL-statements)

Discussion in 'Perl Misc' started by Piet L., Feb 26, 2005.

  1. Piet L.

    Piet L. Guest

    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::DBI->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.
     
    Piet L., Feb 26, 2005
    #1
    1. Advertising

  2. 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
     
    Sherm Pendley, Feb 26, 2005
    #2
    1. Advertising

  3. 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::DBI->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
     
    Gregory Toomey, Feb 26, 2005
    #3
  4. Piet L.

    Guest

    (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
     
    , Feb 26, 2005
    #4
  5. Sherm Pendley <> 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.
     
    Eric Schwartz, Feb 28, 2005
    #5
  6. writes:
    > (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.
     
    Eric Schwartz, Feb 28, 2005
    #6
  7. Piet L.

    Guest

    Eric Schwartz <> wrote:
    > writes:
    > > (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
     
    , Feb 28, 2005
    #7
  8. writes:
    > Eric Schwartz <> 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.
     
    Eric Schwartz, Mar 1, 2005
    #8
  9. Piet L.

    Piet L. Guest

    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
     
    Piet L., Mar 1, 2005
    #9
  10. Piet L.

    JayEs Guest

    "Piet L." <> wrote in message
    news:...
    > 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??
     
    JayEs, Mar 1, 2005
    #10
  11. Piet L.

    Keith Keller Guest

    On 2005-03-01, Piet L. <> wrote:
    > 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

    --
    -francisco.ca.us
    (try just my userid to email me)
    AOLSFAQ=http://wombat.san-francisco.ca.us/cgi-bin/fom
    see X- headers for PGP signature information
     
    Keith Keller, Mar 1, 2005
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Neil Zanella
    Replies:
    8
    Views:
    1,233
    mfmehdi
    Oct 20, 2006
  2. Harry George
    Replies:
    6
    Views:
    444
    Bart Nessux
    Feb 23, 2004
  3. Vince
    Replies:
    12
    Views:
    776
    Martin Gregorie
    Jan 21, 2008
  4. JT
    Replies:
    1
    Views:
    126
    Bob Barrows [MVP]
    Sep 27, 2005
  5. John Crichton
    Replies:
    6
    Views:
    290
    John Crichton
    Jul 12, 2010
Loading...

Share This Page