In my app, I want to create a new record if it doesn''t exist, and
update
some fields if it does.
Looking at the debug log, and it appears that I''m doing something
that''s
causing an extra SELECT. The basic synopsis:
sta = Station.find_by_station_id(station_id)
if sta.nil?
Station.create(params)
else
sta.p1 = "white port"
sta.p2 = "lemon juice"
sta.save!
end
The Station.create() isn''t a problem. But if it takes the
''else''
clause, the SQL trace looks suspicious. (Hand edited for clarity...):
============Station Load: SELECT * FROM `stations` WHERE
(`stations`.`station_id` ''WPLJ'') LIMIT 1
SQL BEGIN
Station Load: SELECT `stations`.id FROM `stations` WHERE
(`stations`.`station_id` = BINARY ''WPLJ'' AND `stations`.id
<> 12095)
LIMIT 1
Station Update: UPDATE `stations` SET `p1` = "white port", `p2` =
"lemon
juice" WHERE `id` = 12095
COMMIT
============It seems like just the UPDATE would suffice, so what is that second
SELECT doing? And what can I do to make it go away? :)
TIA.
- ff
--
Posted via http://www.ruby-forum.com/.
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
Philip Hallstrom
2010-Apr-14 22:37 UTC
Re: why am I getting an extra SELECT before save! ?
On Apr 14, 2010, at 3:30 PM, Fearless Fool wrote:> In my app, I want to create a new record if it doesn''t exist, and > update > some fields if it does. > > Looking at the debug log, and it appears that I''m doing something > that''s > causing an extra SELECT. The basic synopsis: > > sta = Station.find_by_station_id(station_id) > if sta.nil? > Station.create(params) > else > sta.p1 = "white port" > sta.p2 = "lemon juice" > sta.save! > end > > The Station.create() isn''t a problem. But if it takes the ''else'' > clause, the SQL trace looks suspicious. (Hand edited for clarity...): > ============> Station Load: SELECT * FROM `stations` WHERE > (`stations`.`station_id` > ''WPLJ'') LIMIT 1 > > SQL BEGIN > > Station Load: SELECT `stations`.id FROM `stations` WHERE > (`stations`.`station_id` = BINARY ''WPLJ'' AND `stations`.id <> 12095) > LIMIT 1 >Any chance you have a validates_uniqueness_of :station_id in your model? If so, the above select is checking for that.> Station Update: UPDATE `stations` SET `p1` = "white port", `p2` = > "lemon > juice" WHERE `id` = 12095 > > COMMIT > ============> It seems like just the UPDATE would suffice, so what is that second > SELECT doing? And what can I do to make it go away? :) > > TIA. > > - ff > -- > Posted via http://www.ruby-forum.com/. > > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org > . > For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en > . >-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Philip Hallstrom wrote:> Any chance you have a validates_uniqueness_of :station_id in your > model? If so, the above select is checking for that.Give that man a cigar! Yep, that''s exactly what''s going on -- thanks! I believe it''s safe to remove the uniqueness constraint since my code is checking for existence of a station before creating a new one anyway. If anyone wants to warn me of any gotcha''s about doing this, now would be a good time to tell me. <grin> - ff -- Posted via http://www.ruby-forum.com/. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Philip Hallstrom
2010-Apr-14 23:00 UTC
Re: Re: why am I getting an extra SELECT before save! ?
On Apr 14, 2010, at 3:54 PM, Fearless Fool wrote:> Philip Hallstrom wrote: >> Any chance you have a validates_uniqueness_of :station_id in your >> model? If so, the above select is checking for that. > > Give that man a cigar! Yep, that''s exactly what''s going on -- thanks! > > I believe it''s safe to remove the uniqueness constraint since my > code is > checking for existence of a station before creating a new one anyway. > If anyone wants to warn me of any gotcha''s about doing this, now would > be a good time to tell me. <grin>Are you going to check for that condition *every* time you create that model? In scripts, via the console, somewhere else in the code? If you remove that constraint you''ll need to remember this every time... and at some point you''ll forget. I always do :/ I''d leave it alone if it were me. -p -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Fearless Fool
2010-Apr-14 23:06 UTC
Re: Re: why am I getting an extra SELECT before save! ?
Philip Hallstrom wrote:> Are you going to check for that condition *every* time you create that > model? In scripts, via the console, somewhere else in the code? > > If you remove that constraint you''ll need to remember this every > time... and at some point you''ll forget. I always do :/ > > I''d leave it alone if it were me. > > -pPoint well taken. Right now, that''s the only code that modifies the Station table, and I''m doing a massive (initial) load and update -- removing the constraint really does speed things up. But heeding your advice, I''ll reinstate the constraint after this initial load finishes. (What? Of COURSE I''ll remember! :) Thanks. -ff -- Posted via http://www.ruby-forum.com/. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Philip Hallstrom
2010-Apr-14 23:32 UTC
Re: Re: Re: why am I getting an extra SELECT before save! ?
On Apr 14, 2010, at 4:06 PM, Fearless Fool wrote:> Philip Hallstrom wrote: >> Are you going to check for that condition *every* time you create >> that >> model? In scripts, via the console, somewhere else in the code? >> >> If you remove that constraint you''ll need to remember this every >> time... and at some point you''ll forget. I always do :/ >> >> I''d leave it alone if it were me. >> >> -p > > Point well taken. Right now, that''s the only code that modifies the > Station table, and I''m doing a massive (initial) load and update -- > removing the constraint really does speed things up. But heeding your > advice, I''ll reinstate the constraint after this initial load > finishes. > (What? Of COURSE I''ll remember! :):) You could also do my_model_instance.save(false) which would bypass the validations entirely. As long as you''re sure your initial data load is legit. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.