Posted: 6/28/2012 7:03:26 AM
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?
Posted: 6/28/2012 7:47:38 AM
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?
Posted: 6/28/2012 7:57:12 AM
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.
Posted: 6/28/2012 8:05:57 AM
Can I have your email address please, I cant post the original query due to restrictions.
Posted: 6/28/2012 8:09:43 AM
Posted: 6/28/2012 12:05:28 PM
email@example.com, in case you want to send it to me.
Posted: 7/6/2012 12:13:35 PM
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.
Posted: 7/6/2012 12:55:42 PM
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.