C# Getting the size of the data returned from and SQL query

Question

How can I get the size in bytes of the data returned from the database when executing a query? The reason for this is to compare load on the database server using two different techniques. We were running reports built from the same dataset which would load the entire dataset for every report. Now we are caching the dataset and running reports from the cache. We run reports per client, some datasets are significantly bigger than others, and I need some way to give a measurable metric for the database server load reduction.

I have tried looking through DbConnection, DbDataReader and DbCommand for any existing functionality, but was unable to find it. It would be great to measure the data throughput of a given connection or reader if possible, but any solutions are acceptable. Is there perhaps a database server proxy I can use to measure it?

The database is Oracle 10g.

Answer 1

you can't get size of result set except of looping all over it. if you can't afford it from preformace point of view you can return size of result set from server in first row of result set. or return two result sets.

Answer 2

One possibility would be to simply use a network sniffer. Wireshark is amazingly good. It would be tricky to measure by connection (when using multiple connections on a given machine), but you could use it to measure all the traffic to and from a client machine. One benefit of this is that it would also measure outgoing requests, which should be small in your situation (report generation) but are still a part of the overall load.

Another benefit of measuring it this way is that it would find differences (if there are any) in the size of requests being made. For example, if one method caused individual records to be read from the server in separate requests and the other method caused a "batch" of records to be read in one request, then you would be able to see those differences. Both methods in this case might show that the total data at the DbDataReader level is the same, but the first method would result in significantly more network traffic.

Wireshark shows a lot of statistics that could be useful for this. It can give total number of packets, average size of packets, total size, average bytes per second, etc.

Answer 3

Have you tried measuring it on the database side? Oracle 10g appears have a whole stack of performance tuning and monitoring tools.

Answer 4

I don't think there is any easy way of doing this. In order to get the metadata, you really need the data to be represented as DataSet or DataTable not through the DbDataReader or DataRow so that all keys, columns and metadata are represented as a whole.

What I will do is to create a binary formatter and memorystream and serialize the DataSet/DataTable into the memorystream as binary data. You should then be able to ask for the length from the stream. This should quite accurately tells you about the size of the data as posted on your question

This is probably a performance killer so you may only want to use it in debugging environment only to understand about the size of your data