Classic WTF: Manager of the Data Dump

It's a holiday in the US, where we catalog the things we're thankful for. I'm thankful that developers collectively learned to understand how databases work, and didn't start releasing databases that stored flexible documents with no real schema and could just be used as a data dump. That would be terrible! This classic WTF illustrates that. Originally. --Remy

J.T. is not well liked amongst the developers at his organization. As a Database Administrator, it's J.T's job to make sure that database structures and queries maintain data integrity and do not put an unnecessarily load on the server. This often gets in the way of the developers, who prefer to think of the database as a giant dump site where data gets thrown and is rummaged through to be retrieved. Things like "indexes," "valid data," and "naming conventions" are merely obstacles put in place by J.T. to make their life harder.

Generally, the submission-review-rejection procedure happens once or twice with most of the developers. But one particular developer -- a newly hired ".NET Wizard" named Frank -- turns the procedure into a daily cycle that drags on for several weeks. Following is Frank's reply to the first in a chain of rejections on a project that Frank was leading up ...

J.T.,> I cannot find the "DtxSurveys" table you asked to import this> into. Are you sure this is the right table?This is a new table we need. Please just use DTS to import the CSV file I sent and SQL Server will generate the table and the columns. That's how we're doing it in dev.Frank

J.T. replied, explaining explained that, per the database development guidelines, he must submit a CREATE TABLE script that explicitly defines the table.

J.T.,Fine, here is a script:CREATE TABLE [DtxSurveys] ([Status] varchar (8000) NULL, [Resp] varchar (8000) NULL, [Last] varchar (8000) NULL, ... snip ...[Supervisor_Name] varchar (8000) NULL, [Supervisor_CUID] varchar (8000) NULL, [Supervisor_UID] varchar (8000) NULL, [File_Data_Date] varchar (8000) NULL)Frank

J.T. wasn't quite sure where to begin. The script was useful, as in a catch-all dumpster sort of way. He replied to Frank explaining that the table had no primary key, no typed data, and exceeded SQL Server's maximum row size of 8060 bytes. Frank wasn't too pleased and replied back:

J.T.,When we run the script in dev, all we get is a warning that the row is too long. It's not an error, but whatever. Hereis the revised script:CREATE TABLE [DtxSurveys] ([Status] [nvarchar](255) NULL,[Resp] [nvarchar](255) NULL,[Last] [nvarchar](255) NULL,... snip ...[Supervisor_Name] [nvarchar](255) NULL,[Supervisor_CUID] [nvarchar](255) NULL,[Supervisor_UID] [nvarchar](255) NULL,[File_Data_Date] [smalldatetime] NULL)Frank

It was a slight improvement, as in a catch-almost-all garbage can sort of way. The only thing Frank changed was VARCHAR(8000) to NVARCHAR(255) and the File_Data_Date field. I'll spare you the rest of the back-and-forth on the CREATE TABLE script, but suffice it to say that it took several more revisions before it represented the actual: "Status" was a single letter, "Supervisor_UID" was a globally unique identifier, "Universal_ID" was an eight-character numeric identifier, etc.

I'll leave you with one of the last things that Frank sent over for review. It was the following query:

SELECT Product_ID, Product_name, case WHEN (SELECT SUM(ProductRequest.ProductFaceValue) FROM Requests WHERE (ProductRequest.ProductID = Product_ID) AND (ProductRequest_createdByUserUID = @uid) GROUP BY ProductRequest_productName) is null then 0 ELSE (SELECT SUM(ProductRequest.ProductFaceValue) FROM Requests WHERE (ProductRequest.ProductID = Product_ID) AND (ProductRequest_createdByUserUID = @uid) GROUP BY ProductRequest_productName) end as TotalDollarAmount FROM Requests WHERE (product_ID IN (@pid))

When J.T. mentioned that they will have to optimize the query because it ran for 2000 milliseconds, Frank explained that it's already optimized and can't run any faster. J.T. updated the query to use an ISNULL and increased the run time to 52 milliseconds. It was a small, 3800% decrease.

[Advertisement] Otter - Provision your servers automatically without ever needing to log-in to a command prompt. Get started today!