Forum: Ruby on Rails Need help cutting down on db traffic

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.
Isak H. (Guest)
on 2006-04-20 16:06
(Received via mailing list)
I'm having performance issues with one of my actions, and think I've
narrowed it down to too many sql queries.

I have a form with ~60 lines, which takes ~6 sec to save, 60%+ of that
is db io. There's 650+ lines of log data, most of which are
AR/queries.

The app is a simple dual entry book keeping thingy, and the form in
question contains a journal entry along with a partial for every
transaction line; i.e. debet/credit account X by Y.


My update method goes something like this:

-Manually start a transaction. Don't want any single writes to go
through if things bork up.

-Try to load the entry. Make sure it belongs to the logged in client
and doesn't belong to a closed period, then update it with
update_attributes.
-Or create a new entry if it couldn't be found.

-Then iterate through param[:line].keys, i.e. line id's. If the id is
found in a session variable, bulk update the line, otherwise create a
new one from params[:line][id] and add it to the entry.

-save! the entry


I believe a lot of the queries are related to validation. I have 3-4
validates_associated/validates_presence_of statements in the line
class, and these relations seem to get loaded whenever i do an update.

I've tried commenting out the validations. Things are faster, but the
relations still seem to get loaded on update? I define foreign key
constraints in the db, so can probably drop these, or? This still
leaves me at 1.5-2 sec of db traffic, though..

How about custom validations? How should i write something like "def
validate; self.errors.add(:entry, "period is closed!") unless
self.entry.period.open?; end"?

I am a tad frustrated with AR after seeing all the statements
generated, but the user is probably to blame for a lot of it.

It also seems like the same row gets loaded again every time it's
used? Perhaps AR could benefit from a per-request cache, or would that
do more harm than good? Say every result set with less than 5 rows
gets cached until the request is finished?

Any input/suggestions appreciated,
Isak
Bryan D. (Guest)
on 2006-04-20 16:40
One question I would have is, are you running this app on webrick on
your desktop? It's always going to be slower in your development
environment than in deployment.

How many lines are you saving at once? Is there any reason why you can't
save the lines individually? Maybe I don't understand the nature of the
application well enough to comment here.
Isak H. (Guest)
on 2006-04-20 19:14
(Received via mailing list)
On 4/20/06, Bryan D. <removed_email_address@domain.invalid> wrote:
>
> One question I would have is, are you running this app on webrick on
> your desktop? It's always going to be slower in your development
> environment than in deployment.


I am running in development mode, yes. Then again, I'm just a single
user
and my dev box is reasonably beefy.
I'd think several hundred queries for a single request was 'bad' though,
no
matter how fast they could be served.

How many lines are you saving at once? Is there any reason why you can't
> save the lines individually? Maybe I don't understand the nature of the
> application well enough to comment here.


The users want a form where all lines are editable.

Now that I think about it, I figure they don't all need to be updated on
submit. It means more LOC though, and saving 60 rows shouldn't be _that_
expensive?


-Isak
Tom M. (Guest)
on 2006-04-20 20:43
(Received via mailing list)
On Apr 20, 2006, at 8:14 AM, Isak H. wrote:

> Now that I think about it, I figure they don't all need to be
> updated on submit.
> It means more LOC though, and saving 60 rows shouldn't be _that_
> expensive?

I think you'll benefit in the initial loading if you use
eager loading via :include or the new method in 1.1.x
which I remember exists but cannot remember the details
of.

That said, the solution above is probably your real
answer.

P.S. You're not updating 60 rows, you're doing a lot
      more. With some small changes, those references
      to identical rows can almost certainly be
      eliminated.

--
-- Tom M.
This topic is locked and can not be replied to.