Re: Slow Cursor

Thank you soo much for all the input. There are really some GREAT ideas here. I can't wait to get back to work and try them out. It's nice to be in a newsgroup where you can post questions and get the help your looking for. Many of the other groups will cuss you out if you forget to give 100% of the info making it almost not worth asking at all. Again thanks to everyone. On Thu, 1 Oct 1998 15:08:21 -0500, rfurdzik@paulweiss.com wrote: > >I think you can do this all in one step (you not deleteing from second tabl= >e it=20 >is null)=2E > >Delete from FirstTable As A, OUTER SecondTable As B >Where A=2EPK1=3DB=2EPK2 >AND A=2EPK2=3DB=2EPK2 >AND B=2EPK2 IS Null> > > > > >Rafal Furdzik >10/01/98 02:51 PM >To: june_t@hotmail=2Ecom@smtp >cc: informix-list@iiug=2Eorg@smtp=20 >Subject: Re: Slow Cursor =20 > > > > > >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 >__________________________________________________ >