SQL Statement Performance Issue on Informix

I have this Informix SQL statement which takes ages to run. Does anybody see any way to optimize it so it wouldn't take so long?

SELECT * FROM OriginalTable WHERE type = 'S' AND flag <> 'S' INTO TEMP TempTableA;

SELECT * FROM OriginalTable WHERE type = 'Z' AND flag <> 'S' INTO TEMP TempTableB;

UPDATE OriginalTable SET flag = 'D' WHERE Serialnumber in
(
select Serialnumber开发者_Python百科 from TempTableA
WHERE NOT EXISTS(SELECT * FROM TempTableB
               WHERE TempTableB.Col1 = TempTableA.Col1
                 AND TempTableB.Col2 = TempTableA.Col2)
) 

I have in my OriginalTable around 300 million rows, TempTableA 93K rows, and TempTableB 58K rows.

Update OriginalTable 
Set flag = 'D' 
Where Type = 'S'
    And Flag <> 'S'
    And Not Exists  (
                    Select 1
                    From OriginalTable As T1
                    Where T1.Type = 'Z'
                        And T1.flag <> 'S'
                        And T1.Col1 = OriginalTable.Col1
                        And T1.Col2 = OriginalTable.Col2
                    )

In a similar approach as @tombom stated. Pre-query only the columns you care about to keep the temp table smaller. If you are dealing with a table of 60 columns, you are filling a whole lot more than just 3-4 columns where your primary consideration are valid serial numbers. Pre-test the query to make sure it gives you the correct set you are expecting, then apply that to your SQL-update.

So here, the inner query are the ones you DO NOT WANT... Since you were comparing against only column 1 and column 2 from this table, that's all I'm pre-querying. I'm then doing a LEFT JOIN to this inner result set on COL1 and COL2. I know, you want to EXCLUDE THOSE FOUND IN THIS result set... That's why, in the OUTER WHERE clause, I've added "AND ExcludeThese.Col1 IS NULL". So, any instances from OT1 that never existed in the subquery are good to go (via left join), and those that WERE FOUND, WILL have a match on col1 and col2, but THOSE will be excluded via the "and" clause I've described.

SELECT OT1.SerialNumber
    FROM OriginalTable OT1
        LEFT JOIN ( select OT2.Col1,
                           OT2.Col2
                       FROM OriginalTable OT2
                       where OT2.type = 'Z' 
                         AND OT2.flag <> 'S' ) ExcludeThese
           ON OT1.Col1 = ExcludeThese.Col1
          AND OT1.Col2 = ExcludeThese.Col2
    WHERE  OT1.type = 'S' 
       AND OT1.flag <> 'S'
       AND ExcludeThese.Col1 IS NULL
    ORDER BY
       OT1.SerialNumber
    INTO 
       TEMP TempTableA;  

Again, test this query by itself to make sure you ARE getting the records you expect. To help clarify the records returned, change the above select to include more columns for a mental / sanity check, such as

SELECT OT1.SerialNumber,
       OT1.Col1,
       OT1.Col2,
       ExcludeThese.Col1 JoinedCol1,
       ExcludeThese.Col2 JoinedCol2
  from  <keep rest of query intact>

Now, you'll be able to see the serial number and instances of those columns that would or not be joined to the "excludeThese" resultset... Try again, but remove only the "AND ExcludeThese.Col1 IS NULL" clause, and you'll see the other lines and WHY they are being excluded -- that is if you DID have any questions to the content.

Once you are satisfied with the pre-query... which will only return the single column of SerialNumber, that can be index/optimized since you are pulling into a temp table, build an index, then apply your update.

UPDATE OriginalTable 
   SET flag = 'D' 
   WHERE Serialnumber in ( select Serialnumber from TempTableA  );

I was too lazy to test with test data, but maybe this can do?

SELECT col1, col2, 
CASE WHEN type = 'S' THEN 1 
ELSE WHEN type = 'Z' THEN 2 END AS filteredType 
FROM OriginalTable WHERE (type = 'S' OR type = 'Z') AND flag <> 'S' INTO TempTable;

UPDATE OriginalTable SET flag = 'D' WHERE Serialnumber IN
(
SELECT t1.Serialnumber FROM TempTable t1
LEFT JOIN TempTable t2 ON (t1.col1 = t2.col2 AND t1.col2 = t2.col2)
WHERE t1.filteredType = 1
AND t2.filteredType = 2 
AND t2.Serialnumber IS NULL
)

That way you can omit one loading into temp table. On the other hand there will be no index on the new column filteredType.

Also I have no idea of informix. Hope it helps anyway.