# I need some help with Ruby query building and query time **URL:** \ **Category:** Ruby\ **Created:** [July 28, 2009, 4:24pm UTC](https://www.ruby-forum.com/t/i-need-some-help-with-ruby-query-building-and-query-time/172821 "2009-07-28T16:24:54Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\ **Post date:** [July 28, 2009, 4:24pm UTC](https://www.ruby-forum.com/t/i-need-some-help-with-ruby-query-building-and-query-time/172821/1 "2009-07-28T16:24:54Z")
Here is a pastie of my current code. In the formatting on pastie, it’s not listed entirely right. [http://pastie.org/561671](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… ---
**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\ **Post date:** [July 28, 2009, 5:13pm UTC](https://www.ruby-forum.com/t/i-need-some-help-with-ruby-query-building-and-query-time/172821/2 "2009-07-28T17:13:24Z")
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 B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) To: ruby-talk ML [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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](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… ---
**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\ **Post date:** [July 28, 2009, 5:24pm UTC](https://www.ruby-forum.com/t/i-need-some-help-with-ruby-query-building-and-query-time/172821/3 "2009-07-28T17:24:00Z")
Garry F. 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! ---
**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\ **Post date:** [July 28, 2009, 8:52pm UTC](https://www.ruby-forum.com/t/i-need-some-help-with-ruby-query-building-and-query-time/172821/4 "2009-07-28T20:52:20Z")
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.