May 16, 2006 at 1:49 pm
I'm working in a very high-volume environment where every fraction of a second counts. To that end, I'm looking at some stored procs that create, populate, update and select on temp tables. In some cases, there are columns (often several) that are defined as NOT NULL. I don't really see the need for this and I suspect that this is a performance hit.
Am I correct in this suspicion?
May 16, 2006 at 2:37 pm
It depends.
Making a column nullable may increase the number of rows per page, thereby reducing overall table size. Potential performance improvement.
Making a column nullable where the column is subject to subsequent update operations may prevent "update in place" and cause each row updated to generate twice as many log writes. Potential performance hit.
Making a column nullable where it is part of an index may cause the optimizer to select a completely different execution plan. Performance could go either way depending on optimizer.
May 16, 2006 at 2:50 pm
There is no indexing (though I may be changing that...).
So if it's just insert and select, then your second point does not apply so removing the NOT NULL restriction could potentiall help.
What I've also been wondering is if there is any small fraction of a performance hit for validating the "not null"-ness of the columns.
May 16, 2006 at 4:08 pm
May 16, 2006 at 4:12 pm
If a columns in your table nullable SQL Server must maintain a special bitmap for each row to indicate which columns are nullable which is an overhead.
If the columns are of a fixed length then all space required to store the column is still reserved whether it is null or not so your not going to save any space as if you were using variable length dataytype then it would not reserve the space it was not using anyway so all your are doing is introducing an overhead.
hth
David
May 16, 2006 at 4:40 pm
Well, not "my" temp tables. But yes, the procs that I have inherited have temp tables with columns defined as NOT NULL.
May 17, 2006 at 9:00 am
That's what I thought you were saying. There will be a small amount of overhead in, like you said, checking for the nullness of the column upon insert, but I do not think it will be measurable. I know that you said that every little bit helps, so you may want to set up a test where you run your stored procuedures multiple times, change the columns to allow nulls and run the same test. Capture everything with profiler so that you can compare them. I have a feeling that you will not see a measurable difference.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply