Small Partition – The Daily WTF

Once upon a time, I was tuning a database performance issue. The accompanying database was an Oracle database, and the key problem was simply that the data needed to be partitioned. Great, simple, I wrote a script for the change, applied it to a test environment, collected some metrics to prove it had the expected effects, and submitted a request to apply to production.

And the DBAs came down on me like sledgehammers. Why? Well, according to our DBAs, the license we had with Oracle did not allow us to use partitioning. The feature wasn’t disabled in any way, but when the Oracle compliance check was done, they would be scared of us and charge us big bucks to use the feature – and if we wanted to enable it, it would cost $10,000 a year, and no one would he was not ready to pay.

Now, I have no idea how true that was actually. I have no reason to distrust the DBAs I’ve worked with, but maybe they were being overly cautious. But the result is that I had to manually split the data into different tables. The good news is that all writes always went to the most recent table, almost all reads went to either the current table or last month’s table, and everything else was basically inherited and while it could be used in a report, if slower than the crash experiment height, that was fine.

It was stupid and pointless, but it wasn’t the worst sin I’d ever committed.

Because of this I have at least some understanding of this stored procedure he found Let’s go.

ALTER PROCEDURE GetDataForDate
   @date DATETIME
AS
   DECLARE @sql nvarchar(max)
   SET @sql = 'select * from data_' + convert(nvarchar(30),getdate(),112)
   EXEC sp_executesql @sql

Now, this is for an MS SQL database, which doesn’t have any weird licensing around using partitions. But here we can see the manual partition in use.

There is a series data_yyyymmdd tables. When we call this function, it takes the given date and writes a query specific to that table. That means it exists a table for every day.

Ayende was called for this because one of the reports was running slow. This report was simple… used all the tables. Only UNIONissued them together. This, of course, removed all the benefits of partitioning, and it didn’t make the job of the query planner much easier. The execution paths it generated were not overly efficient.

At the time Ayende first found it, there were 750 tables. And apparently, as he ticked each day, a new table was created. And yes, someone manually updated the view, every day.

Ayende sent us this many tables ago and I dread to think how many tables have yet to be made.