Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!usc!samsung!uunet!zephyr.ens.tek.com!tektronix!sequent!lugnut
From: lugnut@sequent.UUCP (Don Bolton)
Newsgroups: comp.databases
Subject: Re: Need to optimize this incredibly S L O W update
Message-ID: <52252@sequent.UUCP>
Date: 4 Feb 91 18:50:24 GMT
References: <600@twg.bc.ca>
Reply-To: lugnut@sequent.UUCP (Don Bolton)
Organization: Sequent Computer Systems, Inc
Lines: 71

In article <600@twg.bc.ca> bill@twg.bc.ca (Bill Irwin) writes:
>
>I have an SQL that is supposed to select the clients that are due
>for Preventative Maintenance (PM) in the coming month and for
>each selected, add a new PM record to the pm table for each
>hardware item whose most recently completed pm record is 60 days
>old.  It actually works, but...
>
>The first time this SQL was run with the pm table empty, it
>selected 11 clients and about 200 items to add pm records for.
>It took about 5 minutes to run.  The 2nd time it was run it
>selected about 7 clients and still hasn't completed running as I
>write this article.  The elapsed time has been over 4 hours
>already, with the CPU time being 150 minutes - almost 3 hours!
>
>With this kind of dramatic increase between the first two runs of
>this SQL, I am visualizing a week to run the third one.  Below is
>the update SQL.  Any suggestions on ways to make it run faster
>would be greatly appreciated.  I am using Unify Turbo-SQL.
>

This sort of thing seems common to SQL based products in general. Had 
an istance with Oracle where I had 20,000 rows and had appx 7,000 that
needed updating with values from annother table (had a zip +4 done to
our contact lists by an external source).
                     an update where exists select blah blah blah chewed
and chewed and chewed. The solution was to create YAT (yet annother table)
as the "sum" of the selected criteria and do renames. went from 20 hours
to 20 minutes.

I've encountered a similar situation with Informix and opted for a similar
type solution.

>insert into pm (item_num):
>select item.item_number from item, client, inv
>        [the (inv)entory table stores item descriptions]
>        [the item table records individual components]
>where
>        client.key = item.key and       # item belongs to client
>        inv.key = item_id and           # item key matches inv key
>        inv_group = 'H' and             # hardware items only
>        quit_date = **/**/** and        # client hasn't terminated support
>        item.inst_date > **/**/** and   # item has been installed
>        [
>        pm_month_1 = 2 or               # one of the 3 PM months for the
>        pm_month_2 = 2 or               # client is February
>        pm_month_3 = 2
>        ]
>        and [
>           item.item_number =           # dont't add a new pm record if
>           select pm.item_num from pm   # the most recent one is within
>           where pm.item_num = item.item_number  # 2 months
>           group by pm.item_num having 02/02/91 - max(pm.pm_date) > 60
>           ;
>        or 0 =                                  # handles the case where the
>           select count(*) from pm              # item is new and has no PMs
>           where pm.item_num = item.item_number # on file
>           ;
>        ]
>/
>
>I know it is the two innner selects that are slowing it down, but
>I need to be selective about whether to add a new PM record for
>an item.  Thanks for any suggestions.
>-- 
>Bill Irwin    -       The Westrheim Group     -    Vancouver, BC, Canada
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>uunet!van-bc!twg!bill     (604) 431-9600 (voice) |     Your Computer  
>bill@twg.bc.ca            (604) 430-4329 (fax)   |    Systems Partner

Good luck