473,327 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Tables have too many nullable columns

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.
Jul 20 '05 #1
6 4156
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('basketItemBak');

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********@boxfrog.com (Doug Baroter) wrote in message news:<fc**************************@posting.google. 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.

Jul 20 '05 #2
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
Jul 20 '05 #3
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*********@wilmot.me.uk> wrote in message news:<3f***********************@news.aaisp.net.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

Jul 20 '05 #4
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).
Jul 20 '05 #5
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********@boxfrog.com (Doug Baroter) wrote in message news:<fc**************************@posting.google. 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).

Jul 20 '05 #6
Thanks for your input.

Wa******@yahoo.com (WangKhar) wrote in message news:<bb**************************@posting.google. 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...

Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Greg Ofiesh | last post by:
Anyone who can help, I have two tables T1 and T2. T1 has fields K1 and F2 and T2 has fields K2 and F1. F1 is the foreign key relating to K1 and F2 is the foreign key relating to K2. My...
4
by: jane | last post by:
HI, I try to create summary table like following: create table summary (a int, b int, c int) (select a.aa, b.bb, b.cc from table_a a ,table_b b where a.key=b.key) data initially deferred...
18
by: Ken Kazinski | last post by:
Does anyone know of a good example for creating a access database and then tables within that database. All the examples I have found so far use a SQL database. Thanks, Ken
12
by: Steven Livingstone | last post by:
I've just blogged some stuff on Nullable types in net 2.0. http://stevenr2.blogspot.com/2006/01/nullable-types-and-null-coalescing.html Question however as to why you can't simply get an implcit...
0
by: Larry Lard | last post by:
There seems to be something a bit lacking in the way the dataset designer thing deals (or rather doesn't) with nullable fields in VS2005. Maybe it's cos I'm using VB2005 Express (which is variously...
5
by: GG | last post by:
I am trying to add a nullable datetime column to a datatable fails. I am getting exception DataSet does not support System.Nullable<>. None of these works dtSearchFromData.Columns.Add( new...
8
by: DaFrizzler | last post by:
Hi, I have received the following email from a colleague, and am quite frankly baffled by the idea. I am just wondering if anyone has any advice or suggestions about this???? === BEGIN MAIL...
12
by: nyathancha | last post by:
Hi, I have a question regarding best practices in database design. In a relational database, is it wise/necessary to sometimes create tables that are not related to other tables through a...
1
by: sherifffruitfly | last post by:
Hi all, I'm tasked with adding a few fields to our database, and all of the stuff that goes along with it (updating c# UI code, etc.). The database is a few hundred tables, with all of the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.