Wednesday, December 27, 2006

Are strings better in the database or VB.Net code?

I'm an unabashed code man. And while I firmly believe that some operations should always be carried out inside the database, I also firmly believe that there are some operations which it makes no sense at all to run within the database.

And I find myself occasionally bumping heads against one of our engineers that believes any type of data manipulation belongs within the database.

My thought is we need to create a simple platform to test it.

Basically, here's the situation. We have a web application which prompts the user for data and then builds a large RTF file from the user entered data in addition to boiler plate text. An RTF file is nothing but a large specially formated string - and since the creation of that string is data manipulation, he believes that it should be done within Stored Procedures and Functions within the database.

My argument is that it needs to be tested and metrics generated for both creating that large string in the database and creating the large string within a VB.Net class using StringBuilder.

Unfortunately, I am unable to find any metrics or tests saying that building strings is more efficient in one form or the other, which surprises me slightly. One would think that such ammo would be used loudly and often by either the all-database or the all-code sides, especially in the war over using Stored Procedures to access all data in SQL Server.

And while I know that SQL Server 2005 has the new LOB's, we're still using SQL Server 2000 for this project, so we're dealing with VARCHAR(8000) and TEXT fields. And manipulating TEXT fields is down right evil within SQL Server.

Of course my hesitation to embrace this could be based entirely on the desire to never work with large string objects in SQL Server and everything else is just mad justifications to support my on biases, but I think I will be running the tests when I get some free time just for myself.

No comments:

Blog Widget by LinkWithin