SQL syntax
-
Actually, syntax does matter. How DB2 handles the use of functions can be significantly different than how MS SQL Server does. Yes, with MS SQL Server, it does matter where you use a function with regards to SARG's. However, DB2 may handle this differently.
I am not a DB2 expert, nor claim to be. We were asked how to do this in MS SQL Server, that includes taking that issue into consideration. I still haven't seen your code for your own challenge. Until you are willing to post all your code as I did, this discussion is irrelevent.
-
> We were asked how to do this in MS SQL Server,
What makes Edwin's suggession extremely "useful".
> Until you are willing to post all your code as I did, this discussion is irrelevent
Actually I used your code. I mentioned I replaced Month with WK in DATEADD and replaced DATE(...) with UDF dbo.DateOnly(...) according to SQL Server syntax.
Simplified virsion of dbo.DateOnly is
DATEADD(dd, DATEDIFF(dd, 0, @Date), 0)
_____________
Code for TallyGenerator -
Sergiy:
Just thought you might like to know...
Out of curiosity I tested both your and my code on a real table in one of the databases I work with. I returned only the count of records to remove as much rendering time from the process as possible, and maximize the amount of server time in the process.
I ran each query 4 times, alternating between them. The queries returned 526,008 records out of 61,354,159 records in the table. The time I used for averaging was the elapsed time displayed at the bottom of the query window.
Total time for the four executions of your query was 249 seconds.
Total time for the four executions of my query was 248 seconds.
A difference of less than one half of one percent.
-
The challenge did not talk about functions on the left side of the SARG. Anyone familiar with MS SQL Server knows that if you put a function on the left side of a function you force the system into a table scan.
You challenged Edwin on the use of character conversions which he did on the right side of the SARG. Also, Edwin's solution used Date(timestamp_column) as a generic representation, not as an actual function (reread his post on page 1).
My tests (and Edwin's) shows that the character conversion is no better or worse than your date arthimetic.
What benefit from Edwin's solution? How about it shows someone one of many paths that they are free to explore.
-
Sergiy,
Since the execution times of the two queries are essentially the same, do you want me to tell you what the real difference is between our two queries? Simplicity.
If you look at your date calculation, you are using four functions (GetDate, DateDiff, DateAdd, and DateAdd) to return a date of exactly one month previous from the date the query is run.
If you look at my calculation, I am using four functions (GetDate, Convert, DateAdd, and DateAdd) to return a date of one month previous plus one day from the date the query is run.
The difference is that you are using two functions (DateDiff and DateAdd) to zero out the time portion of the date, whereas I am using only one function (Convert) to do the same thing.
If I were to modify my date calculation to return the same date as your date calculation I would simply remove the outer DateAdd function, leaving it with 3 functions to your 4. If you were to modify your date calculation to return the same date as my date calculation you would have to add one function to your date calculation (another DateAdd), leaving it with 5 functions to my 4.
So, whatever way you want to go to make the calculations do the exact same thing, your calculation would use one extra function.
-
Lynn, the challenge was about solution.
Edwin's solution was bad for 3 reasons:
1) failing provide correct SQL syntax when that was actually the only point of the topic;
2) using function in front of column in WHERE clause;
3) suggesting bad practice of removing time portion from datetime value using conversion to varchar.
You are not arguing points 1 and 2, so you are agree, Edwin's solution was unacceptable. Right?
Edwin, you told you have run your script and compared toi mine. How could you doi it id your syntax just is not correct? Which script did you compare to which?
Now about conversion.
I named it "stupid" and I can confirm it now.
As I posted before in this particular query it's used only ones to convert current time. So, it does not make significant difference for this particular query. But it does not make it acceptable.
Edwin, do you claim simplicity? I claim simplicity AND performance.
Your "simple" option is about 10 times slower than mine "complex" one. You just did not bother to compare.
So, let's do it.
-- Create temp table and populate it with data
SELECT TOP 100000 IDENTITY(int, 840000,1) AS ID, CONVERT(DATETIME, NULL) as DateValue
INTO #TestDConversions
FROM syscolumns, sysobjects, syscomments
UPDATE #TestDConversions
SET DateValue = DATEADD(hh, ID, 0)
----------------------
-- Now run 4 queries with different functions to compare performance
-- To minimise the time of passing data from Server to QA we return just single value from each query.
declare @CT datetime, @Z datetime
SET @CT = GETDATE()
SET @Z = 0
-- No functions applied
SET @CT = GETDATE()
select MAX(DateValue) from #TestDConversions
PRINT CONVERT(nvarchar(20), GETDATE()-@CT, 14)
-- Simplest function which does not actually modify any value from the column because there are no NULL values
SET @CT = GETDATE()
select MAX(ISNULL(DateValue, @Z)) from #TestDConversions
PRINT CONVERT(nvarchar(20), GETDATE()-@CT, 14)
-- Option I suggested
SET @CT = GETDATE()
select MAX(DATEADD(dd, DATEDIFF(dd, 0, DateValue), 0)) from #TestDConversions
PRINT CONVERT(nvarchar(20), GETDATE()-@CT, 14)
-- Edwin's option
SET @CT = GETDATE()
select MAX(CONVERT(datetime, CONVERT(nvarchar(20), DateValue, 111), 111)) from #TestDConversions
PRINT CONVERT(nvarchar(20), GETDATE()-@CT, 14)
drop table #TestDConversions
------------------------
(1 row(s) affected)
00:00:00:047
(1 row(s) affected)
00:00:00:063
(1 row(s) affected)
00:00:00:077
(1 row(s) affected)
00:00:00:627
-------------------------------------
As you can see option No.3 is almost as fast as simple ISNULL which is doing nothing but accepting parameters and returning result.
And it's way faster than option No.4.
You may run it as many times as you like, you may try it on different servers. Difference factor may depending on system configuration but it will never be less than 5. Average from values I've got is 9.
So, you still claim it's "another way of doing the same thing"?
Now about simplicity.
---------
CREATE FUNCTION dbo.DateOnly (@Datetime)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(dd, DATEDIFF(dd, 0, DateValue), 0)
END
-----------
Now you can use:
select dbo.DateOnly(DateColumn)
FROM AnyTable
Can you suggest more simple query?
Can you suggest better performing query?
P.S.
I was not born with a knowledge about this way of removing time portion. For some time I used another conversion, not that bad as conversion to varchar (conversion to varchar is actually the worst one in terms of performance), but still it was conversion.
Thanks to Jeff Moden who placed here DATEADD(DATEDIFF)) option and spent some time to proof me (and others who were listening) that his option is the best.
It would be a sheer stupidity if I would stick to my old solution after I know there is a better one.
And for sake of consistency I don't use “second best” options even when I need to work out a single value.
Which way you choose - it's up to you.
_____________
Code for TallyGenerator
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply