[dba-VB] Merge rows

John,

OK, someday you can make it working/flying using C# with your current DB
size "shrinked" ~8 times and your current processing time shortened X times
(to an order of magnitude? - wild guess)...

... but do not wait until you'll get fluent with C# - it's time consuming as
everybody's experience shows - and it's not needed to get the outlined above
results - just do it - how to do it seems to be clear now?...

--
Shamil

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, September 23, 2009 12:25 AM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] Merge rows

Shamil,

You are asking the wrong guy on this stuff.  I am not fluent in SQL Server,
even with all that I do.

Eventually I will be moving everything I can to c#, once I am fluent in c#.
For now I use stored 
procedures for all of the actual order processing.

The whole bit mask thing will definitely be "someday", with a big MAYBE.

John W. Colby
www.ColbyConsulting.com


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
> 
> 
_______________________________________________
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 4448 (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