# Performance bottleneck

**URL:** <https://www.ruby-forum.com/t/performance-bottleneck/109408>\
**Category:** Ferret\
**Created:** [July 14, 2007, 1:14pm UTC](https://www.ruby-forum.com/t/performance-bottleneck/109408 "2007-07-14T13:14:53Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![golak\_Sarangi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/3bc359/32.png) [@golak\_Sarangi](https://www.ruby-forum.com/u/golak_Sarangi)\
**Post date:** [July 14, 2007, 1:14pm UTC](https://www.ruby-forum.com/t/performance-bottleneck/109408/1 "2007-07-14T13:14:53Z")

</div>

I have got my database in Mysql. I used ferret to index a table with 10  
million rows. On limiting the selection of data to 1000 initial  
retrieval,  
it takes 200 seconds but for the whole table it took more than four  
hours  
and after which i had to close my indexing application. I used the  
StandardAnalyser for it. There is no problem from the database side as  
retrieval of all the data in the table into a document takes very less  
time.  
So please suggest me ways to overcome it.

---

<div class="post-metadata">

**Author:** ![golak\_Sarangi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/3bc359/32.png) [@golak\_Sarangi](https://www.ruby-forum.com/u/golak_Sarangi)\
**Post date:** [July 14, 2007, 4:41pm UTC](https://www.ruby-forum.com/t/performance-bottleneck/109408/2 "2007-07-14T16:41:07Z")

</div>

Hi,

here’s what I did to speed up index rebuilds:

1. Database

* * *

Use raw SQL to retrieve objects from the DB with  
ModelClass.connection.select\_all() instead of Model.find\_by\_sql().  
This will prevent ActiveRecord objects from being instantiated for  
every row, which is fairly expensive.

Instead of ActiveRecord objects, select\_all() returns hashes with  
string keys. You need to access the values with object[‘name’]  
instead of object.name. Another consequence is that helper methods  
such as

def full\_name  
last\_name + ’ ’ + first\_name  
end

are not available anymore. You have to use SQL for doing this:

“SELECT …, CONCAT(first\_name, ’ ', last\_name) AS full\_name, …”.

Another important point are associations. If you index objects with a  
has\_many association, the objects themselves are loaded in a batch  
statement but the associateded objects are loaded separately.

Assume, for example, you index Articles which have many Comments. If  
the Articles are loaded in batches of 1000, there will be another  
1000 statements for loading the comments.

With ActiveRecord find() you can use the :include option as a remedy.  
With raw SQL you have to use joins and grouping to get all the data  
you need in one row. Hint: have a look at the group functions that  
your RDBMS supports.

Select only the columns that you need for indexing. Instead of  
“SELECT products.\*” use “SELECT product.name,  
product.description,…”. This is especially important if your table  
includes BLOB or CLOB columns.

Bottom line: Brush up your SQL skills and do as much work as possible  
inside the database. ActiveRecord is definitely not made for batch  
processing, especially when dealing with millions of rows.

1. Analyzers

* * *

Use fast analyzers if possible. StandardAnalyzer is fine for most  
purposes but it includes HyphenFilter and StopFilter which are slow.  
Consider using lightweight LetterAnalyzer or WhiteSpaceAnalyzer or  
even no analyzer at all for particular fields. You can set individual  
analyzers for different fields using PerFieldAnalyzer.

Don’t be reluctant to write your own custom analyzer. I’m using an  
analyzer written in pure Ruby based on regular expressions. For its  
particular purpose, it’s as fast as StandardAnalyzer but yields  
better results. Text processing in Ruby is actually pretty fast.

1. Ferret

* * *

Tweak the Index parameters for rebuilds. See the Ferret documentation  
on what you can do. Here’s an example

i = Ferret::Index::Index.new(  
…,  
:max\_buffer\_memory =\> 0x8000000,  
:max\_buffered\_docs =\> 0x10000,  
:merge\_factor =\> 0x10,  
:term\_index\_interval =\> 0x40,  
:doc\_skip\_interval =\> 0x8  
)

Experiment with these values, but be careful! Setting them  
arbitrarily can cause Ferret to crash. Start with the values above  
and try to tweak one at time.

This is the last measure in optimization. Don’t bother fiddling with  
these parameters unless you get steps 1. and 2. right.

Hope this helps.

Cheers.  
Andreas