Take a load off

Getting information from a SQL database can be fast or slow. The speed depends on many factors like database design (tables, views, etc.) and how queries are written. It is usually faster to parse the data using a query on the database server versus returning all data rows to the application and then parsing.

A coworker that developed a couple of ASP pages came to me and said, “The page showing who is and isn’t clocked in for the day is really slow. It’s taking anywhere from 3 to 7 seconds to finally show up. The view being used needs to be fixed.” I tried to figure out what view he was talking about because I hadn’t written one to show who was/wasn’t clocked in for the day.

It turned out that the ASP page was written to use a view that would return all time records for each person that had clocked in or out. The view joined five tables together in order to show all of the information required (name, location, etc.). The view didn’t just show the people clocked in on that day, it was written to show historical records as well. Once all of the rows were returned, over 800 and growing, the web server would then parse the records and only display the ones that matched today’s date. Anyone not clocked in would just have blank values for the time in and time out.

The average execution time for that view is 90 milliseconds (16 ms CPU time). The parsing time required to only display records for the current day was killing the page load time. I wrote a new view, using the same tables, that would show anyone that was clocked in as well as anyone that wasn’t. It utilizes the DATEDIFF, DATEPART and GETDATE functions in the WHERE clause to determine who is clocked in on the same day the view is executed. Here is the WHERE clause:

WHERE (DATEDIFF(dy, GETDATE(), ClockIn) = 0) AND (DATEPART(yyyy, GETDATE()) = DATEPART(yyyy, ClockIn))

The WHERE clause tells the query to only return rows if two conditions are met.

Where the difference (DATEDIFF) of the day of the year (dy) between the date on the server (GETDATE) and the clock in time is 0 (today).
Where the four-digit year (yyyy) of the server date and the clock in date are equal.

The year check is necessary because up to two (2) years worth of records will be stored in the table. The “day of year” value range is 1-366. You can see where an error would occur when the same date, but different years, are stored. The SQL server and the web server are time synched to the same NTP server.

Another query in the view is used to show anyone not clocked in. The two queries are joined with a UNION operator so that the data is returned in a single result set. Anyone that isn’t clocked in will have NULL values for their time. The new view had an average execution time of 10 milliseconds (2 ms CPU time). The web page was re-written to display the values returned from the SQL Server and now loads much faster because there is no need to parse the returned rows.

Rudeness is the weak man’s imitation of strength. – Eric Hoffer

03.Jul.07 Microsoft SQL