CODEWITHSUNDEEP
sqlgroup-bysql-server-2012query-optimizationcase
aearce2000
aearce2000

Reputation: 17

SQL Case Query Optimization

I have a subquery that I am using in a much larger select query that I need to optimize in order to upload it to Domo. I am trying to add a Yes/No column for whether or not a transaction includes a specific item. This is to be displayed on all lines of the transaction.

It works properly, but I'm working with so much data that the software times out before it can get through all of it. I can run the query with no problems in SQL SMS 2012, but is there a simpler/faster way to do this? Thank you!

case 
when transactions.transaction_id in 
    (select transaction_id
    from transaction_lines
    where item_id_0 = 'msa500'
    group by transaction_id) then 'Yes'
else 'No'
end as ContainsMSA500

Upvotes: 0

Views: 857

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can optimize this by doing:

(case when exists (select 1
                   from transaction_lines tl
                   where tl.item_id_0 = 'msa500' and
                         tl.transaction_id = transactions.transaction_id
                  )
      then 'Yes'
      else 'No'
 end) as ContainsMSA500

Then create an index on transaction_lines(transaction_id, item_id_0).

The problem with formulating this as a group by is that SQL Server has to generate the whole list every time. By formulating it using exists, SQL Server can look up the value in an index -- much, much faster in general.

In SQL Server 2012, the in version might be optimized:

(case when transaction_id in (select tl.transaction_id from transaction_lines tl where tl.item_id_0 = 'msa500' ) then 'Yes' else 'No' end) as ContainsMSA500

ALthough logically the same, removing the group by might help.

The final option would be to put this in the from clause of the query. That is hard to explain without seeing the whole query.

Upvotes: 2

Related Questions