# Autocomplete lookup speedup

**URL:** <https://www.ruby-forum.com/t/autocomplete-lookup-speedup/169174>\
**Category:** Rails\
**Created:** [May 30, 2009, 8:10pm UTC](https://www.ruby-forum.com/t/autocomplete-lookup-speedup/169174 "2009-05-30T20:10:08Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![bigdaddy71](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/bbe5ce/32.png) [@bigdaddy71](https://www.ruby-forum.com/u/bigdaddy71)\
**Post date:** [May 30, 2009, 8:10pm UTC](https://www.ruby-forum.com/t/autocomplete-lookup-speedup/169174/1 "2009-05-30T20:10:08Z")

</div>

Is there anything I can do to speed up the query of a table that has  
380,000 entries and will only get bigger?

I tried the recipe in the rails recipe book but the page takes a long  
time just for the page to load due to the array being generated.

---

<div class="post-metadata">

**Author:** ![bigdaddy71](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/bbe5ce/32.png) [@bigdaddy71](https://www.ruby-forum.com/u/bigdaddy71)\
**Post date:** [May 30, 2009, 11:57pm UTC](https://www.ruby-forum.com/t/autocomplete-lookup-speedup/169174/2 "2009-05-30T23:57:17Z")

</div>

Have you tried to add a :limit clause to your select statement?

Maybe show only the first 15 results and make sure there’s an index at  
the column being searched for.

- 

Maurício Linhares  
[http://alinhavado.wordpress.com/](http://alinhavado.wordpress.com/) (pt-br) | [http://blog.codevader.com/](http://blog.codevader.com/)  
(en)

---

<div class="post-metadata">

**Author:** ![bigdaddy71](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/bbe5ce/32.png) [@bigdaddy71](https://www.ruby-forum.com/u/bigdaddy71)\
**Post date:** [May 31, 2009, 12:10am UTC](https://www.ruby-forum.com/t/autocomplete-lookup-speedup/169174/3 "2009-05-31T00:10:08Z")

</div>

On May 30, 7:08 pm, Me [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Is there anything I can do to speed up the query of a table that has  
> 380,000 entries and will only get bigger?
> 
> I tried the recipe in the rails recipe book but the page takes a long  
> time just for the page to load due to the array being generated.

Have you got appropriate indexes on the table?

Fred

---

<div class="post-metadata">

**Author:** ![bigdaddy71](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/bbe5ce/32.png) [@bigdaddy71](https://www.ruby-forum.com/u/bigdaddy71)\
**Post date:** [May 31, 2009, 12:18am UTC](https://www.ruby-forum.com/t/autocomplete-lookup-speedup/169174/4 "2009-05-31T00:18:20Z")

</div>

2009/5/30 Maurício Linhares [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

> 

Also, you may also try using the :select to limit what fields you fetch  
from  
your database. If you can provide a sample code, we can better assist  
you.  
Other than that, I would recommend using the

#{RAILS\_ROOT}/script/performance/benchmarker

to profile your queries to make sure that your optimizations are truly  
working  
for the query in question.

Good luck,

-Conrad

---

<div class="post-metadata">

**Author:** ![bigdaddy71](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/bbe5ce/32.png) [@bigdaddy71](https://www.ruby-forum.com/u/bigdaddy71)\
**Post date:** [May 31, 2009, 8:10pm UTC](https://www.ruby-forum.com/t/autocomplete-lookup-speedup/169174/5 "2009-05-31T20:10:15Z")

</div>

Well this is crazy. I do most of my dev work on my computer I use as I  
work  
from home. Running Vista I was able to insert 388,000 records in 2:40  
mins. I hesitated trying to do it on my laptop. BUT!! I am running  
winxp  
on it and I inserted 389,000 records in 40 mins. WOW! also in winxp  
the  
autocomplete lookup runs much faster in XP than Vista. Also it involves  
putting repetitive data in the ier own tables for better normalization.  
This is really more of a test to see how it compares to another system  
we  
use(JAVA) for doing data uploads.

FasterCSV.foreach(‘CIS.csv’) do |row|  
port,ip,circuitid,node,eqpt,org = row

```
ip1 = Ip.find_or_create_by_name(ip).id
node1 = Site.find_or_create_by_name(node).id
ci = ConfigurationItem.find_by_name(port)
eq = Equipment.find_or_create_by_name(eqpt).id
organ = Organization.find_or_create_by_name(org).id
if ci.nil?
    ConfigurationItem.create(:name => port, :org_id => organ

```

,:equipment\_id =\> eq ,:ip\_id =\> ip1, :circuitid =\> circuitid, :site\_id  
=\>  
node1)  
else  
ci.update\_attributes(:name =\> port, :org\_id =\> organ ,  
:equipment\_id  
=\> eq, :ip\_id =\> ip1, :circuitid =\> circuitid, :site\_id =\> node1)

```
end

```

end

---

<div class="post-metadata">

**Author:** ![bigdaddy71](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/bbe5ce/32.png) [@bigdaddy71](https://www.ruby-forum.com/u/bigdaddy71)\
**Post date:** [June 1, 2009, 9:14am UTC](https://www.ruby-forum.com/t/autocomplete-lookup-speedup/169174/6 "2009-06-01T09:14:53Z")

</div>

First, your find\_or\_creates are just a hack to not throw a runtime  
error if you were to try to call id on nil , while also setting  
conditions for your finder… Either way,

Honestly, in my opinion, you’d be better off doing this in pure sql –  
using Rails for this type of stuff is sometimes too slow :-/  
Especially since you’re not really using Rails for anything except the  
getters/setters, it’s not like you’re performing operations on any of  
your queries, you’re just finding records and setting field values

This is just an “initial” stab at what you’re looking at (in MySQL) –  
could probably be beefed up a ton more, who knows:  
FasterCSV.foreach(‘CIS.csv’) do |row|  
port,ip,circuitid,node,eqpt,org = row  
if connection.select\_count(“select count(1) from configuration\_items  
ci where ci.name=’#{port}’”) \> 0  
insert into configuration\_items (name, org\_id, equipment\_id,  
ip\_id, circuitid, site\_id) select ‘#{port}’, (select id from  
organizations o where o.name=’#{org}’), (select id from equipments e  
where e.name=’#{eqpt}’), (select id from ips i where i.name=’#{ip}’),  
(select ‘#{circuit}’), (select id from sites s where s.name=’#{node}’)  
else  
update configuration\_items ci set ci.name=‘1234’, ci.org\_id=  
(select id from organizations o where o.name=‘test’), ci.equipment\_id=  
(select id from equipments e where e.name=‘test’), ci.ip\_id=(select id  
from ips i where i.name=‘test 4’), ci.circuitid=4, ci.site\_id=(select  
id from sites s where s.name=‘test 2’) where ci.name=‘test’  
end  
end

This could _possibly_ be done in one query with an on duplicate key  
update, but I’m honestly not quite familiar enough with MySQL to do  
this complex of a query with DKU, I’m used to just doing things like  
insert or increment with DKU :).

Anyway, run that in query browser or something and see how the times  
compare when you’re not doing any Rails lookups…

Cheers!

---

<div class="post-metadata">

**Author:** ![bigdaddy71](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/bbe5ce/32.png) [@bigdaddy71](https://www.ruby-forum.com/u/bigdaddy71)\
**Post date:** [June 1, 2009, 4:33pm UTC](https://www.ruby-forum.com/t/autocomplete-lookup-speedup/169174/7 "2009-06-01T16:33:58Z")

</div>

Sorry, I got the conditions backwards, it was late 🙂 And I also  
didn’t replace my test data with your erb…

> ```
> insert into configuration_items (name, org_id, equipment_id,
> 
> ```
> 
> ip\_id, circuitid, site\_id) select ‘#{port}’, (select id from  
> organizations o where o.name=‘#{org}’), (select id from equipments e  
> where e.name=‘#{eqpt}’), (select id from ips i where i.name=‘#{ip}’),  
> (select ‘#{circuit}’), (select id from sites s where s.name=‘#{node}’)  
> end  
> end

On Jun 1, 12:06 am, nodoubtarockstar [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)