Please help me on the below query...

Question

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)

Answer 1

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.

Answer 2

0

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?

answered May 09 '12 at 01:43 PM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

That's a great take on the difficulty of tuning a single query in isolation instead of addressing the larger context.

May 09 '12 at 02:35 PM KenJ

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 ...

May 09 '12 at 02:56 PM Fatherjack ♦♦

(comments are locked)

Answer 3

0

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.

answered May 09 '12 at 03:35 AM

Squirrel
440 ● 1 ● 4

(comments are locked)

Please help me on the below query...

Follow this question

Related Questions