# Reduce Number of Queries When Using ActiveRecord

**URL:** <https://www.ruby-forum.com/t/reduce-number-of-queries-when-using-activerecord/57533>\
**Category:** Rails\
**Created:** [April 11, 2006, 8:14pm UTC](https://www.ruby-forum.com/t/reduce-number-of-queries-when-using-activerecord/57533 "2006-04-11T20:14:46Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\
**Post date:** [April 11, 2006, 8:14pm UTC](https://www.ruby-forum.com/t/reduce-number-of-queries-when-using-activerecord/57533/1 "2006-04-11T20:14:46Z")

</div>

I have a controller method called update\_all that grabs parameters for  
about  
30 form fields and saves them to the database. My current code looks to  
see  
if there is a string in params[] for each form field (two fields for  
each  
SelfEvaluationItem) and saves the updated self\_evaluation\_answer if a  
string  
is available. Since this is iterated code, I am almost certain that  
this  
generates 30 or so SQL queries.

Is there a way to build up all of the self\_evaluation\_answers into a  
larger  
unit (model array?) and then save them all at once? The goal of course  
would be to reduce the number of queries to just 1. I guess the  
alternate  
would be to build a SQL query to do this and execute it, but I prefer to  
work in Ruby and Rails if possible.

Any help is very much appreciated!

I have included the source for reference below:

def update\_all  
for item in SelfEvaluationItem.find\_all  
id = params[:self\_evaluation\_answer][item.id.to\_s][:id]  
comment = params[:self\_evaluation\_answer][item.id.to\_s][:comment]  
response =  
params[:self\_evaluation\_answer][item.id.to\_s][:response]  
if !comment.empty? or !response.empty?  
@self\_evaluation\_answer = SelfEvaluationAnswer.find(id)  
@self\_evaluation\_answer.comment = comment unless comment.empty?  
@self\_evaluation\_answer.response = response unless  
response.empty?  
@self\_evaluation\_answer.save  
end  
end  
flash[:notice] = “Save Completed”  
end

---

<div class="post-metadata">

**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\
**Post date:** [April 11, 2006, 8:41pm UTC](https://www.ruby-forum.com/t/reduce-number-of-queries-when-using-activerecord/57533/2 "2006-04-11T20:41:36Z")

</div>

Eden B. wrote:

> I have a controller method called update\_all that grabs parameters for  
> about  
> 30 form fields and saves them to the database. My current code looks to  
> see  
> if there is a string in params[] for each form field (two fields for  
> each  
> SelfEvaluationItem) and saves the updated self\_evaluation\_answer if a  
> string  
> is available. Since this is iterated code, I am almost certain that  
> this  
> generates 30 or so SQL queries.
> 
> Is there a way to build up all of the self\_evaluation\_answers into a  
> larger  
> unit (model array?) and then save them all at once? The goal of course  
> would be to reduce the number of queries to just 1. I guess the  
> alternate  
> would be to build a SQL query to do this and execute it, but I prefer to  
> work in Ruby and Rails if possible.
> 
> Any help is very much appreciated!
> 
> I have included the source for reference below:
> 
> def update\_all  
> for item in SelfEvaluationItem.find\_all  
> id = params[:self\_evaluation\_answer][item.id.to\_s][:id]  
> comment = params[:self\_evaluation\_answer][item.id.to\_s][:comment]  
> response =  
> params[:self\_evaluation\_answer][item.id.to\_s][:response]  
> if !comment.empty? or !response.empty?  
> @self\_evaluation\_answer = SelfEvaluationAnswer.find(id)  
> @self\_evaluation\_answer.comment = comment unless comment.empty?  
> @self\_evaluation\_answer.response = response unless  
> response.empty?  
> @self\_evaluation\_answer.save  
> end  
> end  
> flash[:notice] = “Save Completed”  
> end

If I’m reading this correctly, what you’re doing is cycling through  
every single item in your SelfEvaluationItem table to compare each item  
against the entries in params[:self\_evaluation\_answer]. Wouldn’t it be  
more efficient to cycle through params[:self\_evaluation\_answer] and  
refer to evaluation items based on the results?

For example:

def update\_all  
params[:self\_evaluation\_answer].each do |answer|  
answer.each\_key do |id\_from\_answer|  
item = SelfEvaluationAnswer.find(id\_from\_answer.to\_i)  
new\_comment = answer[id\_from\_answer][:comment]  
new\_response = answer[id\_from\_answer][:response]  
unless new\_comment.empty? and new\_response.empty?  
@self\_evaluation\_answer = item  
@self\_evaluation\_answer.comment = new\_comment unless  
new\_comment.empty?  
@self\_evaluation\_answer.response = new\_response unless  
new\_response.empty?  
@self\_evaluation\_answer.save  
end  
end  
end  
flash[:notice] = “Save Completed”  
end

Again I’m hoping I have your read your code correctly, but what I  
attemped to do here is to loop through the  
params[:self\_evaluation\_answer] hash, which seems to have keys that are  
String versions of your SelfEvaluationItem primary key ids.

So we take each key and use it to find the SelfEvaluationItemAnswer  
record which matches that id. Then if there’s a new comment or response  
we add them to the record, and save it.

I added descriptive flash messages indicating when an error prevented  
the record from saving or when the record didn’t need to be updated.

I’m not sure this really addresses the gist of your question, but I  
thought I’d offer it as an alternative that might be slightly more  
efficient. Feel free to disregard if this doesn’t suit your needs for  
whatever reason.

Jeff C.man

---

<div class="post-metadata">

**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\
**Post date:** [April 11, 2006, 9:42pm UTC](https://www.ruby-forum.com/t/reduce-number-of-queries-when-using-activerecord/57533/3 "2006-04-11T21:42:54Z")

</div>

Jeff,

Thank you for the suggestions. I really like your approach better than  
mine  
for aesthetic reasons, but it seems that you have introduced an extra  
SQL  
query with each iteration:

item = SelfEvaluationAnswer.find(id\_from\_answer.to\_i)

Since the form will return a valid key for every field (even when left  
blank), I think this is guaranteed to more than double the number of  
queries  
versus the code I have now. Currently I only generate a query before  
entering the loop and once for each form item that has content (blank  
form  
items don’t generate and Update query).

To be honest, it is a little early in the development of this  
application to  
worry much about optimization, but I realized that using SQL I could  
probably do all the UPDATE queries in my loop in a single statement and  
thought I could nip this problem in the bud while still using  
ActiveRecord.

Thanks again for your contribution. I will probably at least reorganize  
some of my code based on your suggestions.

Eden

---

<div class="post-metadata">

**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\
**Post date:** [April 11, 2006, 10:33pm UTC](https://www.ruby-forum.com/t/reduce-number-of-queries-when-using-activerecord/57533/4 "2006-04-11T22:33:55Z")

</div>

Eden B. wrote:

> Jeff,
> 
> Thank you for the suggestions. I really like your approach better than  
> mine  
> for aesthetic reasons, but it seems that you have introduced an extra  
> SQL  
> query with each iteration:
> 
> item = SelfEvaluationAnswer.find(id\_from\_answer.to\_i)
> 
> Since the form will return a valid key for every field (even when left  
> blank), I think this is guaranteed to more than double the number of  
> queries  
> versus the code I have now. Currently I only generate a query before  
> entering the loop and once for each form item that has content (blank  
> form  
> items don’t generate and Update query).
> 
> To be honest, it is a little early in the development of this  
> application to  
> worry much about optimization, but I realized that using SQL I could  
> probably do all the UPDATE queries in my loop in a single statement and  
> thought I could nip this problem in the bud while still using  
> ActiveRecord.
> 
> Thanks again for your contribution. I will probably at least reorganize  
> some of my code based on your suggestions.
> 
> Eden

Eden,

I see what you mean abou the SQL query–it was my mistake, but the line

item = SelfEvaluationAnswer.find(id\_from\_answer.to\_i)

could be placed at the top of the conditional block, the way you had it  
in your original. That would at least make it equal to your original  
version in SQL calls, if we exclude your original find\_all.

I’m not a SQL expert but I’ve been thinking about how to optimize your  
overall number of SQL queries. I’m guessing with enough thought, that a  
sufficient :condition statement could be amalgamated from your  
parameters that would do what you’re looking for.

I’ll let you know if anything comes up.

Jeff C.man

---

<div class="post-metadata">

**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\
**Post date:** [April 11, 2006, 11:02pm UTC](https://www.ruby-forum.com/t/reduce-number-of-queries-when-using-activerecord/57533/5 "2006-04-11T23:02:35Z")

</div>

Eden B. wrote:

> To be honest, it is a little early in the development of this  
> application to  
> worry much about optimization, but I realized that using SQL I could  
> probably do all the UPDATE queries in my loop in a single statement and  
> thought I could nip this problem in the bud while still using  
> ActiveRecord.

Eden,

Try this. What I’ve done is basically make a new updated\_answers Hash  
using only the answers which have had comment or response updated. I’ve  
converted the index from a string to an integer so we can use it to find  
all the related SelfEvaluationItem records.

I believe you’ll still need to cycle through the array and save each  
record individually, but at least the query can be reduced to a single  
statement.

def update\_all  
updated\_answers = {}  
params[:self\_evaluation\_answer].each do |answer|  
answer.each\_key do |id\_from\_answer|  
new\_comment = answer[id\_from\_answer][:comment]  
new\_response = answer[id\_from\_answer][:response]

```
  updated_answers[id_from_answer.to_i] = answer[id_from_answer] 

```

unless (new\_comment.empty? and new\_response.empty?)  
end  
end  
updated\_items = SelfEvaluationAnswer.find(updated\_answers.keys)  
updated\_items.each do |item|  
new\_comment = updated\_answers[item.id][:comment]  
new\_response = updated\_answers[item.id][:response]

```
item.comment = new_comment unless new_comment.empty?
item.response = new_response unless new_response.empty?
item.save

```

end  
flash[:notice] = “Save completed.”  
end

There may be a more efficient way of doing this, but this is what I’ve  
come up with. Hope it helps you some,

Jeff C.man

---

<div class="post-metadata">

**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\
**Post date:** [April 12, 2006, 10:34pm UTC](https://www.ruby-forum.com/t/reduce-number-of-queries-when-using-activerecord/57533/6 "2006-04-12T22:34:56Z")

</div>

Thank you for your help Jeff. I will look at reducing the number of  
queries  
again when I start optimizing the application.

---

<div class="post-metadata">

**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\
**Post date:** [April 16, 2006, 5:09pm UTC](https://www.ruby-forum.com/t/reduce-number-of-queries-when-using-activerecord/57533/7 "2006-04-16T17:09:23Z")

</div>

Jeff and others following this thread,

I found what looks like a good solution to this problem over at Rails  
Weenie:

[http://rails.techno-weenie.net/tip/2006/3/15/speed\_up\_inserts\_with\_transactions](http://rails.techno-weenie.net/tip/2006/3/15/speed_up_inserts_with_transactions)

I think if I wrap my updates in a transaction, then I should get a  
single  
query to the db. I will need to do some testing to make sure that I get  
the  
expected results if the transaction fails, but I think this is a  
possible  
solution to my original question (reduce queries from 32-\>2).

Thanks again,

Eden