[dba-VB] Merge rows

<<<
Think ("Hispanic" AND (income >50K) AND (Has Kids)) AND ((HasDog OR HASCat)
OR ...)
...
The entire point is to make the process faster and faster and faster.
>>>
John,

If you have many [has Kids], [HasCat], [HasDog] kind of fields you can
consider combining them into bit array fields, for predefined range values
you can make say half-byte groups bit array fields values... by thus
minimizing the size of your table record...

--Shamil

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, September 22, 2009 7:17 PM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] Merge rows

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 

_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com


__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4447 (20090922) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru


 

__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4447 (20090922) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru