April 30, 2009 at 2:48 pm
Amber.Brouillard (4/30/2009)
Nevermind, as I look at it closer, I see your point. Thanks for the correction.
Saints be praised 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2009 at 2:53 pm
TheSQLGuru (4/30/2009)
...there is also the same issue of index scan/seek if you are using a function on a column in a where clause.
Unless you use the solution posted by Lynn and others:
where
referencedate >= dateadd(dd, datediff(dd, 0, @comparedate), 0) and -- Beginning of this day
referencedate < dateadd(dd, datediff(dd, 0, @comparedate) + 1, 0) -- Beginning of next day
AFAIK dateadd and datediff are functions 😛
Generally it is true though!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2009 at 3:52 pm
Paul White (4/30/2009)
TheSQLGuru (4/30/2009)
...there is also the same issue of index scan/seek if you are using a function on a column in a where clause.Unless you use the solution posted by Lynn and others:
where
referencedate >= dateadd(dd, datediff(dd, 0, @comparedate), 0) and -- Beginning of this day
referencedate < dateadd(dd, datediff(dd, 0, @comparedate) + 1, 0) -- Beginning of next day
AFAIK dateadd and datediff are functions 😛
Generally it is true though!
Paul
Reread my post Paul - I said using a function on a COLUMN. Your example is using a function on a VARIABLE. Just a slight difference there. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 30, 2009 at 3:58 pm
TheSQLGuru (4/30/2009)
Reread my post Paul - I said using a function on a COLUMN. Your example is using a function on a VARIABLE. Just a slight difference there. :w00t:
Ah. Yes. Well. :blush:
A million apologies, deduct 100 smart-*** points from me!
Maybe the next version of SQL Server will be able to constant-fold entire columns :laugh:
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 6:39 am
I have gone through the entore thread. Could you please tell me how my solution is so suboptimal. Will it not work in any condition or will it be having a bad impact on performance. Just for better of my understanding...
Thanks,
Ashish
May 1, 2009 at 7:05 am
Ashish Pathak (5/1/2009)
I have gone through the entore thread. Could you please tell me how my solution is so suboptimal. Will it not work in any condition or will it be having a bad impact on performance. Just for better of my understanding...
Did you miss the entire discussion around using functions on columns and the effect that has on the ability of the optimizer to seek on an index rather than scan?
How?
:blink:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 7:40 am
Ashish Pathak (5/1/2009)
I have gone through the entore thread. Could you please tell me how my solution is so suboptimal. Will it not work in any condition or will it be having a bad impact on performance. Just for better of my understanding...Thanks,
Ashish
Like Paul said, the problem is that using a function on a column almost always prevents the use of an index seek (if an index is useable) and the engine must resort to an index scan (i.e. reading ALL of the index to find values, as opposed to very efficiently seeking down the btree to get the required rows). Another, sometimes incredibly damaging, issue is that statistics aren't usable either - which can lead to horribly inefficient query plans overall.
I will say that in many, if not most, cases this issue isn't even noticed. There may not even be an index available, in which case all queries would have to use a table scan. Or the where clause would hit such a large portion of the rows that an index scan is more efficient. Or there simply aren't many rows in the table in which case an index seek might take 3 reads whereas a scan would take 100 - simply not a noticeable difference in most cases. But if you have 100M rows an index seek might take 5 or 6 reads but the scan could take tens of thousands - and THAT will leave a mark. 🙂
Having said all that, there is still NO EXCUSE for writing suboptimal code like this once you know how to do it correctly. Yes, sometimes you have to be expedient in your coding to hit a deadline and taking shortcuts can allow you to write code faster. But this isn't one of those situations. It takes marginally more (if any) effort to do it correctly in this case.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 1, 2009 at 8:49 am
Using the DATEDIFF function works but can be very slow.
I prefer using:
DECLARE
@FromDate DATETIME,
@ThruDate DATETIME
SET @FromDate = CONVERT(VARCHAR, GETDATE(), 101) --mm/dd/yyyy 00:00:00.000
SET @ThruDate = DATEADD(DAY, 1, @FromDate)
SET @ThruDate = DATEADD(MS, -3, @ThruDate) --mm/dd/yyyy 23:59:59.997
SELECT * FROM Employee WHERE HireDate BETWEEN @FromDate AND @ThruDate
Using -3 millliseconds will give you the maximum time Sql Server can use.
May 1, 2009 at 9:34 am
Ed (5/1/2009)
Using the DATEDIFF function works but can be very slow.I prefer using:
DECLARE
@FromDate DATETIME,
@ThruDate DATETIME
SET @FromDate = CONVERT(VARCHAR, GETDATE(), 101) --mm/dd/yyyy 00:00:00.000
SET @ThruDate = DATEADD(DAY, 1, @FromDate)
SET @ThruDate = DATEADD(MS, -3, @ThruDate) --mm/dd/yyyy 23:59:59.997
SELECT * FROM Employee WHERE HireDate BETWEEN @FromDate AND @ThruDate
Using -3 millliseconds will give you the maximum time Sql Server can use.
I'd prefer this:
DECLARE
@FromDate DATETIME,
@ThruDate DATETIME
SET @FromDate = dateadd(dd, datediff(dd, 0, getdate()), 0) --mm/dd/yyyy 00:00:00.000
SET @ThruDate = dateadd(dd, 1, @FromDate)
SELECT * FROM Employee WHERE HireDate >= @FromDate AND HireDate < @ThruDate;
May 1, 2009 at 9:41 am
Ed (5/1/2009)
Using -3 millliseconds will give you the maximum time Sql Server can use.
True with datetime, but no longer true on SQL 2008 if you start messing with the higher precision datetime data types. I've seen someone carry that 'convention' over to DATETIME2 and then wonder why they're missing rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2009 at 9:55 am
Does the hack with FLOATs from DATETIMEs still work with DATETIME2?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 10:51 am
No. Nor the version that casts to int.
DECLARE @Today2 DATETIME2 = GETDATE()
SELECT CAST(FLOOR(CAST (@Today2 AS FLOAT)) AS DATETIME2)
Msg 529, Level 16, State 2, Line 5
Explicit conversion from data type datetime2 to float is not allowed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2009 at 11:42 am
Alright, alright, point taken - no more converting to float... now I need to go explain the dateadd/datediff thing to my developers...
I was hoping to be able to provide a nice UDF wrapper, but running Gail's test code from http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/, i find a 60X increase in CPU time when you add the UDF... 🙁
Does that make sense? Can a UDF really be that expensive?
(just in case I did something stupid, here is the function):
CREATE FUNCTION dbo.fn_DayOnly_DateTime (@DateValue DateTime)
RETURNS DateTime
WITH SCHEMABINDING
AS
BEGIN
RETURN dateadd(dd, datediff(dd,0, @DateValue),0)
END
GO
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
May 1, 2009 at 12:12 pm
Tao Klerks (5/1/2009)
Alright, alright, point taken - no more converting to float... now I need to go explain the dateadd/datediff thing to my developers...I was hoping to be able to provide a nice UDF wrapper, but running Gail's test code from http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/, i find a 60X increase in CPU time when you add the UDF... 🙁
Does that make sense? Can a UDF really be that expensive?
(just in case I did something stupid, here is the function):
CREATE FUNCTION dbo.fn_DayOnly_DateTime (@DateValue DateTime)
RETURNS DateTime
WITH SCHEMABINDING
AS
BEGIN
RETURN dateadd(dd, datediff(dd,0, @DateValue),0)
END
GO
Just for S & G's, try this with a CROSS APPLY:
CREATE FUNCTION dbo.ufnDayOnly(@DateValue DateTime)
RETURNS table
AS
RETURN dateadd(dd, datediff(dd,0, @DateValue),0) as DateOnly
END
GO
May 1, 2009 at 12:22 pm
Tao Klerks (5/1/2009)
Does that make sense? Can a UDF really be that expensive?
Yes.
Short answer. UDFs are not considered 'inline'. If they're run in a query, the run once for each row.
Long answer - http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 91 through 105 (of 110 total)
You must be logged in to reply to this topic. Login to reply