[dba-SQLServer] Just an FYI

And I am not expecting one, from you or anyone else on this list.  I think
that Arthur has presented a possible solution.  I think I am going to break
this down into 6 or so different result tables, each with data from a
consecutive range of fields across the big (wide) table.  I need to build a
little "query generator" anyway to write queries for me, and if I add a
field to my field lookup table to hold which vertical table the field is
held in, then the query generator can write the query to pull data from the
correct table.  That has some advantages and few disadvantages.  The storage
tables hold fewer data elements so the scan for any given field is
automatically faster.  It turns out that the data is grouped such that my
client asks for fields "close to each other", i.e. they tend to come from
the same survey.  

Thinking about it in that manner I think I can get this thing pretty fast.
Essentially the wide table gets riced and diced and "partitioned"
vertically, each vertical partition being N fields wide, but with the
advantage that the resulting "vertical" tables hold only the records that
actually contain data elements.

I am running a program I wrote that builds up this vertical table.  I just
told it to stop at field 83 which is the end of a logical group.  That will
be the end of one table.  The next table will start at field 84 and go to...
not sure yet but the same idea.  I think this will work...


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan
Harkins
Sent: Saturday, December 08, 2007 5:02 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Just an FYI

John, I wish I could help, but I just don't have the background for what
you're doing -- I barely understand your problem. No way could I come up
with a solution.

Susan H.



> 8-(
>
> This database is largish and I have to do a LOT of counts of people, 
> almost always using many different fields.  These things can take 30 
> minutes or more EACH and I currently have a request for individual 
> counts each count using a different field, about 20 of these counts.  
> I simply have to find a better way.

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com