Sql query is slow(exists/joins)

Question

I've got a SQL query to update records. I use 'EXISTS' function but it's very slow. Now I want to change my query with joins.

UPDATE zp
SET ZP.TEST1=NULL,
    ZP.TEST2=NULL,
    ZP.TEST3=NULL,
    ZP.TEST4=NULL,
    ZP.TEST5=NULL,
    ZP.TEST6=NULL,
    ZP.TEST7=NULL,
    ZP.TEST8=NULL,
    ZP.TEST9=NULL,
    ZP.TEST10=NULL,
    ZP.TEST11=NULL,
    ZP.TEST12=NULL,
    ZP.TEST13=NULL,
    ZP.TEST14=NULL,
    ZP.TEST15=NULL
from TestTable ZP
WHERE NOT(
        (ZP.name='I' 
    AND
         surname='S'
    OR 
         addr='S' 
    AND 
        ClientID IS NOT NULL)
    AND EXISTS(
        SELECT * FROM table2 P
        WHERE P.OrgID=ZP.OrgID AND
        P.CATEGORY='D'
    )
)

http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql — StanislavL, Nov 10 '15 at 07:21
Improved code formatting, and grammar, also added inline code for better readability — dingo_d, Nov 10 '15 at 07:25
Exists really doesn't look like a problem here. Do you have indexes on the columns you are joining to? — jazza1000, Nov 10 '15 at 07:34
Which DBMS are you using? Postgres? Oracle? You also need to post the execution plan for the query and the definition of all indexes defined on the tables. **Edit** your question for that (and post the plan as _formatted_ text, not as a screenshot) — , Nov 10 '15 at 08:13
You might want to check criteria: `(ZP.name='I' AND surname='S' OR addr='S' AND ClientID IS NOT NULL)` because it might retrieve more rows than you expect. You probably need to put OR expression into parenthesis. See [Logical Operator Precedence](https://technet.microsoft.com/en-us/library/ms186992%28v=sql.105%29.aspx). — Nikola Markovinović, Nov 10 '15 at 10:20

score 0 · Answer 1 · answered Nov 12 '15 at 08:58

0

A slight efficiency improvement would be....

EXISTS(
        SELECT 1 FROM table2 P
        WHERE P.OrgID=ZP.OrgID AND
        P.CATEGORY='D'

Namely 1 rather than *.

But I'm sure that's not the complete solution.

answered Nov 12 '15 at 08:58

AntDC

1,807
14
23

Sql query is slow(exists/joins)

1 Answers1