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

Question

How can I get the size in bytes of data returned from the database when a query is executed? The reason for this is to compare the load on the database server using two different methods. We ran reports created from one data set, which loaded the entire data set for each report. Now we cache the data set and run reports from the cache. We run reports on one client, some data sets are much larger than others, and I need to somehow give a measurable metric to reduce the load on the database server.

I tried viewing DbConnection, DbDataReader and DbCommand for any existing functions, but could not find it. It would be great to measure the data throughput of a given connection or reader, if possible, but any solutions are acceptable. Perhaps there is a database proxy that I can use to measure it?

The database is Oracle 10g.

Answer 1

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

Another advantage of measuring this method is that it will find differences (if any) in the size of the requested queries. For example, if one method caused individual records to be read from the server in separate requests, and another method called a “batch” record of records in one request, you could see these differences. Both methods in this case can show that the general data at the DbDataReader level is the same, but the first method will lead to a significant increase in network traffic.

Wireshark shows a lot of statistics that may be useful for this. It may contain the total number of packets, average packet size, total size, average byte per second, etc.

Answer 2

Have you tried to measure it on the database side? Oracle 10g comes with a whole package of performance tuning and monitoring tools .

Answer 3

you cannot get the size of the result set, except that it is cyclical. if you cannot afford it from a preform point of view, you can return the size of the result set from the server in the first row of the result set. or return two sets of results.

Answer 4

I do not think there is an easy way to do this. To get metadata, you really need data that will be presented as a DataSet or DataTable, and not through DbDataReader or DataRow, so that all keys, columns and metadata are represented as a whole.

I will do this by creating binary formatting and storage stream and serializing the DataSet / DataTable into memystream as binary data. Then you can request the length from the stream. This should tell you fairly accurately about the amount of data published on your subject.

This is probably a performance killer, so you can only use it in a debugging environment to understand the size of your data.