Forum: Ruby on Rails Speed up counting?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
4b1b2afb2d72022424eb2edc6db4d375?d=identicon&s=25 Günther Lackner (lackyg)
on 2008-11-19 10:46
Hello!

I need to speed up a count of apearances of a distinct category of
entities in a table. I get the categories with the following command:

@asset_classes = ActiveRecord::Base.connection.select_values "SELECT
DISTINCT CLASS FROM SymbolsMatch_MASTER ORDER BY CLASS"

Now I need to create a list of this categories together with the number
of apearance in the table. Momentarily I use this syntax but it is VERY
slow.





<ul>
 <% for asset_class in @asset_classes do %>
 <%  count = DeviceToUpdate.find(:all, :conditions => ["status like
'moreinfo' and CLIENT_STATUS is NULL and CLASS like ?",asset_class ]
).count
        if count > 0 %>
  <li>
   <a href="<%= url_for(:controller => :more_info, :action =>
:show_class, :class_name => asset_class) %>"><%= truncate(asset_class,
20, ".") %>  (<%= count.to_s %>)</a>
        </li>
  <% end %>
 <% end %>
</ul>





Thank you for any suggestion how to speed this up!

lacky
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2008-11-19 11:00
(Received via mailing list)
On 19 Nov 2008, at 09:46, Günther Lackner wrote:

> number
> of apearance in the table. Momentarily I use this syntax but it is
> VERY
> slow.
>

If you just want to count items then use DeviceToUpdate.count ...
which will use an sql count rather than fetching the entire collection
into memory and counting that (although arrays don't have a count
method so i'm not sure how the code below is working at all). An
appropriate index would also help counting, and/or you could cache the
result

Fred
4b1b2afb2d72022424eb2edc6db4d375?d=identicon&s=25 Günther Lackner (lackyg)
on 2008-11-19 11:46
Hi!

> If you just want to count items then use DeviceToUpdate.count ...

You mean like this?

count = DeviceToUpdate.count(:conditions => ["status like 'moreinfo' and
CLIENT_STATUS is NULL and CLASS like ?",asset_class ] )



lacky
00973881979aa0a660ffbbb2f7a907fb?d=identicon&s=25 Peter De Berdt (Guest)
on 2008-11-19 12:33
(Received via mailing list)
On 19 Nov 2008, at 11:46, Günther Lackner wrote:

>> If you just want to count items then use DeviceToUpdate.count ...
>
> You mean like this?
>
> count = DeviceToUpdate.count(:conditions => ["status like 'moreinfo'
> and
> CLIENT_STATUS is NULL and CLASS like ?",asset_class ] )

Also keep in mind that in MySQL InnoDB "like" searches are unindexed
and notoriously slow in the first place.

Compare it to having the find the number of occurrences of the word
"rails" in a book. One book offers no index at the back of the book,
so you have to manually go over every page finding the word (MySQL
LIKE search), while the other does have one and you only need to count
the pages it appears on listed next to it (Fulltext indexer)


Best regards

Peter De Berdt
Fac81a9d95c81d817a2debb6f717f3ac?d=identicon&s=25 Sazima (Guest)
on 2008-11-19 15:29
(Received via mailing list)
Man, you should really take a look at:

http://api.rubyonrails.org/classes/ActiveRecord/Ca...

Cheers, Sazima

On Nov 19, 7:46 am, Günther Lackner <rails-mailing-l...@andreas-s.net>
4b1b2afb2d72022424eb2edc6db4d375?d=identicon&s=25 Günther Lackner (lackyg)
on 2008-11-19 15:53
What do you mean?

Sazima wrote:
> Man, you should really take a look at:
>
> http://api.rubyonrails.org/classes/ActiveRecord/Ca...
>
> Cheers, Sazima
>
> On Nov 19, 7:46�am, G�nther Lackner <rails-mailing-l...@andreas-s.net>
This topic is locked and can not be replied to.