I need some help with Ruby query building and query time

Discussion in 'Ruby' started by Alpha Blue, Jul 28, 2009.

  1. Alpha Blue

    Alpha Blue Guest

    Here is a pastie of my current code. In the formatting on pastie, it's
    not listed entirely right.

    http://pastie.org/561671

    The Select, Joins, Conditions, and Order statements are on separate
    lines.

    The issue I'm having is I'm finding all types of variations of query
    times when I use this query. If I add them all to one solid line, at
    times the query times go down and are much faster. If I separate them
    in my IDE for readability, they are sent a little bit differently to
    mysql and at times the query times go much higher.

    With the same exact query I can sometimes reach 0.587ms or I can go up
    to 0.24357ms, the latter being a crazy number.

    So, I have been taught that the larger the query the better. This query
    isn't even finished yet.

    My situation is that I have 37 statistical tables. Each of these tables
    has roughly 14 columns each. So, all I'm doing is joining "one" column
    from each table. In the overall scheme of things, it will look like:

    4 columns from teams table +
    37 single columns from 37 table joins +
    2 full table joins of (14 columns each).

    Yes, that's a big query. I most likely will have to tailor it down some
    but I shouldn't be getting this large of a query return on this small of
    a query that I have built right now. It's roughly 25 columns right now
    and mysql when I process it directly shows a return of 0.235ms.

    So, what can I do to fix this issue and is there something wrong with my
    code that might be making the statement too long or too chopped up?
    And, why does code formatting with line separations increase the time?

    Many thanks in advance...
    --
    Posted via http://www.ruby-forum.com/.
    Alpha Blue, Jul 28, 2009
    #1
    1. Advertising

  2. [Note: parts of this message were removed to make it a legal post.]

    Well, the first thing I would suggest is that unless you are using an ancient version of mySql that doesn't support stored procedures that you convert this into a stored procedure in the database and simply pass in your unique parameters to it.

    The advantages of stored procedures are

    1. They are optimized (some would say compiled) for the database and will run faster.
    2. They are kept separate and secret from the code, and are secure from injection attacks that can trick your program into thinking a string parameter is a command to run instead of a parameter to compare or sort by.

    3. One place to store things. When all your database select logic is in the database, there is just one place to edit it at.




    ________________________________
    From: Alpha Blue <>
    To: ruby-talk ML <>
    Sent: Tuesday, July 28, 2009 7:24:59 AM
    Subject: I need some help with Ruby query building and query time

    Here is a pastie of my current code. In the formatting on pastie, it's
    not listed entirely right.

    http://pastie.org/561671

    The Select, Joins, Conditions, and Order statements are on separate
    lines.

    The issue I'm having is I'm finding all types of variations of query
    times when I use this query. If I add them all to one solid line, at
    times the query times go down and are much faster. If I separate them
    in my IDE for readability, they are sent a little bit differently to
    mysql and at times the query times go much higher.

    With the same exact query I can sometimes reach 0.587ms or I can go up
    to 0.24357ms, the latter being a crazy number.

    So, I have been taught that the larger the query the better. This query
    isn't even finished yet.

    My situation is that I have 37 statistical tables. Each of these tables
    has roughly 14 columns each. So, all I'm doing is joining "one" column
    from each table. In the overall scheme of things, it will look like:

    4 columns from teams table +
    37 single columns from 37 table joins +
    2 full table joins of (14 columns each).

    Yes, that's a big query. I most likely will have to tailor it down some
    but I shouldn't be getting this large of a query return on this small of
    a query that I have built right now. It's roughly 25 columns right now
    and mysql when I process it directly shows a return of 0.235ms.

    So, what can I do to fix this issue and is there something wrong with my
    code that might be making the statement too long or too chopped up?
    And, why does code formatting with line separations increase the time?

    Many thanks in advance...
    --
    Posted via http://www.ruby-forum.com/.
    Garry Freemyer, Jul 28, 2009
    #2
    1. Advertising

  3. Alpha Blue

    Alpha Blue Guest

    Garry Freemyer wrote:
    > Well, the first thing I would suggest is that unless you are using an
    > ancient version of mySql that doesn't support stored procedures that you
    > convert this into a stored procedure in the database and simply pass in
    > your unique parameters to it.
    >
    > The advantages of stored procedures are
    >
    > 1. They are optimized (some would say compiled) for the database and
    > will run faster.
    > 2. They are kept separate and secret from the code, and are secure from
    > injection attacks that can trick your program into thinking a string
    > parameter is a command to run instead of a parameter to compare or sort
    > by.
    >
    > 3. One place to store things. When all your database select logic is in
    > the database, there is just one place to edit it at.
    >


    Hi Garry,

    I'm still new to mysql and didn't know about stored procedures. I will
    look into that and research how to implement it with Ruby. Many thanks!
    --
    Posted via http://www.ruby-forum.com/.
    Alpha Blue, Jul 28, 2009
    #3
  4. Alpha Blue

    Alpha Blue Guest

    Just as a follow-up:

    I checked out the stored procedures and found that they were a bit too
    heavy on server memory, which in my case is very important. Further,
    with some of the routines I would need to create, it would be very
    difficult to rely on them fully. I definitely see a lot of pros with
    stored procedures but I see a lot of cons as well.

    I ended up disecting my model.find routine into 5 separate routines with
    5 variable returns.

    This changed my time from:

    8000 MS or 8 seconds TO:
    122 Milliseconds = 0.122 Seconds

    In my case, I just needed to reduce the query size into smaller chunks.
    Many people say to go with larger queries but in this case the query was
    too large.

    Thanks.
    --
    Posted via http://www.ruby-forum.com/.
    Alpha Blue, Jul 28, 2009
    #4
    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. Guest
    Replies:
    0
    Views:
    678
    Guest
    Feb 25, 2004
  2. Sam Watson
    Replies:
    9
    Views:
    302
    Sam Watson
    Dec 12, 2003
  3. Domino
    Replies:
    5
    Views:
    376
    dorayme
    Nov 5, 2006
  4. flamesrock
    Replies:
    8
    Views:
    449
    Hendrik van Rooyen
    Nov 24, 2006
  5. Replies:
    1
    Views:
    93
Loading...

Share This Page