Re: Slow Cursor
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
__________________________________________________