RE: SQL advice please

Thanks to all the suggestions, and especially to Will for quantifying the solution. You guys are great! >From: William Rice <ricew@operamail.com> >To: informix-list <informix-list@iiug.org>, Steve Barnes ><sgbarnes99@hotmail.com> >Subject: RE: SQL advice please >Date: Wed, 1 Mar 2000 14:19:01 -0500 > >Just some metrics I got from trying the different methods. >I noted the number of rows in each table and the time. >I had to cut it down to get a reasonable time sometimes :) >On all tests exclusive locks were taken on all tables involved. >All testing done on 7.30UC2 > >Test 1: > DELETE FROM table_a WHERE trim(col_a)||trim(col_b) IN > (SELECT trim(col_a)||trim(col_b) from table_b) > table_a table_b time: > 350000 6 killed after 900 seconds > 350000 101 killed after 800 seconds > 20000 100 55 seconds> >Test 2: > DELETE FROM table_a WHERE > EXISTS(select 1 from table_b where table_a.col_a=table_b.col_a > AND table_a.col_b=table_b.col_b) > table_a table_b time: > 350000 6 2 seconds > 350000 101 6 seconds > 350000 20000 32 seconds> > > >===== Original Message From Rudy Fernandes <rferdy@americasm01.nt.com> >===== > >Steve Barnes wrote: > > > >> This is to be achieved using SQL only. > >> > >> Table_a contains about 350,000 rows, table_b probably about 20,000. > >> > >> Thanks to all the suggestions so far. I know the original method is far >from > >> perfect, but does actually work as col_a is char containing only >numbers, > >> and col_b is a char containing only characters. Please don't ask me why >it > >> is this way, it is an inherited layout! > >> > > > >As Doug had suggested earlier, this requirement begs for the use of the >EXISTS > >clause > > > >DELETE FROM table_a > >WHERE EXISTS ( > > SELECT 1 FROM table_b > > WHERE table_a.col_a = table_b.col_a > > AND table_a.col_b = table_b.col_b);> > > >Avoid using the TRIM function in the WHERE clause unless it is a business > >requirement. > > > >An index on table_b (col_a, col_b) would help, unless the TRIM function >is > >used. If reasonably unique indexes on table_b.col_a or table_b.col_b >already > >exist, the col_a, col_b would not be essential. > > > >Rudy > >------------------------------------------------------------ >This e-mail has been sent to you courtesy of OperaMail, as a free >service from >Opera Software, makers of the award-winning Web Browser, Opera. Visit us >at >http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free >e-mail >account is waiting at: http://www.operamail.com/ >------------------------------------------------------------ > ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com