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
Table structures like below :
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
Speaking of indexes, if you haven't already, it could be useful to create an index on the
On the table side, it would be nice to use the DATE datatype for the
answered May 09, 2012 at 03:47 AM
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?
answered May 09, 2012 at 01:43 PM