Working with complex SQL Server database schemas

I have a database, I have to maintain compatibility with SQL Server 2005 and I've been thinking about ways to reduce complexity and deal with performance issues.

My database is like most other's and filled with data, it's a lot of data and there's a lot of queries in there to. I have many stored procedures that have been evolving (for some time now) to meet business demands. And this is mostly fine, but I'm running into performance problems and my queries are becoming increasingly complex to manage.

At a first glance, I don't think there's anything wrong with my data model, it's not absurdly normalized (we already denormalize some things), yet I find myself not being able to write and run those blazing fast queries for powering my web interface AJAX queries because all the constraints that seems to somewhat haphazardly exist here and there.

So, I've thought about it, and I think I want to organize my database in rings. Let me explain.

• Basically, in the inner most ring, you'll find the most specialized set of data. These tables are completely denormalized and have been built by aggregating data from outer rings to make sure specific queries run really fast.

• The outer most ring is ideally "dumb" and is basically just a really bad place to put things.

• Between outer and inner is basically your conceptual model, these pull from the other rings or push to the inner rings and this is where you clean your data and make sure that it's correct.

• Data can only flow from an outer ring to an inner ring.

• I don't want to use triggers to keep the different rings consistent, instead I have a services and jobs that, listen, poll and run at regular intervals to ensure eventual consistency, cross the board.

Now, this is where I ask for advice and hope to get some input from experienced database people. It's my belief th开发者_StackOverflow中文版at I could get more out of my database this way. And it will allow me to address both complexity and performance issues at different stages. Maybe there's a common name for what I'm doing or maybe this is what the NoSQL movement is all about, but I don't know really, the idea has some appeal to me, but if I'm way out there, I'd like to hear about it before I make a mistake...

---

Although I basically agree with @TomTom's answer, I would phrase it differently: you have essentially developed the concept of the data warehouse (or data mart, to be specific) on your own. Buying a book on data warehousing is a great idea; attending a seminar or series of classes on the topic is even better. You've obviously done some serious thinking about this already and that will serve you well when you learn about best practices and the different approaches that have been developed.

---

You need a beginner b ook into databases. Seriously. Split it into an OLTP and an OLAP part - a data warehouse is in order. Get rid of the stored procedures. Then realize that your "lot of odata" is likely other peoples "jokes of data" - I work on a sysstem supposed to scale to around 60tb of data (that is 60.000) - our initial hardware has 21.000 gigabyte.

Your system sonds like you mix up a normal database (OLTP) with a data warehouse. Split them - this wont work. Split them ALSO IN HARDWARE. This is total standard - geta book about data warehouses.