x

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)
more ▼

asked May 09 '12 at 03:18 AM in Default

satya gravatar image

satya
361 18 18 21

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

answered May 09 '12 at 03:47 AM

KenJ gravatar image

KenJ
19.1k 1 3 11

(comments are locked)
10|1200 characters needed characters left

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

answered May 09 '12 at 03:35 AM

Squirrel gravatar image

Squirrel
1.5k 1 2 4

(comments are locked)
10|1200 characters needed characters left

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?
more ▼

answered May 09 '12 at 01:43 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x50

asked: May 09 '12 at 03:18 AM

Seen: 889 times

Last Updated: May 09 '12 at 02:56 PM