CODEWITHSUNDEEP
t-sqlsybase
czchlong
czchlong

Reputation: 2584

Improve a query with duplicated SELECTS?

I am performing a query that selects two times from the exact same table on 2 different columns and performing a compare with the same set of data twice from another table.

My current method:

DELETE FROM MY_TABLE
WHERE MY_TABLE.BUY_ORDER_ID
IN ( SELECT #tmp_table.order_id FROM #tmp_table )
OR MY_TABLE.SELL_ORDER_ID
IN ( SELECT #tmp_table.order_id FROM #tmp_table )

Is there a way to improve on the query?

Thanks

Upvotes: 0

Views: 66

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Possibly. Need to test on your data.

DELETE MY_TABLE
FROM MY_TABLE m
JOIN #tmp_table 
  on #tmp_table.order_id = m.BUY_ORDER_ID
  or #tmp_table.order_id = m.SELL_ORDER_ID

If #tmp_table.order_id is the PK or unique then declare it.

Splitting hairs but maybe 

DELETE MY_TABLE
FROM MY_TABLE m
JOIN #tmp_table 
  on #tmp_table.order_id in ( m.BUY_ORDER_ID, m.SELL_ORDER_ID )

Upvotes: 1

Sasa Popovic
Sasa Popovic

Reputation: 53

I've tried this on SQL Server and it seems faster. I suppose you can do something similar on sybase?

    DELETE FROM MY_TABLE
    WHERE EXISTS 
    (
    SELECT * FROM #tmp_table 
    WHERE
    #tmp_table.order_id = MY_TABLE.BUY_ORDER_ID 
    OR
    #tmp_table.order_id = MY_TABLE.SELL_ORDER_ID
    )

Upvotes: 1

Related Questions