ansaurus

Question

Please help explain if I'm destroying my DB Schema for the sake of performance :(

Answer 1

A:

"...So i'm tweaking the schema and queries to help speed some things up..." - I would beg to differ about this. It seems that you're slowing things down. (Just kidding.)

I like the Database Programmer blog. He has two columns for and against normalization that you might find helpful:

I'm not a DBA, but I think the evidence is in front of your eyes: Performance is worse. I don't see what splitting these 1:1 relationships into separate tables is buying you, but I'll be happy to take instruction.

Before I changed anything, I'd ask SQL Server to EXPLAIN PLAN on every query that was slow and use that information to see exactly what should be changed. Don't guess because a normalization guru told you so. Get the data to back up what you're doing. What you're doing sounds like optimizing middle tier code without profiling. Gut feelings aren't very accurate.

duffymo 2009-07-09 01:21:09

I've use the query plan visualized nearly EVERY TIME i run my queries in SSMS. With the joins between two tables, i'd get 2 million rows joining another massive amount. I've got plenty of indexes also on the parent and child tables. It's just that it need to join the two tables to get results. Also, i've ot split tables, just changed the relationship and null values. And when i ran the indexed views, they were indead waaay faster.

Pure.Krome 2009-07-09 01:34:15

Answer 2

+2 A:

null values generally are not used in indexs. What you've done is to provide a sentinel value so that the column always has a value which allows your indexes to be used more effectively.

You didn't change the structure of your database either, so I wouldn't call this denormalizing. I've done that with date values where you have an "end date" null denoted not ended yet. Instead I made it a known date way in the future which allowed for indexing.

I think this is fine.

geofflane 2009-07-09 01:25:10

Thanks for the comment. You're 100% correct that i've only changed from nulls to a sentinel value (never heard that word used like that, before). I feel dirty and I'm seeing how many other people do this -> which you do. So i'm not that alone, it seems :) Also, i thought NULLS didn't effect indexing.. ??

Pure.Krome 2009-07-09 01:36:37

nulls aren't indexed because null != null so there's no need to even look at the index, it's faster than using a sentinel value.

Tony Lee 2009-07-09 02:14:55

Answer 3

+1 A:

Database should always be designed and initially implemented in 3NF. But the world is a place of reality, not ideals, and it's okay to revert to 2NF (or even 1NF) for performance reasons. Don't beat yourself up about it, pragmatism beats dogmatism in the real world all the time.

Your solution, if it improves performance, is a good one. The idea of having an actual radio (for example), manufactured by nobody and having no features, is not a bad one - it's been done a lot before, believe me :-) The only reason you would use that field as NULL was to see which vehicles have no radio and there's little difference between these queries:

select Registration from vehicles where RadioId is null
select Registration from vehicles where RadioId = 0

My first thought was to simply combine the four tables into one and hang the duplicate data issue. Most problems with DBMS' stem from poor performance rather than low storage space.

Maybe keep that as your fallback position if your current de-normalized schema becomes slow as well.

paxdiablo 2009-07-09 01:35:45

By creating an indexed view, i've essential merged all 4 tables into one, while keeping it normalized (in my woeful db theory) understanding. To do an indexed view, I've had to make them all inner joins and therefore, have ZERO Id's instead of NULL's. So you're saying that this is still an acceptable practice, if not slightly common?

Pure.Krome 2009-07-09 01:40:24

It's acceptable *and* more common than you think. Databases aren't designed then left alone, they're continuously tuned based on the data within them. For example, we collect stats and, if need be, re-baseline our schema every six months (although it's been a few years since we've had to change anything since the type of data has sort of stabilized) in order to keep performance high.

paxdiablo 2009-07-09 01:48:01

And it's pretty acceptable practice to use a 'special' value (in my case, Id=0) instead of null's to replace outer joins with inner joins?

Pure.Krome 2009-07-09 02:01:46

Yes, provided it's documented somewhere that, and why, you did it. This should be in the design docs for the DB, and I'd slip a short note into the nvarchar(50) fields for id=0 in case the design docs ever disappear :-).

paxdiablo 2009-07-09 03:06:24

Answer 4

A:

im running into the same issue of performance vs academic excellence. we have a large view on a customer database with 300 columns and 91000 records. we use outer joins to create the view and the performance is pretty bad. we have considered changing to inner joins by putting in the dummy records with a value of zero on the columns we join on (instead of null) to enable a unique index on the view.

i have to agree that if performance is important, sometimes strange things have to be done to make it happen. ultimately those who pay our bills don't care if the architecture is perfect.

2009-09-09 21:40:40

ansaurus

tags:

views:

answers:

Please help explain if I'm destroying my DB Schema for the sake of performance :(

related questions