Path: utzoo!utgpu!news-server.csri.toronto.edu!bonnie.concordia.ca!uunet!zaphod.mps.ohio-state.edu!van-bc!twg!bill
From: bill@twg.bc.ca (Bill Irwin)
Newsgroups: comp.databases
Subject: Need to optimize this incredibly S L O W update
Message-ID: <600@twg.bc.ca>
Date: 3 Feb 91 06:35:49 GMT
Reply-To: bill@twg.bc.ca (Bill Irwin)
Organization: The Westrheim Group, Vancouver, B.C., Canada
Lines: 55


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.

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