CODEWITHSUNDEEP
sql-serversubqueryquery-optimization
kyle_13
kyle_13

Reputation: 1233

SQL query optimization for subqueries

I am trying to speed up a stored procedure, and have the following subqueries:

    SELECT
    ...
        ISNULL((SELECT Table1.Order WHERE Table2.STATUS <> 'R'),0) AS 'Order',
        ISNULL((SELECT Table1.Order WHERE Table2.STATUS = 'R'),0) AS 'Reject',
    ...
FROM Table1
LEFT JOIN
Table2
ON Table1.KEY=Table2.KEY

Is there a way to more efficiently write the above?

I was thinking an IF statement or something, but not quite sure how to write it.

I'm asking this because I read that it's good to minimize subqueries to improve performance.

Upvotes: 0

Views: 159

Answers (2)

Jasmina Shevchenko
Jasmina Shevchenko

Reputation: 797

Try to use CASE WHEN:

SELECT 
       /* ... */
       [Order] = CASE
                   WHEN t2.STATUS <> 'R' THEN t1.[Order]
                   ELSE 0
                 END,
       Reject = CASE
                  WHEN t2.STATUS = 'R' THEN t1.[Order]
                  ELSE 0
                END
       /* ... */                
FROM   Table1 t1
       LEFT JOIN Table2 t2
         ON t1.[KEY] = t2.[KEY]

Upvotes: 3

jsfRocks
jsfRocks

Reputation: 11

You can try using oracle "UNION" equivalent for SQL Server. I have worked on Oracle 10g and you can use like,

SELECT Table1.Order 
FROM Table1, Table2 
WHERE Table2.STATUS <> 'R' and Table1.id=Table2.id

UNION or UNION ALL

SELECT Table1.Order 
FROM Table1, Table2 
WHERE Table2.STATUS = 'R' and Table1.id=Table2.id

I am not sure whether this helped you or not...!! Anyways...

Upvotes: 0

Related Questions