2

What would be the fastest way to transmit SQL tabular data over HTTP using WCF endpoints?

Right now I am querying Entity Framework with ESQL and then serializing the DataTable result into a byte[] and just sending that over, then deserializing on the other end, which seems slow. Right now the size of the serialized data is coming to about 4000 bytes for 15000+ rows. I also experimented with OData Services but that was also rather slow.

In contrast to JSON, which I would think would be the fastest - a JSON file that contains only 1048 SQL rows is coming up as almost 200000 bytes. Is there any direct correlation between the size of the data and how fast it will transfer over HTTP? (seems intuitive, but I'm not positive).

What would be the fastest format? I am thinking of going with SQLDataReader to JSON and send the JSON over, which I would think should perform well but I'm not sure. This would be for data synchronization purposes only.

Thanks.

EDIT Did some more tracing, looks like some of my initial measurements were wrong. See below the respective methods in milliseconds.

6:42:23 PM Monday, July 11, 2011 : :Function 'Deserialize' duration. Elapsed: 481 ms

6:42:23 PM Monday, July 11, 2011 : :Function 'HttpRequest' duration. Elapsed: 4776 ms

6:42:22 PM Monday, July 11, 2011 : :DataTable Length (Bytes) = 13047247 bytes

The DataTable referenced above contained ~7000 rows, seems steep, right?

Sean Thoman
  • 7,429
  • 6
  • 56
  • 103
  • Which part do you think is "slow"? The de-serialization, or actual sending? I would say you should compress data, but frankly if you are sending 4k of data to and fro, your average network latency would dominate any reasonable benchmark. – Gleno Jul 12 '11 at 01:23
  • I timed the serialization process vs invoking the service method that returns the data, and it seems that the bottleneck is the service method, not the serialization. – Sean Thoman Jul 12 '11 at 01:24
  • Okay, then let's discuss what you consider "slow", and how you preform that test? I see you are asking if size of the data has any effect on transfer speed. Well, that's fairly obvious - unless we are talking very small data chunks, such as in your case - 4kb chunks. It may cost additional time just to open all the connections. So... what is slow in this case? About a second? – Gleno Jul 12 '11 at 01:29

1 Answers1

1

The SqlDataReader is the fastest method for getting data out of the database, yes. The trouble you're going to have then is that you're going to need to assemble your result in full before sending it out over HTTP. You cannot just stream the result as it's read from the database. JSON is a reasonably tight way of representing data, but since you'll have to repeat the column names over and over, you'd be much better off with a simple plain text tab separated value file. Set you Content-type = text/plain and scrub any tabs out of your data when you get it from the SqlDataReader. You might also want to consider gzipping your result - depending on the speed of your network vs your web server's processing power.

-- EDIT --

As Sean rightly pointed out, the plain text option isn't a WCF solution.

mattmc3
  • 17,595
  • 7
  • 83
  • 103
  • Gotcha. I think that in an AJAX-enabled wcf service, if you create a contract that serializes the results such that each row is just a string[], then no column names will clutter up the data. I would parse the results into JSON inside a while(reader.Read) loop, using a transform function for my different types. I think that would be fast? Then there's only one loop through the data going on. I can send over a seperate (small) XML file to describe the schema of the data if needed. – Sean Thoman Jul 12 '11 at 02:04
  • I'm not sure how much (if any) overhead the WCF service will add. You may want to benchmark that against just making a simple .ASPX page that serves as your 'web service' and returns a plain text representation of the table. Then, on the client app, use the bare metal `HttpWebRequest` object to hit your .ASPX page and get your data that way. – mattmc3 Jul 12 '11 at 02:18