Help in Performance Improvement of a Data Function.

Friends,

I am having a serious performance problem with the below query:

SELECT     DATEADD(mi, (SELECT     BiasInMinutes FROM  A WHERE   (Begin1 < B.CompletedWhen) AND (End1 >= B.CompletedWhen) AND   (Timezone = C.TimeZone_I)), B.CompletedWhen) AS Expr1) BETWEEN'2012-05-19' + ' 00:00' AND '2012-06-05' + ' 23:59'

The query executes for 3 minutes to fetch the data for 1 months,4 minutes for 2 months and around more than 15 minutes for more than a year.

I have indexes on the date colums but still performance is very poor.

Any idea on this?

Is that the complete query, where are the joins for B and C? Without knowing the structure of tables A, B and C it is not possible to give you an optimized query.

Can you not simply use  BETWEEN '2012-05-19 00:00' AND '2012-06-05 23:59' instead of using concatenation?

can you please provide table structure.. no of records and indexes created both Clustered and Non-Clustered, i will try to provide a quick fix.

Can I have your email address please, I cant post the original query due to restrictions.

navratan.verma@gmail.com,

senthilks@gmail.com, in case you want to send it to me.

Hi Satnam & Navratan,

If this issue is solved please you both share this problem & it's solution with US.

That will be a good  task to learn for our  team.

I have fixed this out such that there is more than 100% improvement in the performance of the query. I am actually writing an article on the same subject but will take atleast 2 weeks time for it to get released.

Thank you.