Tables have too many nullable columns

[Doug Baroter]

Hi,

I've enherited a big mess, a SQL Server 2000 database with
approximately 50 user tables and 65+ GB data, no explicit
relationships among entities (RI constraints whatsover), attempt to
create an ERD would more than likely kill the relatively complexed
app, the owner would want to drop out of window, so,
I don't intend to do that, you get the picture, sorry no DDLs this
time, and many tables have many nullable columns, joins slows down the
system quite a bit, what's my best bet to improve its performance?
change most of these nullable columns into non-nullable with default
value of something like ' ', any thoughts along this line would be
appreciated.

[Doug Baroter]

OK. Here's some more detail to the question.
DDL for one critical table (I'll have to search tons of scripts/codes
to find
which ones reference this table):

select left(name,15) colName, isnullable
from syscolumns
where id = object_id('bask etItemBak');

Resultset:
colName isnullable
--------------- -----------
BasketItemID 0
SessionKey 1
ItemID 1
ItemNo 1
ItemName 1
ItemModel 1
ItemManufacture 1
ItemCategory 1
ItemCategoryID 1
Quantity 1
Price 1
ShippingOpt 1
ShippingCost 1
URL 1
DT 1
IP 1
OrderID 1
ExtraInfo 1
myCost 1
ShippingOneItem 1

(20 row(s) affected)

So, the question is, if I turn most of these columns from nullable to
non-nullable would it improve transaction (of this table) to a
noticable degree?
I read somewhere as well Microsoft site yesterday, NULLABLE column
requires "special care" hence hinders performance. What's your
opinion?

And back to the main question, any thing else that is obvious that I
should look into?

Thanks.
qw********@boxf rog.com (Doug Baroter) wrote in message news:<fc******* *************** ****@posting.go ogle.com>...

Hi,

I've enherited a big mess, a SQL Server 2000 database with
approximately 50 user tables and 65+ GB data, no explicit
relationships among entities (RI constraints whatsover), attempt to
create an ERD would more than likely kill the relatively complexed
app, the owner would want to drop out of window, so,
I don't intend to do that, you get the picture, sorry no DDLs this
time, and many tables have many nullable columns, joins slows down the
system quite a bit, what's my best bet to improve its performance?
change most of these nullable columns into non-nullable with default
value of something like ' ', any thoughts along this line would be
appreciated.

[Steven Wilmot]

In most cases, it is not the data in the datble that poses the problem, but
the query used to extract data from it.

SQL Profiler will help you find out which queries are run the most, and
which ones take up the most CPU time ...

From there, you should look at the best way of improving those individual
queries. NULLable columns may give you a few percentage-points
performance-change m and a minor impact on data-size, but your most likely
cause of problems is likely to be any queries.

Hope this helps.

Steven

[Doug Baroter]

Thanks for the idea of using SQL Profiler, unfortunately we don't have
the permission to run it on a hosted-server by our ISP; btw, the
programming logic for the application (not mine) does not look bad but
quite a lot of bugs though. I would humbly think the performance of a
database-driven application depends on at least two critical factors
of db design and method of data manipulation.

"Steven Wilmot" <st*********@wi lmot.me.uk> wrote in message news:<3f******* *************** *@news.aaisp.ne t.uk>...

In most cases, it is not the data in the datble that poses the problem, but
the query used to extract data from it.

SQL Profiler will help you find out which queries are run the most, and
which ones take up the most CPU time ...

From there, you should look at the best way of improving those individual
queries. NULLable columns may give you a few percentage-points
performance-change m and a minor impact on data-size, but your most likely
cause of problems is likely to be any queries.

Hope this helps.

Steven

[Doug Baroter]

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* **************@ 127.0.0.1>...

Not being able to run the Profiler, is a severe handicap. If you don't
know what queries that are slow, you cannot fix them. So I would spend
all my energy to resolve this problem, one way or the other. I figured out a workaround (setting timer for blocks of code including
query) for that, downside, consumes more time to do it.
If you cannot
run Profiler at the current host, then try to get a copy of the database
on a machine at home. A while ago, I thought about that, the owner hesitated on my idea.
Now, I'm not interested in doing that.
Changing nullable columns to NOT NULL is not likely to give any significant
performance enhancements. Thanks for your opinion.
More likely is that you add new bugs to the
system.

Entirely possible to add one or two or this sort of range of bugs,
however,
very unlikely to exceed that for a couple of reasons, a) I suppose I
know what I'm doing, b) I took caution in the bug fixing (occasional
oversight).

[WangKhar]

Just being nosey - but you have trimmed your data types down to just
what you need? The nulls are likely as ES says to cause a headache...

qw********@boxf rog.com (Doug Baroter) wrote in message news:<fc******* *************** ****@posting.go ogle.com>...

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* **************@ 127.0.0.1>...

Changing nullable columns to NOT NULL is not likely to give any significant
performance enhancements.

Thanks for your opinion.

More likely is that you add new bugs to the
system.

Entirely possible to add one or two or this sort of range of bugs,
however,
very unlikely to exceed that for a couple of reasons, a) I suppose I
know what I'm doing, b) I took caution in the bug fixing (occasional
oversight).

[Doug Baroter]

Thanks for your input.

Wa******@yahoo. com (WangKhar) wrote in message news:<bb******* *************** ****@posting.go ogle.com>...

Just being nosey - but you have trimmed your data types down to just
what you need? The nulls are likely as ES says to cause a headache...