Path: utzoo!censor!geac!torsqnt!news-server.csri.toronto.edu!cs.utexas.edu!usc!ucsd!ucbvax!bloom-beacon!eru!hagbard!sunic!dkuug!freja.diku.dk!rimfaxe.diku.dk!nomann
From: nomann@rimfaxe.diku.dk (Ole Nomann Thomsen)
Newsgroups: comp.databases
Subject: Effecient merging of tables in SQL ? (informix V2.10)
Keywords: sql, informix, merge
Message-ID: <1990Nov7.115344.27844@diku.dk>
Date: 7 Nov 90 11:53:44 GMT
Sender: news@diku.dk (The Netnews System)
Distribution: comp
Organization: Department Of Computer Science, University Of Copenhagen
Lines: 63

Hello all. I am doing some programming in informix-sql V2.10, and have run 
into a problem that I can solve (:-)) , but only inefficiently (:-().

So, if you have the time and energy, please lend me a hand.

This is the problem:
====================

I have build a temporary table T1, containing the columns "no" and "lnum" (both
integers).

The main table contains (among other things) the columns "s_no" (serial) and
"l_num" (integer).

I need to update the main table, so that the "l_num" is set to the "lnum" from
T1 when "no" and "s_no" matches. T1 contains unique "no" to match some of the
main table's "s_no" (but not all).

Now this statement (repeated from jelly-ware memory) does it:

update main
  set l_num = (
    select lnum from T1                             {1}
    where T1.no = main.s_no                         {1}
  )
where exists                                        {2}
    select no from T1                               {2}
    where T1.no = main.s_no;                        {2}

But it's cruciatingly slow:

The {1} part of the statement seems to involve searching the entire T1 table,
(approx. 500 entries), for each row in main (approx 800).

The {2} part seemingly searches T1 *again* but if I leave it out, all the
main.l_num with no main.s_no matched by T1.no get updated with NULL.
This is catastrophic.

A naive estimate (mine :-}) of the involved amount of search follows:

{1} for each 800 in main, search the 500 in T1 :  40000
{2} well ... um, the same again                :  40000
                                       result  :  80000 tests.

It certainly seems to take long enough for this, but can it really be the
only way to do it ? what if the main table had 10000 rows and T1 8000 ? 
(shudder!). Both T1 and main's no/ s_no are indexed, which does help, but
not nearly enough.

Now, what I'd like to see, was a stmt. like this:

MERGE (main ordered by s_no), (T1 ordered by no)
  if main.s_no = T1.no then
    set main.l_num = T1.lnum;

But that's whishfull thinking. Still, can any of you tell me of an efficient
way to do this ? I can't seem to find any help in my manual.

Please reply by E-mail. Thanks in advance.

- Ole. (nomann@diku.dk).

"Information is not knowledge" - Frank Zappa.