[dba-VB] Merge rows

jwcolby jwcolby at colbyconsulting.com
Tue Sep 22 10:17:17 CDT 2009


Charlotte,

 >Why would you get a better result from one ugly record with all the data in it than from querying 
for a master record with child records

There is nothing inherently pretty or ugly from having all answers in a single record.

This is nothing more than a bunch of polls.  From a data processing perspective would you prefer 
that the person answer all 640 questions on a single form (record) or on 640 records.  Remember this 
is 50 MILLION "people" we are talking about here (minus the duplicates of course).

The client wants to be able to ask questions about any questions in the table that a given person 
has answered.  He often gives a ("FieldA = 1" AND "FieldB = T" AND "FieldC = M") AND (FieldC = Y" OR 
"FieldD = Y" OR "FieldE = Y") etc etc.

Think ("Hispanic" AND (income >50K) AND (Has Kids)) AND ((HasDog OR HASCat) OR ...)

It is simply easier, faster and more reliable if the data is all in a single record rather than 
spread across multiple records.  Again - 50 MILLION records, 640 fields.  Fancy tricks like 
subtables and joins bog down the queries.  Answers in separate records cause ANDS to fall out. 
Things like that.

Heck, even not having an index on a single field in the WHERE can cause the result to drop from 2 
minutes to 20 minutes.  This is a business and making the process faster means I can charge the same 
amount for less of my time.  The entire point is to make the process faster and faster and faster.

John W. Colby
www.ColbyConsulting.com


Charlotte Foust wrote:
> John,
> 
> I'm confused.  Why would you get a better result from one ugly record
> with all the data in it than from querying for a master record with
> child records, perhaps in a union query, that contained both answer 1
> and answer 2?  Is the client wanting to query across polls, so that if
> the respondent answers answer1 in one poll and answer2 in another, they
> want to see that client?  It sounds like totally meaningless information
> (not an uncommon request, in my experience).
> 
> Charlotte Foust 




More information about the dba-VB mailing list