Re: Slow Cursor
I have found cases where it was much faster to build a temp table with an
index and then use a NOT EXISTS (SELECT xxx FROM t_Table WHERE yyy = zzz).
Otherwise I have sometimes seen IDS to a sequentail scan in orer to satisfy
the NOT IN() clause.
S.W.
wilcoxon@pioneerplanet.infi.net
rfurdzik@paulweiss.com wrote in message <6v0pvt$dh$1@news.xmission.com>...
>
>June Tong <june_t@hotmail=2Ecom> on 09/30/98 10:35:56 PM
>Please respond to June Tong <june_t@hotmail=2Ecom>
>To: informix-list@iiug=2Eorg
>cc: (bcc: Rafal Furdzik/PaulWeiss)
>Subject: Re: Slow Cursor
>
>If you can convert this select to delete that should be much faster,
subque=
>ries=20
>are usually very slow, joining tables is MUUUCH faster (that is from my=20
>experience)=2E Try This:
>
>1) Get unmatching rows to a new table
>select A=2EPK1, B=2EPK2
>from FirstTable As A, OUTER SecondTable As B
>Into temp T1
>Where A=2EPK1=3DB=2EPK2
>AND A=2EPK2=3DB=2EPK2
>AND B=2EPK2 IS Null
>
>2) Delete Unmatching rows from a first table
> Delete from FirstTable As A, T1 As B
>Where A=2EPK1=3DB=2EPK2
>AND A=2EPK2=3DB=2EPK2>
>It Should work, let me know I would like to know=2E
>=20
>
>> delete from FirstTable
>> where>> FirstTable=2EPK1 || FirstTable=2EPK2 not in
>> (select secondtable=2EPK1 || secondtable=2EPK2)
>
>
>Note: Some recipients have been dropped due to syntax errors=2E
>Please refer to the "$AdditionalHeaders" item for the complete headers=2E
>
>
>justabill wrote:
>
>> delete from FirstTable
>> where>> FirstTable=2EPK1 || FirstTable=2EPK2 not in
>> (select secondtable=2EPK1 || secondtable=2EPK2)
>>
>> This is a lot faster, I'm not convinced that it is the fastest way of
>> doing it which is what I usually strive for=2E
>
>Hmmm=2E Nothing faster comes to mind=2E Depending on what proportion of
t=
>he
>rows were going to be deleted, it *might* be faster to simply copy into a
>new table the rows you were NOT going to delete, drop the table, and rename
>the new table=2E This also depends on how many other
>constraints/indexes/other things you had that would need to be
re-created=2E
>Your method is probably the most straight-forward=2E
>
>
>> One other problem I found and corrected is that in the first table the
>> primary key values where decimals and in the other table they where
>> strings=2E I changed it so both tables where both decimals=2E
>
>Well this was really your problem=2E I didn't know that "It's well known
t=
>hat
>delete is much slower than insert and update" (actually, I would sort of
>expect UPDATE to be the slowest, but it depends on what you're updating)
bu=
>t
>it is (sort of) well known that doing comparisons between strings and
>numbers is slow=2E Read all about it in my FAQ:
>http://www=2Egeocities=2Ecom/SiliconValley/Bridge/4578/faq=2Ehtml (I
know,=20=
>I know,
>I'm on David's hit list now -- but David, I *swear* as soon as I see all
>these questions in the official FAQ, I'll take them out of mine)=2E
>
>June
>--
>june_t@hotmail=2Ecom
>Lost in the wilds of Palo Alto, living on Peanut M&M's
>
>
>
>
>
>
>
>
>
>__________________________________________________
>
>This message is intended only for the use of the=20
>Addressee and may contain information that is=20
>PRIVILEGED and CONFIDENTIAL=2E If you are not the
>intended recipient, you are hereby notified that
>any dissemination of this communication is
>strictly prohibited=2E If you have received this
>communication in error, please erase all copies of
>the message and its attachments and notify us
>immediately=2E Thank You=2E
>__________________________________________________
>