SQL Tip: Keep your Presentation Logic in your Presentation Layer

jmkehayias — Fri, 20 Feb 2009 03:13:00 GMT

Whether you are just starting out in SQL Server, or you have been working in SQL Server for many years, eventually in your work, you cross a boundary where someone will want data formatted in a different manner than it is stored in the database server. Take for example DateTime data. It is not uncommon to see questions on the forums where someone asks how to return data from a DateTime column in a custom format, or to return the difference between two DateTime columns as hours, minutes and seconds. Now it is entirely possible to do this kind of logic inside of SQL Server using TSQL, I'll admit that. I'll also admit to having written some of the most convoluted TSQL there could ever possibly be to solve problems simply because it was all I knew.

I've used this analogy before, and I'll use it again. If you have to build a house of wood and screws and all that you have is a hammer, it is completely possible to drive a screw through a board using a hammer, and you could most assuredly build your house. However, if you were to take a little time and drive to the hardware store and either buy some nails, or a screwdriver, you are probably going to be able to first build the house faster, it will be much more stable, and it will last much longer. The same thing goes with software development. I used to write logic to do everything you can imagine using TSQL, and I to be reusable, I did much of it in functions and or stored procedures using output parameters and created more than my share of badly performing and overly complex TSQL code that did very little actual work.

Back on topic with this post, I find that a lot of people starting out in development work just like I did, writing TSQL and they stick with TSQL to everything in the world. Take the original example, the difference between two dates in a custom format like DD:HH:MI:SS for displaying the result. You can do this in TSQL, and putting the code in a function makes it really easy to use anywhere you want to:

CREATE FUNCTION dbo.testSQL(@first datetime, @second datetime) RETURNS VARCHAR(12) AS BEGIN DECLARE @difference datetime SET @difference=@second-@first DECLARE @retval VARCHAR(12) SELECT @retval = CAST(DATEDIFF(dd, '1900/01/01', @difference) AS VARCHAR)+':'+RIGHT(CONVERT(VARCHAR, @difference, 120),8) RETURN(@retval) END GO

Then again, you can do it in the presentation layer for example C#:

private static string CSharpDateDiff(DateTime first, DateTime second) { TimeSpan diff = second.Subtract(first).Duration(); return string.Format("{0}:{1}:{2}:{3}", diff.Days, diff.Hours, diff.Minutes, diff.Seconds); }

or VB.NET:

Private Shared Function CSharpDateDiff(ByVal first As DateTime, ByVal second As DateTime) As String Dim diff As TimeSpan = second.Subtract(first).Duration() Return String.Format("{0}:{1}:{2}:{3}", diff.Days, diff.Hours, diff.Minutes, diff.Seconds) End Function

Now to me, both of these are much easier to read than the TSQL code, and they are just as reusable in your application when placed in an appropriate library or class that can be imported or used by your code, but what about performance? I'm so glad you asked. Using the following 100000 row test table:

USE tempdb GO CREATE TABLE temptimes ( rowid INT IDENTITY PRIMARY KEY, date1 datetime, date2 datetime ) GO INSERT INTO temptimes SELECT GETDATE(), DATEADD(ss, RAND()*10000, DATEADD(mi, RAND()*10000, GETDATE())) GO 100000
and the following C# console app:
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; namespace ConsoleApplication3 { class Program { public struct RowData { public int RowID; public string DiffVal; public RowData(int rowid, string diff) { RowID = rowid; DiffVal = diff; } } static void Main(string[] args) { DateTime startTSQL; DateTime finishTSQL; DateTime startCLR; DateTime finishCLR; List<RowData> clrdata = RunCLRTest(); List<RowData> sqldata = RunSQLTest(); startCLR = DateTime.Now; clrdata = RunCLRTest(); finishCLR = DateTime.Now; startTSQL = DateTime.Now; sqldata = RunSQLTest(); finishTSQL = DateTime.Now; TimeSpan sql = finishTSQL.Subtract(startTSQL); TimeSpan clr = finishCLR.Subtract(startCLR); Console.WriteLine(string.Format("SQL RunTime: {0} seconds, {1} milliseconds", sql.Seconds, sql.Milliseconds)); Console.WriteLine(string.Format("CLR RunTime: {0} seconds, {1} milliseconds", clr.Seconds, clr.Milliseconds)); Console.WriteLine("Press Any Key to Exit!"); Console.Read(); } private static List<RowData> RunSQLTest() { List<RowData> ret = new List<RowData>(); SqlConnection conn = new SqlConnection("Server=.;Database=tempdb;Trusted_Connection=True;"); conn.Open(); SqlCommand cmd = new SqlCommand("select rowid, dbo.testSql(date1, date2) from temptimes", conn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { ret.Add(new RowData(dr.GetInt32(0), dr.GetString(1))); } conn.Close(); return ret; } private static List<RowData> RunCLRTest() { List<RowData> ret = new List<RowData>(); SqlConnection conn = new SqlConnection("Server=.;Database=tempdb;Trusted_Connection=True;"); conn.Open(); SqlCommand cmd = new SqlCommand("select rowid, date1, date2 from temptimes", conn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { ret.Add(new RowData(dr.GetInt32(0), CSharpDateDiff(dr.GetDateTime(1), dr.GetDateTime(2)))); } conn.Close(); return ret; } private static string CSharpDateDiff(DateTime first, DateTime second) { TimeSpan diff = second.Subtract(first).Duration(); return string.Format("{0}:{1}:{2}:{3}", diff.Days, diff.Hours, diff.Minutes, diff.Seconds); } } }

you will find that the CLR parsing is faster consistently for this simplistic test.

add to that the output of a SqlTrace for the execution:

and you can see that the TSQL function drives more CPU and has a longer duration in both of the executions performed by the C# code above. Speaking of which, why did I run both loads twice in the code? It was done intentionally, to remove any cost of setting up the SqlConnection to the SQL Server initially, and to remove anyone's ability to point to cached data being beneficial to one call over the other. The numbers reported by the app are for the second runs which would have loaded the data into the Buffer Cache so both work from cache. The SqlTrace numbers hold true to the C# numbers as well.

So in the future hopefully this will help you make the decision to do presentation work in the presentation layer, and leave the database server to what it does the best, CRUD (create, retrieve, update, delete) operations on data.

EDIT AFTER THE FACT:

After reading this post, Alexander Kuznetsov wrote a followup post called

Should I Always Keep My Presentation Logic in My Presentation Layer?

where he covers reasons for placing Presentation Login inside SQL Server. After reading this post, please jump over to his and see the other side of the story. Many thanks to Alexander for covering those in his post.

Search results matching tags 'Miscellaneous', 'Transact-SQL', and 'MSDN Forums'

SQL Tip: Keep your Presentation Logic in your Presentation Layer

Should I Always Keep My Presentation Logic in My Presentation Layer?