Hi All, Kindly help me on the below query, In most of the queries we are using the below query as sub query to restrict the employees who are on leave, but due to this query taking much time for execution and it is causing performance problems. please suggest me to tune this one to run my queries faster select empid from employee_leave where (DATEDIFF(day,leav_start,leav_end)+1) -(DateDiff(week,leav_start,leav_end)*2) >=3 AND cast(convert(varchar(20),getDate(),101) as datetime) BETWEEN leav_start and leav_end Table structures like below : create table employee( empid int primary key, empname varchar(30)) create table employee_leave( emp_leav_id int primary key identity(1,1), empid int references employee(empid), leav_start datetime, leav_end datetime)
The first thing that jumps out at me is the DATEDIFF in the where clause. Whenever you use a function on a column in the WHERE clause, you lose much of the efficiency that comes from the underlying indexes. If you can find a way to express that formula that doesn't involve using a function on the `leav_start` and `leav_end` columns, that should be helpful. Speaking of indexes, if you haven't already, it could be useful to create an index on the `leav_start` and `leav_end` columns to help out the between operation. If you `INCLUDE` the empid column in this index, SQL Server won't have to go back to the table from the index at all to get this value for the select statement. On the table side, it would be nice to use the DATE datatype for the `leav_start` and `leav_end` columns, so you don't have to store the empty time component and won't have to convert getDate to a varchar and back to drop the time.
this "(DATEDIFF(day,leav_start,leav_end)+1)-(DateDiff(week,leav_start,leav_end)*2) >=3" is killing your query. Are you able to store a no_of_days in your table instead of calculating it in this query ? anyway that part looks like calculating the of of days excluding weekend. You are better off calculating that and store it in the table when the leave record is created.
You say this query is taking "much time for execution". How long does it take, how much data are you working with? I have tested the query with two tables as you define them and I can query 10,000 employees with 500,000 leave records and get 120,000 records in the result set in approx 1s. The Execution Plan has a cost of 0.0032831 which is pretty low. I agree with the other answers saying the functions in your WHERE clause are undesirable but am wondering if you might be better off looking elsewhere for your slow TSQL. Can you post any more of your scenario - how you use this query etc etc?