Re: Slow Cursor

In article <3612EA8C.805033A7@hotmail.com>, June Tong <june_t@hotmail.com> writes >justabill wrote: > >> delete from FirstTable >> where>> FirstTable.PK1 || FirstTable.PK2 not in >> (select secondtable.PK1 || secondtable.PK2) >> >> 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. > >Hmmm. Nothing faster comes to mind. Depending on what proportion of the >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. This also depends on how many other >constraints/indexes/other things you had that would need to be re-created. >Your method is probably the most straight-forward. > > >> 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. I changed it so both tables where both decimals. > >Well this was really your problem. I didn't know that "It's well known that >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) but >it is (sort of) well known that doing comparisons between strings and >numbers is slow. Read all about it in my FAQ: >http://www.geocities.com/SiliconValley/Bridge/4578/faq.html (I know, 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). > No problem, I'll add them... >June >-- >june_t@hotmail.com >Lost in the wilds of Palo Alto, living on Peanut M&M's > > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care