Databases
StackOverflow2013
Postsdbo
POFastest way to insert into a SQL Server table from .NET code?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFastest way to insert into a SQL Server table from .NET code?
    primarykey
    data
    text
    <p>What is the fastest way to do this:</p> <ul> <li>One table, no references that I cannot prefill (i.e. there is one reference key there, but i have all the data filled in)</li> <li>LOTS of data. We talk of hundreds of millions of rows per day, coming in dynamically through an API</li> <li>Requests must / should be processed as soon as feasible in a near real time scenario (i.e. no writing out to a file for upload one per day). 2 seconds is the normal maximal delay</li> <li>Separate machines for data / application and the SQL Server</li> </ul> <p>What I do now:</p> <ul> <li>Aggregate up to 32*1024 rows into an array, then queue it.</li> <li>Read the queue in 2-3 threads. Insert into database using SqlBulkCopy.</li> </ul> <p>I get about 60k-75k rows imported per second, which is not enough, but quite close. I would love to hit 250.000 rows.</p> <p>So far nothing is really used. I get 20% time "network I/O" blocks, have one core 80% loaded CPU side. Discs are writing out 7mb-14mb, mostly idle. Average queue length on a RAID 10 of 6 raptors is.... 0.25.</p> <p>Anyone any idea how to speed this up? Faster server (so far it is virtual, 8gb ram, 4 cores, physical disc pass through for data).</p> <hr> <p>Adding some clarifications:</p> <ul> <li>This is a 2008 R2 Enterprise SQL Server on a 2008 R2 server. machine has 4 cores, 8gb ram. All 64 bit. The 80% load average comes from this machine showing about 20% cpu load.</li> <li>The table is simple, has no primary key, only an index on a relational reference (instrument reference) and a unique (within a set of instruments, so this is not enforced) timestamp.</li> <li>The fields on the table are: timestamp, instrument reference (no enforced foreign key), data type (char 1, one of a number of characters indicating what data is posted), price (double) and volume (int). As you can see this is a VERY thin table. The data in question is tick data for financial instruments.</li> <li>The question is also about hardware etc. - mostly because i see no real bottleneck. I am inserting in multiple transactions and it gives me a benefit, but a small one. Discs, CPU are not showing significant load, network io wait is high (300ms/second, 30% at the moment), but this is on the same virtualization platform which runs JSUT the two servers and has enough cores to run all. I pretty much am open to "buy another server", but i want to identify the bottleneck first.... especially given that at the end of the day I am not grabbing what the bottleneck is. Logging is irrelevant - the bulk inserts do NOT go into the data log as data (no clustered index).</li> </ul> <p>Would vertical partitioning help, for example by a byte (tinyint) that would split the instrument universe by for example 16 tables, and me thus doing up to 16 inserts at the same time? As actually the data comes from different exchanges I could make a partition per exchange. This would be a natural split field (which is actually in instrument, but I could duplicate this data here).</p> <hr> <p>Some more clarifications: Got the speed even higher (90k), now clearly limited by network IO between machines, which could be VM switching.</p> <p>What I do now is do a connection per 32k rows, put up a temp table, insert into this with SqlBUlkdCopy, THEN use ONE sql statement to copy to main table - minimizes any lock times on the main table.</p> <p>Most waiting time is now still on network IO. Seems I run into issues where VM wise. Will move to physical hardware in the next months ;)</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBenjamin
    1. USTomTom
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFastest way to insert into a SQL Server table from .NET code?
      1. USbernd_k
      1. VTFavorite
    2. VO
      singulars
      1. POFastest way to insert into a SQL Server table from .NET code?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POFastest way to insert into a SQL Server table from .NET code?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHell, are you really sure storing that data in a relational DB is the solution you really need? Can't you store the data at first in some kind of log files, and when you are going to analyse the data, run some kind of aggregate process to it extracting only relevant information to your database?
      singulars
      1. POFastest way to insert into a SQL Server table from .NET code?
      1. USDoc Brown
    2. COYes, but i would love not to. There is a LOT of stuff going on here, and it is also a good programming example. Also, when extracing logs for active use I may have to proces 1-2 billion rows asap from compressed binary format into relational data. Just tying to get the limits here.
      singulars
      1. POFastest way to insert into a SQL Server table from .NET code?
      1. USTomTom
    3. COThis is especially true because at the end I dont really see why it is not inserting faster. Even one core is not used up, discs are not, and I have network I/O as wait conditions. I dont transfer a lot of data. This is a little thing for me to think about... ;) and fix.
      singulars
      1. POFastest way to insert into a SQL Server table from .NET code?
      1. USTomTom
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload