Question

How is performance impacted when you have a table that, lets say could have over 1M rows, and there are 3-5 columns that are consistently NULL.

Is it bad design to have data structures in place that support this? Should we look at normalizing it a little more?

Thanks! -Calvin

P.S. Love this place.

Answer 1

Glad you like this place! It's only going to get better, when more people show up.

I'm not sure what you are asking in your second sentence, in particular I'm not sure what NULL has to do with normalization.

Tackling the first question...

I don't think NULL values cause performance problems per se, at least not that you'd notice. I know that historically, especially with those icky legacy DBMS products like [expletive deleted] and [bodily harm], folks used to worry a lot about fixed-versus-variable length columns, physical ordering of columns in rows, [excrement] like that.

What CAN hurt performance somewhat is inserting rows with all nulls, all zeroes, all empty character strings, and then later updating rows to fill in the values: instant row splits across multiple pages, instant fragmentation of your physical database storage. At least, that was true historically with SQL Anywhere... I am not absolutely sure V11 hasn't eased that problem, I will let Mr. Paulley et al fill in the blanks here.

I haven't run any INSERT/UPDATE/rowsplit tests lately, and when I did it wasn't easy to demonstrate the problem, you had to work at it. And it wasn't directly caused by NULLs per se, but by increasing value sizes over time.

I guess I'm saying, there are lots of reasons to hate NULLs but performance isn't one of them.

Answer 2

In addition to Breck's and Martin's responses, Nulls are quite ignorable w.r.t. to storage size:

From the SA 11 docs:

Column space utilization for NULL values is 1 bit per column and space is allocated in multiples of 8 bits. The NULL bit usage is fixed based on the number of columns in the table that allow NULL values.

Answer 3

Answer is WRONG see comments!

I think I know another situation despite later updates like Breck said, where you have a negative performance impact: If it is a varchar column and you are searching for NULL values.

Like: select * from x where stringcolumn is null

here I see always a sequential scan as the index on that column will not be used in contrast to:

select * from x where stringcolumn = ''

here the index will be used. So if you don't need to differ between undefined strings and empty strings I prefer to use a default value of '' instead of NULL.

The above said is valid only for char columns, for example for Date columns a search for NULL values will be index based.

Anyone, please correct if my observation is biased. Otherwise a technical confirm is also appreciated.