# What's the best way to run (many) complex queries in a rake task?

**URL:** <https://www.ruby-forum.com/t/whats-the-best-way-to-run-many-complex-queries-in-a-rake-task/168964>\
**Category:** Rails\
**Created:** [May 27, 2009, 9:11pm UTC](https://www.ruby-forum.com/t/whats-the-best-way-to-run-many-complex-queries-in-a-rake-task/168964 "2009-05-27T21:11:40Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![pahagon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac8455/32.png) [@pahagon](https://www.ruby-forum.com/u/pahagon)\
**Post date:** [May 27, 2009, 9:11pm UTC](https://www.ruby-forum.com/t/whats-the-best-way-to-run-many-complex-queries-in-a-rake-task/168964/1 "2009-05-27T21:11:40Z")

</div>

Hi everyone,

I’m currently working on a project which will execute a rake task from  
time to time, and said taks involves a complex recommendation  
algorithm that can be accomplished with a complex SQL query.

I may use a single one to do everything, or a lot of them, one per  
item to be computed. The nature of the query isn’t in cause here, it’s  
just

#################  
######## ONE SINGLE QUERY  
query “INSERT INTO deviations(pivot\_id, deviant\_id, value)  
SELECT  
ratings.content\_id as pivot\_id,  
ratings2.content\_id as deviant\_id,  
AVG(ratings.rate - ratings2.rate) as value  
FROM ratings  
INNER JOIN ratings as ratings2  
ON ratings.viewer\_id = ratings2.viewer\_id  
AND ratings.viewer\_type = ratings2.viewer\_type  
AND ratings.content\_id \< ratings2.content\_id  
GROUP BY pivot\_id, deviant\_id”

ActiveRecord::Base.connection().execute(query)

######## MULTIPLE QUERIES  
sql = ActiveRecord::Base.connection()  
ids.each do |id|  
query = “INSERT INTO deviations(pivot\_id, deviant\_id, value)  
SELECT  
#{id.to\_i} as pivot\_id,  
ratings2.content\_id as deviant\_id,  
AVG(ratings.rate - ratings2.rate) as value  
FROM ratings  
INNER JOIN ratings as ratings2  
ON ratings.viewer\_id = ratings2.viewer\_id  
AND ratings.viewer\_type = ratings2.viewer\_type  
AND ratings.content\_id = #{id.to\_i}  
AND ratings.content\_id \< ratings2.content\_id  
GROUP BY deviant\_id”  
sql.execute(query)  
end  
#################

Just for curiosity sake, I have an index on [viewer\_id, viewer\_type,  
content\_id]

I teste performance, for the single query variant and the multiple  
queries variant, and then changed MySQL so I can have more memory and  
the temp tables aren’t stored in the hard drive.  
After changing MySQL settings, the single query variant improved,  
(268sec vs 80sec) but the multiple queries variant took almost twice  
the time. (190sec vs 390sec) What the…??

I checked process info, and it’s just using one of the four available  
CPUs.

What can I do to improve this performance?  
How to use all the available CPUs?  
Is it to create multiple threads inside the rake task, and then use a  
connection per task with multiple queries??  
Am I doing something fundamentally wrong here?

Thanks in advance for some tips, I’m really out of ideas, and this is  
my master thesis that ends in two weeks ☹

Cheers,

Paulo P.

THE NEW MYSQL OPTIONS:  
[mysqld]  
sort\_buffer\_size=2M  
read\_buffer\_size=2M  
join\_buffer\_size=2M

read\_rnd\_buffer\_size=2M

max\_heap\_table\_size=256M  
tmp\_table\_size=256M

myisam\_sort\_buffer\_size=64M

thread\_cache=256

query\_cache\_type=1  
query\_cache\_limit=1M  
query\_cache\_size=32M

---

<div class="post-metadata">

**Author:** ![pahagon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac8455/32.png) [@pahagon](https://www.ruby-forum.com/u/pahagon)\
**Post date:** [May 27, 2009, 9:59pm UTC](https://www.ruby-forum.com/t/whats-the-best-way-to-run-many-complex-queries-in-a-rake-task/168964/2 "2009-05-27T21:59:47Z")

</div>

On May 27, 8:11 pm, Paulo P. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> #################  
> AND ratings.content\_id \< ratings2.content\_id  
> ratings2.content\_id as deviant\_id,  
> #################
> 
> Just for curiosity sake, I have an index on [viewer\_id, viewer\_type,  
> content\_id]
> 
> I teste performance, for the single query variant and the multiple  
> queries variant, and then changed MySQL so I can have more memory and  
> the temp tables aren’t stored in the hard drive.  
> After changing MySQL settings, the single query variant improved,  
> (268sec vs 80sec) but the multiple queries variant took almost twice  
> the time. (190sec vs 390sec) What the…??

hard to say without knowing what you changed. Take a look at your  
query plans (use explain) to see if your queries could be better. If  
your tables are innodb you’ll might also want to increase the innodb  
buffer pool size

> I checked process info, and it’s just using one of the four available  
> CPUs.
> 
> What can I do to improve this performance?  
> How to use all the available CPUs?

> Is it to create multiple threads inside the rake task, and then use a  
> connection per task with multiple queries??  
> Am I doing something fundamentally wrong here?

Running multiple queries in parallel won’t help if you are IO bound  
(also unfortunately running a mysql query with the standard ruby mysql  
gem will block all threads in the ruby interpreter).

Fred

---

<div class="post-metadata">

**Author:** ![pahagon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac8455/32.png) [@pahagon](https://www.ruby-forum.com/u/pahagon)\
**Post date:** [May 28, 2009, 10:08am UTC](https://www.ruby-forum.com/t/whats-the-best-way-to-run-many-complex-queries-in-a-rake-task/168964/3 "2009-05-28T10:08:43Z")

</div>

Use the spawn plugin to fork new processes for each query once you  
address the database optimizations.

---

<div class="post-metadata">

**Author:** ![pahagon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac8455/32.png) [@pahagon](https://www.ruby-forum.com/u/pahagon)\
**Post date:** [May 29, 2009, 12:50pm UTC](https://www.ruby-forum.com/t/whats-the-best-way-to-run-many-complex-queries-in-a-rake-task/168964/4 "2009-05-29T12:50:21Z")

</div>

> Just for curiosity sake, I have an index on [viewer\_id, viewer\_type,  
> content\_id]

Maybe, you want to include the rate in the index as well? That way,  
everything is in the index, and I would presume, MYSQL will not have to  
access the table at all.