SqlDataReader.GetBytes(1, idx, buffer, 0, ChunkSize))

[Ron]

Dear all

Apologies if this is not the correct forum for this question as I realise it
is a bit of a c#/ADO.NET one but it's specific to it's interaction with SQL
Server using Sequential Access of a row.

I'm streaming data via a datareader up to a browser and am using
SqlDataReader.GetBytes to get bytes a chunk at a time up to the browser.
Intention is not to read while blob into memory.

Its actually a very simple question.....

How big should ChunkSize be to get the most effective response?

8192?
32768?
65535?
.....?

Your help and thoughts greatly appreciated

[Jeroen Mostert]

Post by Ron
Apologies if this is not the correct forum for this question as I realise it
is a bit of a c#/ADO.NET one but it's specific to it's interaction with SQL
Server using Sequential Access of a row.
I'm streaming data via a datareader up to a browser and am using
SqlDataReader.GetBytes to get bytes a chunk at a time up to the browser.
Intention is not to read while blob into memory.
Its actually a very simple question.....
How big should ChunkSize be to get the most effective response?
8192?
32768?
65535?
.....?

Measure it.

If you don't have the time or the range of hardware and client
configurations needed, pick a size, any size, and change it if people
complain it's too slow. If your blobs are usually not that big, pick a size
in which the majority of them will fit, so you minimize roundtrips between
the APIs. Generally, when buffers are involved, gobbling up as much data as
possible in one pass is best, but this is not a law.

Maybe someone in the group has enough experience to immediately spit out a
formula for determining the appropriate size based on network latency (or
somesuch), but I doubt it.

[Michael Coles]

Search for the 'network packet size' SQL Server sp_configure option. The
default is 4096, which is pretty standard. Someone wrote up a whitepaper a
while back in which they discussed some optimizations that could be made to
this setting in conjunction with some Windows network settings, but I've
lost my bookmark to it (my guess would be that it's published up on TechNet
or MSDN somewhere).

I don't know if there is a magic formula you can plug in and run with it,
but you might be able to get some performance gain by tweaking this setting
on the server and your network packet size setting in the O/S. In the end
it will probably be a good deal of trial and error on your part.
--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

Post by Ron
Dear all
Apologies if this is not the correct forum for this question as I realise it
is a bit of a c#/ADO.NET one but it's specific to it's interaction with SQL
Server using Sequential Access of a row.
I'm streaming data via a datareader up to a browser and am using
SqlDataReader.GetBytes to get bytes a chunk at a time up to the browser.
Intention is not to read while blob into memory.
Its actually a very simple question.....
How big should ChunkSize be to get the most effective response?
8192?
32768?
65535?
.....?
Your help and thoughts greatly appreciated

[Ron]

Thanks gentlemen for your replies, both very useful :-)