# Need help cutting down on db traffic **URL:** \ **Category:** Rails\ **Created:** [April 20, 2006, 2:06pm UTC](https://www.ruby-forum.com/t/need-help-cutting-down-on-db-traffic/58307 "2006-04-20T14:06:21Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Isak\_H](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/77aa72/32.png) [@Isak\_H](https://www.ruby-forum.com/u/Isak_H)\ **Post date:** [April 20, 2006, 2:06pm UTC](https://www.ruby-forum.com/t/need-help-cutting-down-on-db-traffic/58307/1 "2006-04-20T14:06:21Z")
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 ---
**Author:** ![Isak\_H](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/77aa72/32.png) [@Isak\_H](https://www.ruby-forum.com/u/Isak_H)\ **Post date:** [April 20, 2006, 2:40pm UTC](https://www.ruby-forum.com/t/need-help-cutting-down-on-db-traffic/58307/2 "2006-04-20T14:40:27Z")
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. ---
**Author:** ![Isak\_H](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/77aa72/32.png) [@Isak\_H](https://www.ruby-forum.com/u/Isak_H)\ **Post date:** [April 20, 2006, 5:14pm UTC](https://www.ruby-forum.com/t/need-help-cutting-down-on-db-traffic/58307/3 "2006-04-20T17:14:42Z")
On 4/20/06, Bryan D. [removed\_email\_address@domain.invalid](mailto: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 ---
**Author:** ![Isak\_H](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/77aa72/32.png) [@Isak\_H](https://www.ruby-forum.com/u/Isak_H)\ **Post date:** [April 20, 2006, 6:43pm UTC](https://www.ruby-forum.com/t/need-help-cutting-down-on-db-traffic/58307/4 "2006-04-20T18:43:50Z")
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.