I have a SQL Server database (2008 R2, for grins) whose schema contains a frequently accessed table of records that can grow quite large over time. The vast minority of those records are frequently read for realtime access to current information, and the rest are historical.

In a perfect world, the most current records would live in a separate table and get moved into a different table when they expire. I don't have any influence over the database design or the application, so I've been thinking of splitting the table in two and replacing the table with a view of the same name.

I've already tested the performance impact on the query side, and there are some big gains. Since the application doesn't give a shit whether the object is a table or a view, I think I can make this a fairly seamless conversion.

The problem is that I'll need to write INSTEAD OF triggers or something to handle DML commands that the program will make against the view. The CREATE/UPDATE/DELETE stuff needs to be spoofed to a pretty high degree. The row counts returned must match what the application expects from modifying records in a single table, CREATE and DELETE must ensure the records get created in the correct destination table, and UPDATE must handle scenarios where the modified records switch tables and those where they do not.

Has anyone ever implemented this technique successfully? What are some challenges I should anticipate?

12/26/2013 4:22:37 PM

switch to using procs instead of direct table modification. then it doesn't matter what the db schema is at all

12/26/2013 4:28:23 PM

also: never use triggers.

12/26/2013 4:29:06 PM

Since you missed it:

Quote :

"I don't have any influence over the database design or the application"

How I'm gonna magically start using stored procedures when I can't change how the application interfaces with the database, dude?

P.S. Take that superstitious "never use triggers" claptrap back to Mickey Mouse Database School, bruh.

[Edited on December 26, 2013 at 4:32 PM. Reason : real talk]

12/26/2013 4:31:12 PM

oh I didn't see you couldn't change the application. my b

triggers are awful though for real. its basically hidden crap that no one will ever look for so when changes are made the triggers will always be forgotten.

how big is the table? How many archived rows need to be available to the application? Can you split the table and not use a view, but then supply the archived rows through reporting?

if it has to be able to modify rows that are in the "archived" table theres probably no nice way to do it without modifying the application which you obv. cant do.

add more indexes imo. can never have enough indexes.

12/26/2013 4:42:33 PM

I mean if you want to deal w/ triggers than go for it but thats some GOTO level shit right there.

12/26/2013 4:43:34 PM

If you're getting a performance increase by slicing the table into pieces either
A) your disk is not up to snuff and you should get faster disk
B) you are missing indexes which is preventing sql server from optimizing the query/caching

both of these can be fixed without modifications to the application and the indexes are obviously minor changes.

if the design is just so bad that sql server cant optimize it even with better resources AND you cant fix the application, then g/l w/ triggers. sux 2 be u

12/26/2013 4:48:00 PM

Shaggy said:

Quote :

"triggers are awful though for real. its basically hidden crap that no one will ever look for so when changes are made the triggers will always be forgotten."

This would be a rarely used customization, and I document those very well. I'm not very concerned about the maintainability when it comes to the mere existence of triggers on the view.

Quote :

"how big is the table? How many archived rows need to be available to the application? Can you split the table and not use a view, but then supply the archived rows through reporting?"

On the high end, it can be millions of rows, and everyone using the application is likely to get similar buildup over time. I can't really archive the data, because the application provides a history report for auditing that expects the data to be stored in the table.

Quote :

"if it has to be able to modify rows that are in the "archived" table theres probably no nice way to do it without modifying the application which you obv. cant do."

Well, INSTEAD OF triggers. They don't necessarily HAVE to be INSTEAD OF triggers, but I imagine that would be the most straightforward option.

Quote :

"add more indexes imo. can never have enough indexes."

Two of the heavy-duty queries that hit this table use ISNULL on a couple of columns. The table already has covering indexes for these queries, but whatever genius designed the queries didn't realize ISNULL was sending the query straight to Index Scan Land. Adding indexes at this point doesn't improve performance very significantly, which is what sent me down this path in the first place.

Quote :

"If you're getting a performance increase by slicing the table into pieces either A) your disk is not up to snuff and you should get faster disk"

I don't have any influence over the database design, which extends to the hardware implementation. The bottleneck isn't the disk, though.

Quote :

"B) you are missing indexes which is preventing sql server from optimizing the query/caching"

See my note about the use of ISNULL above. Covering indexes are present, but an index scan is slower than an index seek, period. Splitting the tables would reduce the amount of time the index scan takes in both circumstances.


[Edited on December 26, 2013 at 4:52 PM. Reason : ...]

12/26/2013 4:50:12 PM

P.S. For anyone else trying to talk me out of it, I don't need to be talked out of it. I'm asking for shit I might run into that I haven't already anticipated, and "don't use triggers" without a viable alternative within my constraints just does not interest me in the least.

12/26/2013 4:54:52 PM

Have you tried JavaScript? lol

12/26/2013 5:24:10 PM

What about a scheduled job to archive the non-current data into a new table? I know that's kind of like triggers / stored procedures, but its another approach to it.

12/27/2013 7:51:44 AM

Quote :

"I can't really archive the data, because the application provides a history report for auditing that expects the data to be stored in the table."

12/27/2013 7:54:25 AM

I've come back around to this. For arbitrary data access in Management Studio, it's working all right. However, in the application, it's failing.

The application's normal operation after inserting a new record is to immediately run scope_identity() to capture the identity value for the new record. However, there's no identity column on a view, and even though both tables unified by the view have identity columns, scope_identity() doesn't capture identity values inserted from inside a trigger. And I don't think there's any way in SQL Server to manually return a value for scope_identity(), so I think I'm going to have to give this one up.

6/9/2014 3:04:27 PM

trigger plz

6/9/2014 5:16:23 PM