Re: Slow Cursor

On Tue, 29 Sep 1998 02:31:21 GMT, justabill@school.house.rocks (justabill) wrote: >First of all I can't thank you enough for your replies. I really >hadn't thought about having too many indexes on the table. I will >definately keep that in mind. I worked on this problem today and this >is what I came up with. If you have a second to give me some feedback >I would greatly appreciate it. > >I dropped the whole cursor idea all together and did it with the >following sql > >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. When you do a "not in" using a subquery I believe the engine will execute that subquery for every row you delete. With the above syntax I am also afraid that it will not be able to use an index, not even the primary key index on PK1,PK2. You might check this by "set explain on" and check the sqexplain.out file. My original suggestion modified to something similar would be to do the following: select FirstTable.PK1 || FirstTable.PK2 as ftpk, SecondTable.PK1 as stpk1 from FirstTable, outer SecondTable where FirstTable.PK1 = SecondTable.PK1 and FirstTable.PK2 = SecondTable.PK2 into temp tt_1; delete from FirstTable where FirstTable.PK1 || FirstTable.PK2 in (select ftpk from tt_1 where stpk1 is null); The above should work, but you will have to test it. The idea is that the "in (select...)" is much faster than a "not in" and the difference is large enough in many cases that even with the initial building of a temporary table like this the combination of the two statements will still be faster than your single statement. Do test it though. May be it isn't in your case. I dislike it profoundly though. Concatenating things to create a new key field on the fly may not allways work exactly as expected and it may not be particularly fast. The datatype of the ftpk field in tt_1 is one problem. It does become a char type, but I don't know the length. A small test shows it seems to become long enough though, but it may make the temporary table rather large. Also see below for significant problems with non char type fields. Modified with Art's suggestion of using rowid the above would become: select FirstTable.rowid as ftrowid, SecondTable.rowid as strowid from FirstTable, outer SecondTable where FirstTable.PK1 = SecondTable.PK1 and FirstTable.PK2 = SecondTable.PK2 into temp tt_1; delete from FirstTable where rowid in (select ftrowid from tt_1 where strowid is null); This might be faster and works as is if your tables are not fragmented. Appart from my equally profound dislike of rowid it looks better than the above stuff. If your table is fragmented you might add rowid's to it. I would ad a regular serial column to it instead as that's what such a rowid realy is. Why not get it out into the open. >One pretty important thing I forgot to mention, and I apologize in >advance, is that our database has logging turned off. In which case large deletes is no problem. >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, if both are decimals the use of || to concatenate them will convert them into char. I am not so sure how well that works. Both the speed and some other issues are a consern. You will have to look at what the engine does. On my engine the decimals are concatenated with no spaces between them. This should work ok assuming there is a decimal point. If it where integers or decimals with no component after the decimal point the concatenation would *not* work. 1001 || 100 and 100 || 1100 would both become 1001100. You would have to use rowid or an extra serial column or some other solution instead. These problems are one of the reasons why I would never dream of using concatenation unless I could find no other solution at all. As to my statement that "It's well known that delete is much slower than insert and update." that did apply to the SE engine in the old time when indexes where involved. With no indexes I believe a delete even at that time was fast and still is in any engine. I still believe that deletes with indexes are slower than it's to insert or update things, but as June Tong didn't know about it this would have to be tested again. Even things I claim are "well known" may not be so well known or even correct after all :-( May be I will find time to test it one day, but input from others on this would be fine. Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)