is it possible to write the below query in other way to improve the performance. The current query taking almost 6 seconds to retrieve 150000 records. Likely these tables expected to store more data in future.
from tab_1 t1
inner join tab_2 t2 on t1.tid = t2.fktid
left join tab_3 t3 on t2.fktid = t3.fktid and t2.did = t3.fkdid
WHERE CAST(t1.CreatedDate AS DATE) >= @startdatetime
AND CAST(t1.CreatedDate AS DATE) <= @enddatetime
order by t1.tid
For starters, don't CAST the table column; instead, make the input parameter match the data type of the column. If the CreatedDate column cannot be queried that way (e.g., it's stored as 'dd/mm/yyyy'), then you will always have performance issues with this query.
am storing datetime yyyy-mm-dd hh:mm:ss in this format (Eg 2024-07-10 11:24:30.057) .
only while retrieving data i have used cast by the way after your suggestion removed cast it helped to reduce few ms but not much.
How many rows in each table?
What indexes do the tables have?
It's likely that t1 should be clustered by CreatedDate first but can't even be sure of that without more details. Do you (almost) always query t1 by specifying a CreatedDate range?