[dba-VB] Merge rows

OK, so you can "pack" 400 boolean fields into 50 bytes long binary field -
that will be BINARY(50) in T-SQL.
Although I'm not currently sure you can implement bitwise logical operations
solely using T-SQL: but I suppose you can get subset of data using
non-Boolean where clause, and then scan it in C#/VB.NET and filter using
bitwise C# operations/functions over bit arrays.

Something like that should work I suppose.

Please correct me if I'm wrong.

--
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 11:21 PM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] Merge rows

Hundreds.  Not sure really.  Probably somewhere around 400-500.

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> 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
> 
> _______________________________________________

 

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

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru