Informix上的SQL语句性能问题

本文介绍了Informix上的SQL语句性能问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一条需要一段时间才能运行的Informix SQL语句.有没有人看到任何优化它的方法，这样就不会花很长时间了?

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 from TempTableA
WHERE NOT EXISTS(SELECT * FROM TempTableB
               WHERE TempTableB.Col1 = TempTableA.Col1
                 AND TempTableB.Col2 = TempTableA.Col2)
)

我的OriginalTable中有大约3亿行，TempTableA 93K行和TempTableB 58K行.

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
                    )

这篇关于Informix上的SQL语句性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！