SQL query optimization for subqueries

Question

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.

"...I read that it's good to minimize subqueries to improve performance." You're hoping that a change will help. Hope doesn't scale well. Learn to read the execution plan instead. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented May 10, 2013 at 18:47

Martin Smith · Accepted Answer · 2013-05-10 19:29:24Z

3

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]

edited May 10, 2013 at 19:29

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

answered May 10, 2013 at 18:52

Jasmina Shevchenko

1

Sign up to request clarification or add additional context in comments.

2 Comments

Aaron Bertrand Over a year ago

+1 but please don't use 'single quotes' for aliases - certain forms of this are deprecated and it makes them look an awful lot like string literals. [square brackets] are much preferred.

kyle_13 Over a year ago

@Mike - Thanks, this was what I was looking for!...and thanks to all for your input.

Jasmina Shevchenko · Accepted Answer · 2013-05-10 19:15:04Z

0

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...

edited May 10, 2013 at 19:15

Jasmina Shevchenko

1

answered May 10, 2013 at 18:59

jsfRocks

112 bronze badges

Collectives™ on Stack Overflow

SQL query optimization for subqueries

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related