Performance improvement for select query

Hi all,

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.

declare @startdatetime date = '2024-07-01'
,@enddatetime date = '2024-07-10'
SELECT
t3.col1
,t1.CreatedDate
,t1.tid
,t2.fktid
,t2.did
,t2.col5
,t3.fktid
,t3.fkid
,t3.col3
,t3.col4
,t3.col5

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.

1 Like

Hi @ScottPletcher , thanks for reply.

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.

Br,
V8553

We have no idea what we're trying to tune.

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?