Help in Performance Improvement of a Date Function.

Question

Help in Performance Improvement of a Date Function.

Satnam Singh

Hall of Fame

Points: 3542
More actions
June 28, 2012 at 1:05 am

#96848

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?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

rhythmk SSCertifiable Points: 7271 More actions · Answer 1

Satnam Singh (6/28/2012)
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?

I doubt the query posted is correct.However what is A,B & C ?

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

Satnam Singh (6/28/2012)
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?

No idea, because the query you've posted won't run. Here it is with a little formatting to make that clear:

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'

Can you post the actual query you're using? Scripts for creating and populating tables A, B and C with some sample data will encourage folks to get involved.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 3

Satnam Singh (6/28/2012)
Any idea on this?

You'll need to at least post the rest of the query before anyone can really help. Otherwise, I'll just say that you have a nasty correlated subquery that needs to be fixed. You've also violated several best practices by using BETWEEN to find a date range.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 4

Hi Jeff. I'm trying to find an article covering the best practices you're referring to with "between" and date ranges but not having much luck. Can you point me to one?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Basically, when you query data based on dates (particularly when using date/time data type that can have time portion) you want a closed end comparision on the beginning part of the search and an open end comparision on the upper end.

For example, if you are looking for all orders created in January 2012 (OrderedDate) the query would look something like this:

select

so.* -- would actually list the columns to return

from

dbo.SalesOrders so

where

so.OrderDate >= cast('20120101' as datetime) and

so.OrderDate < cast('20120201' as datetime) -- using datetime in conversion as this is a SQL Server 2005 forum

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 6

Thanks, Lynn

So it sounds like in using BETWEEN, we are talking about the risk of inaccuracy as opposed to a performance hit, would that be correct?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Lynn Pettis

SSC Guru

Points: 442467

July 2, 2012 at 3:28 pm

#1507948

Yes. BETWEEN is actually converted to >= and <=.

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 8

jshahan (7/2/2012)
Hi Jeff. I'm trying to find an article covering the best practices you're referring to with "between" and date ranges but not having much luck. Can you point me to one?

Lynn Pettis did a good job above. Another article on the subject is available at the following link.

http://databases.aspfaq.com/general/should-i-use-between-in-my-database-queries.html

@Satnam Singh,

Correct me if I'm wrong... it still looks like you have code missing. There doesn't appear to be a FROM clause for the outer SELECT for the table alias of "B". If you want help optimizing the code, we need the whole ball of wax to even start.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 9

jshahan

SSCarpal Tunnel

Points: 4622

July 2, 2012 at 4:21 pm

#1507963

Thanks 🙂