Re: Large volume updates

> Subject: Large volume updates > Date: 13 Apr 1995 11:27:20 GMT > Reply-To: arktech@clark.net (Jonathan Crawford @ ArkTech) > Organization: Clark Internet Services, Inc., Ellicott City, MD USA > > Help! This question may be in the FAQ. (If there is a FAQ. It's not > at rtfm.mit.edu and it's not on comp.answers or news.answers. If it > exists, it has expired on my service provider, and I apologize > profusely!) FAQ is at location below. Kerry is working on getting it to the "usual" places. Name: kcbbs.gen.nz IP: 202.14.102.1 Location: New Zealand, GMT + 12 Access: Anonymous FTP Directory: /informix Contact: Kerry Sainsbury <kerry@kcbbs.gen.nz> Contents: Files related to the Informix FAQ listing (primary site). > I'm currently trying to do this using something like: > > update a set flag = 0; > update a set flag = 1 where key in > (select key from b where class = 'SPECIAL'); > update a set flag = 2 where key in > (select key from b where class <> 'SPECIAL'); > update b set flag = 0; > update b set flag = 1 where key in > (select key from a) and class = 'SPECIAL'; > update b set flag = 2 where key in > (select key from a) and class <> 'SPECIAL';> > Yes, at 1.5 million records each in a and b, this is grotesque. > I'm fried, jet lagged, exhausted, and feeling like I must be > missing something obvious. Does anyone have any suggestions on > how to solve this problem with reasonable performance? Just > the first update is taking 45 minutes. > > Any help would be greatly appreciated! > > -- Jonathan Crawford > Ark Technologies, Inc. So why is your task more interesting than what I *should* be doing? The following is 4GL-like pseudo-code, so don't take the syntax as canon: {DEFINE v_variables LIKE corresponding DB variables.} DECLARE a_found CURSOR FOR SELECT key FROM a ORDER BY key { assuming index on key, otherwise omit this line } FOR UPDATE OF flag FOREACH a_found INTO v_key SELECT class INTO v_class FROM b WHERE key = v_key IF STATUS = NOTFOUND THEN LET v_flag = 0 ELSE IF v_class = 'SPECIAL' THEN LET v_flag = 1 ELSE LET v_flag = 2 END IF END IF UPDATE a SET flag = v_flag END FOREACH You will need a similar loop for updating b based on a. If you do not have indexes on key for both the a and b tables, you should consider building them, if only for the update. Using straight SQL is the WORST way to do this, as you have found. You pass the data too many times. Since you are using 5.04, you may be able to do the above with a stored procedure. That would be the fastest, since everything would be done in the engine. Otherwise either a 4GL or ESQL/C program can do the job. The technique above only passes the a table once, rather than the three times needed by your SQL only method. Then you would pass the b table once in its loop. For each loop, the "other" table gets passed at least once, depending on whether you have an index on key. So each table gets passed twice or more. If you use 4GL or ESQL/C, you could write something that superficially resembles a merge sort. This would allow you to update both tables in the same loop, only passing each table once. I am not familiar enough with stored procedures to know whether this can be done in SPL. So the sort-like program would pass each table once, rather than the five or more times used by your straight SQL. Yep, that WAS interesting. Sounds like I need to get a life! Regards, Alan +---------------------------+-----------------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Lockheed Martin, SLS | Voice: 303-977-9998 | | P.O. Box 179, M/S 3810 | Standard disclaimers apply. Cutesy ones, too. | | Denver, CO 80201-0179 USA | Your mileage may vary. Void where prohibited. | +---------------------------+-----------------------------------------------+