May 26, 2014 at 10:55 pm
Hi,
I have a query, which eats massive amounts of CPU because of a CLR function, which deals with local time (our server system time (US) is different from local time (Australia).
If I use:
andc.PublishDate <= dateadd(hour, +10, getutcdate())
everything is fine.
When I replace this with:
andc.PublishDate <= dbo.fnClrAestDateTimeNow_CLR()
the query time goes from 1 second to 45 seconds and the CPU consulption goes through the roof.
According to the developers all that CLR is doing is calculating local time. CLR should be faster then TSQL code.
Any ideas?
Thanks.
May 26, 2014 at 11:00 pm
Roust_m (5/26/2014)
Hi,I have a query, which eats massive amounts of CPU because of a CLR function, which deals with local time (our server system time (US) is different from local time (Australia).
If I use:
andc.PublishDate <= dateadd(hour, +10, getutcdate())
everything is fine.
When I replace this with:
andc.PublishDate <= dbo.fnClrAestDateTimeNow_CLR()
the query time goes from 1 second to 45 seconds and the CPU consulption goes through the roof.
According to the developers all that CLR is doing is calculating local time. CLR should be faster then TSQL code.
Any ideas?
Thanks.
Quick questions, why use a CLR for this and on what is the assumption that it should be faster than TSQL based?
😎
May 26, 2014 at 11:09 pm
To deal with clock moves.
CLR is precompiled code.
May 26, 2014 at 11:40 pm
Roust_m (5/26/2014)
To deal with clock moves.
Bear with me here, but why not do this in TSQL as a set based operation?
CLR is precompiled code.
This is true (the CLR are generally faster) if using iterative (loops) code, replacing set based functionality of the SQL Server with a row by row iterative CLR is never going to be faster. If it was, I guess we'd all be doing nothing but CLR:-D
😎
May 27, 2014 at 12:18 am
Eirikur Eiriksson (5/26/2014)
Roust_m (5/26/2014)
To deal with clock moves.Bear with me here, but why not do this in TSQL as a set based operation?
Can you please elaborate on this? How do you handle the clock moves in spring and autumn?
May 27, 2014 at 12:39 am
Roust_m (5/27/2014)
Eirikur Eiriksson (5/26/2014)
Roust_m (5/26/2014)
To deal with clock moves.Bear with me here, but why not do this in TSQL as a set based operation?
Can you please elaborate on this? How do you handle the clock moves in spring and autumn?
For this calculation, the time zones for the source and the destination have to be known. The time zone may have daylight savings/ summer time (DST) period, if the temporal value being calculated falls within a DST period, use that offset otherwise the normal offset.
Think of this as validity periods, DST is valid from spring until autumn.
Have a look here for the timezone database.
😎
May 27, 2014 at 1:17 am
The problem is, if you hardcode the clock move dates, you may get it wrong, as Australian government sometimes changes those dates.
The server timezone is (UTC-06:00) Central Time (US & Canada). The timezone that needs to go into the database is: (UTC+10:00) Canberra, Melbourne, Sydney
May 27, 2014 at 2:06 am
Roust_m (5/27/2014)
The problem is, if you hardcode the clock move dates, you may get it wrong, as Australian government sometimes changes those dates.The server timezone is (UTC-06:00) Central Time (US & Canada). The timezone that needs to go into the database is: (UTC+10:00) Canberra, Melbourne, Sydney
One should not hardcode the entries, here is a (simplified) suggestion of a schema
😎
+------------+ +-------------+
| Location | ,---|-| TimeZone |
+------------+ | +-------------+
| TimeZone |>|--' | UTC Offset |
| (Details) | | Valid From |
+------------+ +-------------+
TimeZone UTC Offset Valid From
----------------------------------
UK UTC +0 1.00 2014-03-30
UK UTC +0 0.00 2014-10-26
UK UTC +0 1.00 2015-03-30
May 28, 2014 at 4:19 pm
Roust_m (5/26/2014)
Hi,I have a query, which eats massive amounts of CPU because of a CLR function, which deals with local time (our server system time (US) is different from local time (Australia).
If I use:
andc.PublishDate <= dateadd(hour, +10, getutcdate())
everything is fine.
When I replace this with:
andc.PublishDate <= dbo.fnClrAestDateTimeNow_CLR()
the query time goes from 1 second to 45 seconds and the CPU consulption goes through the roof.
According to the developers all that CLR is doing is calculating local time. CLR should be faster then TSQL code.
Any ideas?
Thanks.
Passing the calculated value in as a parameter instead?
as in
declare @TimeOffset datetime
set @TimeOffset = dbo.fnClrAestDateTimeNow_CLR()
--Whatever your TSQL statement is
andc.PublishDate <= @TimeOffset
The CLR cost can't be accurately estimated AND is non-deterministic. You might help the exec plan by unconfusing it with explicit parameterization.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply