Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Code Audit - The Beginning

For the next few months, I will be involved in an interesting project for a mission critical application that our company have outsourced to a well-known and respected consulting company here Sweden.
My part will be the technical details of the forecasting application now when our former DBA has left our company.

Today I took a brief look at the smallest building blocks; the Functions. No function is inline so I can assume some of the performance issues are derived from these.

One function I stumled across is very simple. All it does is to add a timepart from current execution time to the datepart from a variable.


CREATE FUNCTION dbo.GetDateTimeFromDate
(
   
@p_date DATE
)
RETURNS DATETIME
AS
BEGIN
   
DECLARE @ActualWorkDateTime DATETIME

   
SET @ActualWorkDateTime = CONVERT(VARCHAR, @p_date, 101) + ' '+ CONVERT(VARCHAR, GETDATE(), 114)

   
RETURN  @ActualWorkDateTime
END


This doesn't look to bad compared to what I have seen on the online forums. But there is a hidden performance issue here, besides being not an inline function, and that is the conversion to varchar and back to datetime. Also, this functions crashed in my tests when I changed dateformat to dmy. This is because the developer used style 101 in the convert function. If he had used style 112 the function would not have crashed no matter which dateformat value I use.

So to our next meeting I will explain to the consultants the issues I have with this function and the others that I've found.
A better choice for this function would be


CREATE FUNCTION dbo.GetDateTimeFromDate
(
   
@p_date DATE
)
RETURNS DATETIME
AS
BEGIN
    RETURN  (
               
SELECT  DATEADD(DAY, DATEDIFF(DAY, GETDATE(), @p_date), GETDATE())
           
)
END


See, now there is no conversion, it's inline and dateformat-safe! A generic function for adding the date part from one variable to the time part from another variable looks like this


CREATE FUNCTION dbo.fnGetDateTimeFromDatePartAndTimePart
(
   
@DatePart DATETIME,
   
@TimePart DATETIME
)
RETURNS DATETIME
AS
BEGIN
   
RETURN  (
               
SELECT  DATEADD(DAY, DATEDIFF(DAY, @TimePart, @DatePart), @TimePart)
           
)
END

Print | posted on Thursday, July 21, 2011 8:44 AM | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 ]

Feedback

Gravatar

# re: Code Audit - The Beginning

I always found the hardest part about writing software code to be working on the fine details. Going through code line by line is a lengthy process but if it what must be done then it's what must be done. I would assume that it would be easier to find and repair issues with the code than it would be to find a better way to write code that currently performs it's task correctly (just maybe not as efficiently as possible). in your opinion what was the hardest part of doing this code audit?
1/28/2012 6:45 AM | employee schedule
Gravatar

# re: Code Audit - The Beginning

I always follow the simple rule that is get it to work then go back and refactor it down to its simplest form. There is no point in staring at several lines of code for a lengthy period of time if it works you can always come back and clean it up after you have completed the section you are working on. Are you writing tests for your software? Writing tests will greatly improve writing bug free software and the user experience.
3/1/2012 6:00 PM | Milwaukee SEO
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET