[dba-VB] Merge rows

The data storage requirements may be smaller, but the time required to perform multiple 
bitwise operation functions on every one of those 50 milllion rows is going to be a lot slower 
than using simple selection criteria on fields.

-- 
Stuart


On 23 Sep 2009 at 0:15, Shamil Salakhetdinov wrote:

> 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
>  
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
>