[dba-SQLServer] Any gut feelings?

JWColby jwcolby at colbyconsulting.com
Thu Nov 2 23:58:00 CST 2006


I have this huge table, 65 million records, ~700 fields.  The table has a
bunch of now useless fields which I will be deleting, basically old cass and
ncoa data from a previous (many years ago) address verification on the
table.  More importantly, it also has what I will call demographics data,
but extended into such things as brand preference for consumables, hobbies,
types of electronics purchases, etc.
 
Obviously the sheer size is an issue here.  What I would think to do, coming
from a small database background, is to create a PK on the big table, then
break this big table down into smaller tables, each focused on one "set" of
demographics data.
 
My question to you guys is, given the size of the table in terms of sheer
number of records, is this a valid strategy?  If I have an indexed and
identical PK in each table, can I break out the Address into a table,
age/income/race/religion into a table, then tables for boating, electronics,
medicines, etc. with each sub table only containing records where the
demographics info is populated.  IOW, not everyone has boating info, not
everyone has medical info, electronics info etc. so I end up with a smaller
subset of records in each table where there is only a record in that table
if the person has data for that demographic set.
 
Having done that can I then expect to be able to join up to a half dozen
tables to get inner join subsets, outer join mega-sets etc. in anything
approaching near real time (minutes)?
 
I am in the process of doing the very first (two) breakouts, address and
boating.  Obviously I have to create a new table, append the data in, then
build an index on the PK for starters.  Having done that I can do a quick
and dirty test, but I would like your opinions on the general feasibility of
this approach given the limitations of current (but state of the art)
desktop hardware, SBS 2003 and SQL Server 2000.
 
Having answered this (assuming that the answer is "it is probably a valid
approach", I then need assistance on various methods to determine the
existence of data within a given record of valid demographic data.  IOW, I
am pulling all 65 million records, a subset of fields (typically 10 to 30
fields) into a smaller table.  This smaller table will eventually have an
indexed PK, but NO indexes on the various fields.  One way I can think of to
do something like this is to generate a "validity" field where I store a
number which is simply a count of the fields with something other than a
zero length string.  So record 1 has 0 valid data fields, rec 2 has 3 valid
data fields, 3 has 12 valid data fields and so forth.  IF I could generate
such a query to get these counts and store it in a single field (and ever
get results) and then apply an index on that field, anything over a zero has
valid data and could be used to pull records, or (in reverse) records with a
zero in the "validity" field could be deleted from the table to drop the
number of records in that table.
 
Other thoughts on how to accomplish this objective?  Remember that this main
table is simply freakin huge, with probably 20-50 different demographic
groupings and some where the boundaries aren't even clear.  In order to do
this I will have to create that same number of tables, cut out the data and
get it into these new tables, index the PK, generate counts of the validity
data, delete records with no valid data etc.  Some kind of automation for
doing this would be nice, but basically, I can if necessary just brute force
it once I have the procedure down.
 
Ideas?  Objections?  "Your crazy to even think about this"?
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 



More information about the dba-SQLServer mailing list