How to use MAX on a table with millions of records

Question

I've a table that contains all the information related to transactions , it has the deviceID making sales and also the transaction number ( This table has millions of rows). I've a list of some DeviceID on a temp table and I'm doing a join to the transactions table to get the max Transaction number but it takes forever to display results. Is there a better way to write the sql syntax to get the results in less time? Maybe doing a subquery into the same transactions table to get the MAX values , Any idea will be highly appreciated. My current code is below:

select a.DeviceID,
MAX(tq.Transaction_number)
from Datawarehouse.DBO.Transactions tq with (nolock)
/*The #Temp1 table contains the Device IDs we want to get the MAX transaction number ,
the total amount of Devices are between 1 and 1000 */
inner join #Temp1 a on a.DeviceID = tq.DeviceID
--We only want the MAX transaction number for sales ( Not Refunds)
where tq.Transaction_type = 'SALES'

Answer 1

You can use apply :

select t.DeviceID, tt.Transaction_no
from #temp1 t cross apply ( 
     select max(tr.Transaction_number) Transaction_no
     from Datawarehouse.DBO.Transactions tr
     where t.DeviceID = tr.DeviceID and 
           tr.Transaction_type = 'SALES'
) tt;

However, this will help you if you have index on (DeviceID, Transaction_type &Transaction_number) and don't forgot to SET NOCOUNT prior to the query especially when you are working with SSMS.