I need some help with Ruby query building and query time

[Alpha Blue]

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

 

[Garry Freemyer]

[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 <[email protected]>
To: ruby-talk ML <[email protected]>
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...

 

[Alpha Blue]

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!

 

[Alpha Blue]

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.