RE: SQL advice please

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/ ------------------------------------------------------------