--0-1402790597-12487939325727
Content-Type: text/plain; charset=us-ascii

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 <jdezenzio / gmail.com>
To: ruby-talk ML <ruby-talk / ruby-lang.org>
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/.
--0-1402790597-12487939325727--