[dba-VB] Merge rows

John,

You can use bitmask(/bitwise) operations - and you can use symbolic
constants/variables for bits to not get "seriously nervuos".

How many Boolean value fields do you have in your table?

--
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 8: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


__________ 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 4448 (20090922) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru