0

What I want to do:

Write a lot of small (average 500-1000 bytes) blob-like objects that I get from a queue to a SQL Server database, to a table that has a bigint primary key, and the data as varbinary column.

Currently I do it this way:

  1. get some object from a concurrent queue
  2. create a new memory stream, and a new binary writer
  3. serialize the object into the memory stream with the Write... command from the writer
  4. call stream.ToArray() and set the result as the parameter value of the SQL command
  5. execute the command

This creates a lot of small byte arrays which will become garbage immediately after the write. I would like to avoid this, and only create one byte array, always write & read from that array.

But it seems like the SqlCommand objects internally copy the buffer if you pass a byte array.

Also the problem of reusing the same byte array over and over is that I cant tell the SqlCommand how many bytes it should write to the table (each object will have a slightly different length)

Any idea how to do this without writing my own SQL implementation ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1803928
  • 79
  • 5
  • Honestly if a network connection is involved, a minuscule amount of time spent on potential GC is the least of your concerns. [Premature Optimization](http://wiki.c2.com/?PrematureOptimization) –  Feb 08 '17 at 16:30
  • not true in my case because, because I dont care how long it takes for the data to get to the database, the whole write is async because this is a high availability system that should have minimum delays. I dont care if the write takes 100ms or 200ms but I care about the possible GC interruption (well, it should be very fast, but it's better to be a little bit slower and have no GC impact) – user1803928 Feb 08 '17 at 17:12
  • Assuming you are on .NET 4+ then you'll find the GC to be pretty efficient and quick compared to the ball and chain prior to .NET 4. .NET 4 running on Windows workstations performs GC on a background thread and servers perform background GC on .NET 4.5 so there's little chance of the current thread being frozen during a collect. If your system is truely high avail, it should have a sufficient spec. Unless you've got actual results to show from an instrumentation session showing significant delays in your code caused by excessive collects I think the concern is perhaps a little unwarranted. –  Feb 08 '17 at 17:29
  • If you're that worried about GC, be aware that using a simple `foreach` is enough to cause a temporary iterator object to be created that ultimately will require it to be collected. The effect is magnified based on the frequency of use. Use it enough and that too is grounds for a stutter. Ask any XNA or Unity3D game developer who sadly are stuck with pre-.NET 4 tech. That's why they use `for` instead of `foreach`. But on .NET 4.5 on Windows Server am I concerned? No –  Feb 08 '17 at 17:36

0 Answers0