March 27, 2012 at 2:26 am
Hey all,
I have this piece of code
SELECT * ,
CONVERT(DATETIME, RIGHT('00' + CAST(DAY(answertext) AS VARCHAR(2)), 2)
+ '/' + RIGHT('00' + CAST(MONTH(answertext) AS VARCHAR(2)), 2) + '/'
+ CAST(YEAR(AnswerText) AS VARCHAR(4)), 103)
FROM tblMI_PolicyAdditionalDetailsUp
WHERE ISDATE(answertext) = 1
AND ( REPLACE(REPLACE(REPLACE(answertext, '-', '/'), '.', '/'), '\',
'/') LIKE '[0-9]/[0-9]/[0-9][0-9][0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(answertext, '-', '/'), '.', '/'), '\',
'/') LIKE '[0-9][0-9]/[0-9]/[0-9][0-9][0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(answertext, '-', '/'), '.', '/'), '\',
'/') LIKE '[0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(answertext, '-', '/'), '.', '/'), '\',
'/') LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(answertext, '-', '/'), '.', '/'), '\',
'/') LIKE '[0-9]/[0-9]/[0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(answertext, '-', '/'), '.', '/'), '\',
'/') LIKE '[0-9][0-9]/[0-9]/[0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(answertext, '-', '/'), '.', '/'), '\',
'/') LIKE '[0-9]/[0-9][0-9]/[0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(answertext, '-', '/'), '.', '/'), '\',
'/') LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9]'
)
Now the where criteria of this code is used many times on our database, and it may start being used more. It also may be updated and improved at any time.
So i thought i would create a function like this:
create FUNCTION [dbo].[Is103Date]
(
@DateString VARCHAR(20)
)
RETURNS BIT
AS
BEGIN
RETURN CASE WHEN ISDATE(@DateString) = 1
AND ( REPLACE(REPLACE(REPLACE(@DateString,
'-', '/'), '.',
'/'), '\', '/') LIKE '[0-9]/[0-9]/[0-9][0-9][0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(@DateString,
'-', '/'), '.',
'/'), '\', '/') LIKE '[0-9][0-9]/[0-9]/[0-9][0-9][0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(@DateString,
'-', '/'), '.',
'/'), '\', '/') LIKE '[0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(@DateString,
'-', '/'), '.',
'/'), '\', '/') LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(@DateString,
'-', '/'), '.',
'/'), '\', '/') LIKE '[0-9]/[0-9]/[0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(@DateString,
'-', '/'), '.',
'/'), '\', '/') LIKE '[0-9][0-9]/[0-9]/[0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(@DateString,
'-', '/'), '.',
'/'), '\', '/') LIKE '[0-9]/[0-9][0-9]/[0-9][0-9]'
OR REPLACE(REPLACE(REPLACE(@DateString,
'-', '/'), '.',
'/'), '\', '/') LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9]'
) THEN 1
ELSE 0
END
END
and use it like this
SELECT *, CONVERT(DATETIME, RIGHT('00' + CAST(DAY(answertext) AS VARCHAR(2)), 2)
+ '/' + RIGHT('00' + CAST(MONTH(answertext) AS VARCHAR(2)), 2) + '/'
+ CAST(YEAR(AnswerText) AS VARCHAR(4)), 103)
FROM tblMI_PolicyAdditionalDetailsUp
WHERE dbo.is103date(answertext) = 1
However the first piece of code took 6 seconds to run the one with the UDF took 28 seconds.
Four times slower. 470k records. I was expecting it to be a little slower (and i know UDF`s within a where causes slow downs). Is there any way to improve my functions speed (not including CLR function - as i dont think i have access to create CLR functions on the server)?
many thanks for your advice.
Dan
March 27, 2012 at 3:04 am
WOW!!!!
Time to do some more reading up on that. It seems to make it go quicker (or the same speed - depending on when run) as the original query.
Thanks so much for your help!
Dan
March 27, 2012 at 3:51 am
Good to help. Inline Table Valued functions are underestimated in my opinion. I try to write most (or all) table valued functions inline, and I tend to convert some scalar functions to table valued for performance reasons, as in your scenario.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply