Help needed tuning SQL statement.

We have a SQL statement a co-worker wrote that is giving us some trouble. We've tried several variations but none seem to do what we want as quickly as we feel it should. The statement takes a very long time on small tables of less than 10,000 rows. Sometimes it creates too many extents errors too.

Can some SQL expert reading this tune the statement so that it does what we want quickly? The enclosed example is what we started with, and to our thinking best portrays what we seek to accomplish. Neither of us has had any SQL training yet, so any help or pointers will be greatly appreciated.

We first create table_1 with the storage parameters we want. Then to populate the table we are trying the following:

  insert into table_1
    (column_A, column_B, column_C)
    select table_2.column_A, table_2.column_B, substr(table_3.column_C,1,5)     from table_2, table_3, table_1
    where table_2.column_D = '999999' and

          table_2.column_B != table_1.column_B;

Notes:

There are seven columns in table_1 when it is created, but we are only

   placing data in three of the columns. The other columns will be updated    using a form later.

column_B which is in both table_1 and table_2 is an indexed char column.

column_D which is in table_2 is not an indexed column.

table_1 should have approximately 7,000 rows and has no indexed columns.

What we are attempting to do is if table_2.column_D has 999999 in the row it is an active row any thing else in that column and it is inactive and shouldn't be entered in table_1. If a record is active it should be added to table_1 if table_2.column_B is not the same as table_1.column_B.

I hope I haven't confused you too much. We sure have been. :)

Please post or e-mail any respones. E-mailed responses should be sent to proberts_at_iglou.com or proberts_at_ky-ngnet.army.mil Received on Sat Nov 26 1994 - 01:00:06 CET