[dba-VB] SPAM-LOW: Re: Merge rows

jwcolby jwcolby at colbyconsulting.com
Tue Sep 22 12:03:50 CDT 2009


Yes, there are fields with real data in them.  Mostly they are just Y or nothing.

John W. Colby
www.ColbyConsulting.com


Jack and Pat wrote:
> My initial thought was bit fields also,  until I saw boat_length and income
> identified by ranges.
> 
> -----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 12:31 PM
> To: Discussion concerning Visual Basic and related programming issues.
> Subject: Re: [dba-VB] Merge rows
> 
> This is an idea worthy of consideration.  The problem is translating the
> client's original field 
> name to a bit in the bit field when he sends me an order.
> 
> The client has a spreadsheet of these fields and sends me orders with the
> field names (or more often 
> a "record number" in the spreadsheet).  I look down through the spreadsheet
> to find the record 
> number, translate that to the field name, then drop that field name into the
> where clause of a query.
> 
> It would be fascinating to have an already translated bit map of these
> fields, and able to do these 
> kinds of where clauses.  It would end up being Where (Bit37 AND Bit44 AND
> Bit69) OR (BIT190 OR 
> Bit191) etc.
> 
> Not exactly readable, but I have to assume that it would be much faster.  It
> would make me seriously 
> nervous though that I actually got the right bits.  As it is now it is
> "plain English", you can read 
> the field names from the SQL statement.  I would definitely have to have a
> well tested tool that 
> performed the bit lookup and wrote the where().
> 
> Can you index bits?  If not you would turn the actual query into a ton of
> table scans, though as you 
> mention the size of the table would shrink dramatically.  It might actually
> be possible to get the 
> entire table into memory if each field (that contained just 'Y' values) was
> a single bit.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Shamil Salakhetdinov wrote:
>> <<<
>> 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
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> 



More information about the dba-VB mailing list