Re: [SOLVED for good] performance tuning questions: replace IN (values) by JOIN

"tojo" <Tojo_at_hotmail.com> wrote in message news:MPG.19bd3b5393457b6e9896a2_at_news.t-online.de...
> In article <3f5315cd$0$10359$afc38c87_at_news.optusnet.com.au>,
> howardjr2000_at_yahoo.com.au says...
> >
> > Quite the opposite. Foreign keys nearly always *ought* to have indexes,
but
> > never do unless one makes it so. Terrible locking issues arise when
> > non-indexed foreign keys are in place, and someone decides to update the
> > parent table... Ugh!
> >
> Howard,
>
> isn't there only a locking issue if you update the primary key of the
> parent table? Updating other fields should be OK. Please correct me if
> I'm wrong.
>
> -- Tom

No, you're correct. It's parent primary key updates that are the issue. But there are an awful lot of apps out there that take the easy way out: when you update column 3, the code issues says 'set column 1 to window 1, column 2 to window 2, column 3 to window 3...' and so on. Net result, you think you've updated something which isn't the primary key, but in fact you have. And the locking issues flow on from there.

But yes, I should have said 'update the parent table's primary key'.

Regards
HJR Received on Mon Sep 01 2003 - 05:40:06 CDT