[dba-SQLServer] My learning experience - was RE: Clustered index

jwcolby jwcolby at colbyconsulting.com
Sun Dec 9 14:33:58 CST 2007


This email is going to present results of a "side by side" (but not quite
apples to apples) comparison of the work I have done over the last few years
on a huge database which I lovingly call "the database from hell".  But
first some background.  

I have three different databases.  One called HSIDRaw (first generation),
the next called HSID (second generation),  the next called HSIDVert.  

tblHSIDCean: 

HSIDRaw has a table called tblHSIDClean which is all of the original data,
65 million records, with close to 700 fields.  This data comes from an old
"fixed width" text file which I imported years ago.  It was this database /
table that I have mostly been using for the last couple of years.  Each
record in tblHSIDClean has an autonumber PKID field which is the PK of each
record in tblHSIDClean, created at the time I did the data import.  This
PKID carries through to everything else I do.  The data in this table
consists of responses to polls, so although there are 65 million records in
the database, not all people answered every poll.  There is data about boat
ownership (about 7 million responders), income (about 40 million
responders), travel (unknown number of responders ATM) etc.  So every field
does not contain data for every record, in fact in some fields the data is
sparsely populated.  

I took the name / address info from this table and had it "address
validated", the results of which sit in another table of ~51 million valid
deliverable name / address records (tblAZHSID).  That data carries the PKID
from tblHSIDClean as well so that I can relate a clean address back to these
tblHSIDClean records.  That "address validated" table contains ONLY address
validation info, and NONE of the data in tblHSIDClean.  tblAZHSID is of no
interest to this discussion other than so that you can conceptualize how the
data might be used to get "names / addresses of people WHERE...", i.e.
mailing list data, which is the ultimate requirement of this data.
tblAZHSID has address info, tblHSIDClean contains WHERE info, and they are
related on PKID.

INDEXES: In tblHSIDClean I used indexes on the fields that the client would
ask about.  I would build the index on demand (as I needed it) and they
slowly accumulated.  The indexes were always just a single field, no "cover
index" concept at this point.  These indexes provided fast COUNTS but did
not work well for returning PKID sets for further processing in complex
queries with WHERE clauses covering many different unrelated fields - WHERE
Income IN() AND BoatLength IN() AND HasDogs = 'Y'.

tblHSID: 

In the last month I extracted tblHSID From HSIDClean, out into a new
database called HSID.  tblHSID has the leading ~100 fields stripped off.
These 100 fields are OLD address validation stuff and is never used at all
for any reason.  Thus tblHSID has 584 fields.  Additionally, tblHSID only
has 51 million records.  When I did address validation years ago It turned
out that about 14 million records "fell out" of the 65 million records
because of bad address info.  The 51 million records represent all the
records that were deliverable.  This table carries the PKID field from
tblHSIDClean so that I know which record in tblHSID (new) relates to which
record in tblHSIDClean (old).  To summarize, tblHSID has ~100 fewer fields
and 14 million fewer records than tblHSIDClean.

INDEXES: In this database I started building "cover indexes" where each
index was GENERALLY still for a single field but would also include the PKID
as well.  In a couple of cases I build a cover index where a logical group
of fields were all contained in the index, as well as the PKID.  In these
cover indexes I place the data field first in the index, and then the PKID,
on the theory that I would be doing WHERE clauses using this index to look
for specific values in the data field.  In some cases the results are simple
counts, how many records with HullType = 'A' (boaters with aluminum hulls)
or NarrowIncomeBand = 'T' (people with incomes > 150K).  So covering indexes
SHOULD make the process of getting counts of specific data elements faster,
and in particular should make the process of getting actual PKID sets for
these data elements faster.  One of the uses of the database is to get
actual addresses for all of the people WHERE..., for which I need PKID sets
in order to facilitate comparisons and joins, and also to get at the name /
address data over in tblAZHSID.

tblHSIDVert:

In the last few days I created HSIDVert database with tblHSIDVert_001_083,
tblHSIDVert_084_143, tblHSIDVert_144_233 etc.  Each table contains data
points from specific sets of fields.  You can think of each table as a
vertical partition of tblHSID, although of course it is more than that.

In these tables I completely restructured the data in the manner I have
discussed in the last few days.  I got a list of all of the remaining data
fields (all of the fields in tblHSID), 584 fields total, and placed the
field names into tblHSIDFldName with an autoincrement PKID which becomes the
FieldNameID.  I then created tables named as above, with just three columns,
FldNameID, FldValue, and PKID.  PKID of course is the PKID from records in
tblHSID and tblHSIDClean and simply tells me which RECORD back in tblHSID /
tblHSIDClean each "data point record" is coming from.  FldNameID comes from
the above mentioned tblHSIDFldName and simply tells me which FIELD each data
point comes from.  Data is the data point or element from the field
specified.

I then created a process that pulls ONLY the data points out of tblHSID for
each field in tblHSID, and place those data points into tblHSIDVert.  IOW I
grabbed "just the facts ma'am" and "stacked them" into a single column of
data, with a pair of other fields to keep it all straight - the FieldNameID
and the PKID.

INDEXES: In HSIDVert I created a single clustered index covering all three
fields, in the order of FldNameID, Data, PKID.  If you think about the
concept, the "second generation" (tblHSID) is of course better than the
"first generation" (tblHSIDClean) for three reasons, fewer records, fewer
fields, and covering indexes.  HOWEVER I still have to create these covering
indexes as the client asks for results using a specific field.  I.e. each
field has its own distinct covering index, and there are still 584 possible
fields needing a cover index.  That's a lot of indexes.  The "third
generation" has some advantages in that ONLY the actual data is contained in
the table, (no empty fields) and EVERY field gets an index automatically due
to the fact that this table uses a cover index (the clustered index).

So which "schema" will win the race?  

Results in part two of this email.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 




More information about the dba-SQLServer mailing list