question

satya avatar image
satya asked

Please help me on the below query...

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)
performance-tuning
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel avatar image
Squirrel answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That's a great take on the difficulty of tuning a single query in isolation instead of addressing the larger context.
0 Likes 0 ·
yeah, I'm wondering if this is appearing slow simply because it is being used 000's of times and. We may end up putting the results in a temp table to join on and save a lot of work but we need more info. Actually having problems repro my figures, think I may have missed clearing the cache between executions. Still not what I'd call slow though ...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.