Need your help
I need to tune the below query,what indexes or changes can i do to make this query faster.
IF (SELECT sum(b) from test_main where a in (select b from test_sub where DATEDIFF(Day, c, getdate()-2) <=0 )) > 0
selet main.c, dbo.UDF_exists(main.a) exists_ ,e,SUBSTRING(e,6,5) sequence_ , cast(1000/sum(main.b) as decimal(18,2)) divide_
from test_main main join test_sub sub
where DATEDIFF(Day, main.c, getdate()-2) <=0 and DATEDIFF(Day, sub.c, getdate()-2) <=0
and cast(SUBSTRING(e,6,5) as int) between 1000 and 5000
group by main.c,main.a,main.e
DATEDIFF(Day, MyColumnName, GetDate()-999)
SQL has to make that calculation on every single row in the table ...
Instead you can do something like
MyColumnName < DATEADD(Day, -999, GetDate())
and then SQL can calculate the (CONST) value for the date expression and then use an INDEX (if one exists) to find rows where MyColumnName is earlier than that (or later, or between-two-values).
Even if no index exists SQL will not have to perform that calculation on every single row in the table, it can just compare the value against the CONST cut-off date.
(And you could also try adding an INDEX on the date column and comparing if the query is faster)
is two days ago to the EXACT millisecond ... it is NOT "The last two days" as us humans tend to think of it
Thanks Kristen,for letting that know.
I have created the indexes and now the query performs better.
If you have only created the index, and not changed the style of the code, you won't have got the full benefit. But hopefully you've done both
Please go through the coding standards and best practices articles