Takes long time execution in the SQL Joins Query

Transact-SQL
1

Hi Experts,
I need your support to optimize the below query currently using joins. The issue is that Table2 is the static table data e.g the last row reside on '5/1/2000' and i want to keep continue the last available row data of Table2 with Table1 ( daily data) ( I have many tables which having static data in the history below is only two tables example)...
Any quick method the sql statement runs faster.

 Select t1.Item_id ,  
                   t1.Start_Date, 
                   t1.Item_name,
                   t2.Interval,
                   t2.Item_id 
    From Table1 t1
     left outer Join Table2 t2  on Table2.Item_id=t1_item_id 
               and  t2 .Start_date <= t1.Start_date   
               and  t2 .End_date > t1.Start_date
2

You can try using OUTER APPLY:

Select ..
  From Table1 t1
 Outer Apply (Select Top 1
                From Table2 t2
               Where t2.Item_id = t1.item_id
                 And t2.Start_date <= t1.StartDate
               Order By
                     t2.StartDate desc
                   , t2.EndDate desc
             )  As t3

Change the order by to identify the qualifying row - this will get the latest start date with the latest end date. You can also change the where clause to limit the rows - if needed.

3

Thank you Jeffw8713 for your response!
For multiple tables i need define a outer apply many times just after the below outer apply block?

And suppose i want to run a date range sql query from 01-April-2021 to 01-May-2021 the table2 having a row on date 01-April-2021 will the same data of Table2 will reflect along with Table1 until a new qualifying row exist ? TOP 1 still works?

 Outer Apply (Select Top 1
                From Table2 t2
               Where t2.Item_id = t1.item_id
                 And t2.Start_date <= t1.StartDate
               Order By
                     t2.StartDate desc
                   , t2.EndDate desc
             )
4

I don't understand your question about other tables - but if you have other 'history' tables where you want to get the latest qualifying row then an cross/outer apply with TOP 1 should work. It depends on the table and how you want to get the relevant data.

Other options are available - using row_number(), rank(), dense_rank() in a CTE and then joining to the CTE - for example.

If you have the criteria correct to identify the row in 'Table2' that you want - then it will work. But - it depends on your requirements.

1 Like
5

:blush: thank you sir i understood and it works !