Forum: Ruby on Rails Best way to go - query large database?

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.
85ba58eed034056754d3760749897ab9?d=identicon&s=25 Rudy (Guest)
on 2007-01-16 20:28
(Received via mailing list)
Hi, looking for the best solution here. This is the situation: I have a
large database (at least 100000 records). I have to find all the
records from a group of people from a certain date, and after that I
have to query and make calculations with a set of those records from
every member of that group.
Looping through every member of the group, and calculating, would take
me very long, I'm afraid. I was wondering if it is possible to do the
first query, and then loop (query) through the Array without going to
the database again. To do a series of query on a (much smaller) Array,
that is.
Is that possible? If so, how? Is that the best way then?
If it is not possible, what would be the fastest way to go?
Or is there another to look at this problem?
Many thanks for your thoughts ande suggestions.
F148ec0cad8ea241b1cfd80e7ec8a7df?d=identicon&s=25 Fredrik (Guest)
on 2007-01-16 22:41
(Received via mailing list)
One possibility would be to use CREATE TEMPORARY TABLE and create your
smaller recordset that way.

Another way would be to use sql to do the calculations if possible on
the fly using only the first query. (find_by_sql query)

Yet another way could be to pull all the data out with joins/relations
(assuming you have multiple tables here) and then loop through it with
code performing the calculations that way without going back to the
database since you hopefully can get all your data out with one query.

The third one would probably be my choice, but I don't know how the
data is stored.

Hopefully that could get you started.

Fredrik
Bce1d1b7c3ec7b577dcb42e254899e6b?d=identicon&s=25 Michael Schuerig (Guest)
on 2007-01-16 23:02
(Received via mailing list)
On Tuesday 16 January 2007 19:35, Rudy wrote:
> Is that possible? If so, how?
You may be able to pull in all the required objects through eagerly
loaded associations.

> Is that the best way then?

For more specific suggestions you need to put more specifics into your
question. It may even be sensible to offload everything you're trying
to do to a single, probably complicated, SQL statement.

Michael

--
Michael Schuerig
mailto:michael@schuerig.de
http://www.schuerig.de/michael/
0ab6a5abab167b409f58d280dc59a229?d=identicon&s=25 Faisal N Jawdat (Guest)
on 2007-01-16 23:23
(Received via mailing list)
On Jan 16, 2007, at 1:35 PM, Rudy wrote:
> Hi, looking for the best solution here. This is the situation: I
> have a large database (at least 100000 records). I have to find all
> the records from a group of people from a certain date, and after
> that I have to query and make calculations with a set of those
> records from every member of that group.

Are the latter calculations just an attempt to further filter the
results, or are you calculating new data?  If the former, you can
probably just use more complicated conditions to find the data.  If
the latter, you can do the calculation on the database using a more
complex find_by_sql statement, or in Rails by executing Ruby code on
the result set.  The latter will probably take longer if the system
is small, but be much faster when you have multiple clients hitting
the server to do that kind of operation.

-faisal
85ba58eed034056754d3760749897ab9?d=identicon&s=25 Rudy (Guest)
on 2007-01-16 23:31
(Received via mailing list)
Let me try to describe the situation a bit more clear: I have 20 users
who all have given points to each other. Each 'answer/point' is stored
in a record. (= 20 x 20 = 400 records) What I need is to calculate the
average of the points for every user, and list them. (Because I have
maybe 100 users in total, who are giving points on a lot of occasions,
the database will be be growing rapidly) What I'm looking for is the
most stable (speedwise) way to get the appropriate records and do the
calculations. I'd prefer to avoid a situation were results showing up
would slowly start to take more time as the database got bigger.
Thanks for any suggestion anyway.
4375b4aca04b8d72482fd409fb6d0b47?d=identicon&s=25 Roderick van Domburg (roderickvd)
on 2007-01-16 23:40
Rudy wrote:
> Let me try to describe the situation a bit more clear: I have 20 users
> who all have given points to each other. Each 'answer/point' is stored
> in a record. (= 20 x 20 = 400 records) What I need is to calculate the
> average of the points for every user, and list them. (Because I have
> maybe 100 users in total, who are giving points on a lot of occasions,
> the database will be be growing rapidly) What I'm looking for is the
> most stable (speedwise) way to get the appropriate records and do the
> calculations. I'd prefer to avoid a situation were results showing up
> would slowly start to take more time as the database got bigger.
> Thanks for any suggestion anyway.

If I interpret this correctly, the following should quite simply work:
Answers.find(:all, :select => 'user_id, AVG(points)', :group =>
'user_id')

- Roderick
4375b4aca04b8d72482fd409fb6d0b47?d=identicon&s=25 Roderick van Domburg (roderickvd)
on 2007-01-16 23:42
Roderick van Domburg wrote:
> If I interpret this correctly, the following should quite simply work:
> Answers.find(:all, :select => 'user_id, AVG(points)', :group =>
> 'user_id')

Obviously that should have been "Answer" instead of "Answers", excuse my
monkey hand-coding.

- Roderick
0ab6a5abab167b409f58d280dc59a229?d=identicon&s=25 Faisal N Jawdat (Guest)
on 2007-01-16 23:54
(Received via mailing list)
On Jan 16, 2007, at 4:27 PM, Rudy wrote:
> Let me try to describe the situation a bit more clear: I have 20 users
> who all have given points to each other. Each 'answer/point' is stored
> in a record. (= 20 x 20 = 400 records) What I need is to calculate the
> average of the points for every user, and list them.

you can presumably do this with a join, nested select, and some sort
of average (e.g. sum(points)/count(points), assuming that the points
are stored in floats).

that said, it's probably cleaner, easier, more portable, and faster*
to select all the records you want and then use ruby to calculate the
averages.  sql will probably do a faster job of calculating the data
for small data sets, but as the data grows and the number of clients
grows it's going to be progressively slower.  by putting it in ruby
you move the progressive slowness onto the part of the system that
can be easily replicated through the addition of cheap hardware.

grain of salt:  i haven't tested this.  i haven't even tried doing
it.  as with all estimations of performance, you should treat this as
made up until you've profiled it.

-faisal
0ab6a5abab167b409f58d280dc59a229?d=identicon&s=25 Faisal N Jawdat (Guest)
on 2007-01-17 00:17
(Received via mailing list)
On Jan 16, 2007, at 5:40 PM, Roderick van Domburg wrote:
> Answer.find(:all, :select => 'user_id, AVG(points)', :group =>
> 'user_id')

i take back my earlier claim that it would be easier to do this in
ruby.  also, if you do it this way in the db and your points are
integers postgresql (at least) will produce answers in floats, so
there appears to be no drawback to doing it this way, at least at first.

-faisal
E5e4661481399f05760ad37a5c6fc754?d=identicon&s=25 Kitto (Guest)
on 2007-01-17 11:59
(Received via mailing list)
I would the RDBMS do its job and do the calculation in an SQL
statement. Should be faster (if you do have indices) and reduces the
load between your sql machine and the webserver.
This topic is locked and can not be replied to.