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 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/ (pt-br) | http://blog.codevader.com/ (en) On Sat, May 30, 2009 at 3:08 PM, Me <chabgood-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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. > > >
On May 30, 7:08 pm, Me <chabg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
2009/5/30 Maurício Linhares <mauricio.linhares-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>> > 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/ (pt-br) | http://blog.codevader.com/ (en) > > > > On Sat, May 30, 2009 at 3:08 PM, Me <chabgood-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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. > > > > > >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> > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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
On Sat, May 30, 2009 at 5:12 PM, Conrad Taylor
<conradwt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> 2009/5/30 Maurício Linhares
<mauricio.linhares-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
>
>>
>> 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/ (pt-br) |
http://blog.codevader.com/(en)
>>
>>
>>
>> On Sat, May 30, 2009 at 3:08 PM, Me
<chabgood-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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.
>> > >
>> >
>>
>
> 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
>
>
>>
>>
>>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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!
On May 31, 12:09 pm, Chris Habgood
<chabg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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
>
> On Sat, May 30, 2009 at 5:12 PM, Conrad Taylor
<conra...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > 2009/5/30 Maurício Linhares
<mauricio.linha...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
>
> >> 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/(pt-br)
|http://blog.codevader.com/(en)
>
> >> On Sat, May 30, 2009 at 3:08 PM, Me
<chabg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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.
>
> > 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
Sorry, I got the conditions backwards, it was late :) And I also didn''t replace my test data with your erb...> 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 > update configuration_items ci set ci.name=''#{port}'', ci.org_id> (select id from organizations o where o.name=''#{org}''), ci.equipment_id> (select id from equipments e where e.name=''#{eqpt}''), ci.ip_id=(select id > from ips i where i.name=''#{ip}''), ci.circuitid=4, ci.site_id=(select > id from sites s where s.name=''#{node}'') where ci.name=''#{port}'' > else > 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 > endOn Jun 1, 12:06 am, nodoubtarockstar <jenniferwendl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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! > > On May 31, 12:09 pm, Chris Habgood <chabg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > 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 > > > On Sat, May 30, 2009 at 5:12 PM, Conrad Taylor <conra...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > 2009/5/30 Maurício Linhares <mauricio.linha...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > >> 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/(pt-br) |http://blog.codevader.com/(en) > > > >> On Sat, May 30, 2009 at 3:08 PM, Me <chabg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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. > > > > 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