Query tuning

Transact-SQL
1

Hello,
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

o main.a=sub.b

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

Else

slect null,null,null,null

2

Don't use

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)

3

P.S.Beware that

GetDate()-2

is two days ago to the EXACT millisecond ... it is NOT "The last two days" as us humans tend to think of it :smile:

1 Like
4

Thanks Kristen,for letting that know.
I have created the indexes and now the query performs better.

Thank you

5

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 :smile:

6

ahmeds08,

Please go through the coding standards and best practices articles