I’ve written a query for a report which is designed to pull some of information from a system transaction log (transaction_log), while also looking for a Master value from a second table (location_table) – this does not always exist, as sometimes the master_location or location_id fields can be NULL.

I have come up with the below query which produces my desired results, although it takes much longer to run the report since adding the ‘master_location’ column to the query.

I believe the performance of the query/report can probably be improved by ditching the subquery within the SELECT statement and using a join instead, but I am rusty with SQL at the moment and can’t get it working.

Can anyone please suggest improvements to this query to improve the performance?

SELECT 
   transaction_type, 
   description,
   id_num,
   product_id,
   quantity,
   location_id,
   CASE WHEN transaction_log.location_id IS NOT NULL THEN (select 
    TOP 1
    master_location_id 
    from location_table t1 
    where t1.location_id = transaction_log.source_location_id AND master_location_id IS NOT NULL
   ) ELSE NULL END as master_location,
  dummy_value, 
  employee_id,
 FROM 
    transaction_log WITH (NOLOCK)

I tried a join as below, which is returning over 2000 rows too many (compared to the above query). Should I maybe be filtering down the results further in a WHERE clause?

SELECT 
   transaction_type, 
   description,
   id_num,
   product_id,
   quantity,
   location_id,
   master_location_id,
   dummy_value, 
   employee_id,
  FROM 
    transaction_log t1 WITH (NOLOCK)
    INNER JOIN (select 
    location_id,
    master_location_id 
    from location_table) t2 on t1.location_id = t2.location_id