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 f

Solution 1:

Update OriginalTable 
Set flag ='D'Where Type ='S'And Flag <>'S'AndNotExists  (
                    Select1From OriginalTable As T1
                    Where T1.Type ='Z'And T1.flag <>'S'And T1.Col1 = OriginalTable.Col1
                        And T1.Col2 = OriginalTable.Col2
                    )

Solution 2:

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' ) ExcludeTheseON OT1.Col1 = ExcludeThese.Col1
          AND OT1.Col2 = ExcludeThese.Col2
    WHERE  OT1.type = 'S' AND OT1.flag <> 'S'AND ExcludeThese.Col1 IS NULL
    ORDERBY
       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  );

Solution 3:

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

SELECT col1, col2, 
CASEWHEN type ='S'THEN1ELSEWHEN type ='Z'THEN2ENDAS 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
LEFTJOIN TempTable t2 ON (t1.col1 = t2.col2 AND t1.col2 = t2.col2)
WHERE t1.filteredType =1AND t2.filteredType =2AND t2.Serialnumber ISNULL
)

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.