Background: Professional tool developer. SQL / DB lover.
Configuration: .Net 3.5 winforms application working with MS SQL Server 2008.
Scenario: I populate the database with information extracted from a large number of files. This amounts to about 60 M records, each of which has an associated message of arbitrary size. My original plan was in the nvarchar (max) fieldin the record for storing messages, however, after performing a test run in a subset of data, this would make the database too large (extrapolates to an unacceptable 113 GB). By running a few queries in this initial test data set (1.3 GB database), I found that there was a significant amount of message duplication, and we could use this to reduce the message data by about one sixth. Ive tried and thought of several approaches to achieve this, but none of them are satisfactory. I searched around for several days, but either: a) it does not seem like a good answer (unlikely), or b) I don’t know how to express what I need well enough (rather).
Approaches reviewed / tested:
- Mass attachment of messages to entries with the nvarchar (max) field . - It turned out that they have too much redundancy.
- Stick to this column of the message, but find a way to make the database "compress messages." “I don't know how to do this.”
- Add a message table for unique messages that have the identifier pointed to by the master record (s). - while working, in principle, the realization of uniqueness is painful and suffers from a slowdown as more messages are added.
- Perform duplicate removal on the client. - requires that all messages be received by the client for each session of the population. This does not scale, as they will need to fit into memory.
- () - ( ) -. , , . - , .
. :
- , ( ) ID int nvarchar (max).
- .
:
. (SELECT) .
II. , .
III. , , (OUTPUT).
( ) .
- ( int ) , , .
:
- , .
- Ive (UNIQUE) , nvarchar (max).
- Ive MS SQL Server 2008, .
- MERGE , ( , ), .
, - , , , " , , " ". , , .
, : ?
.