1

Is it possible to know the size of a dataset returned by a SQL statement in bytes

It would be nice if from the query itself I can get the size of the data. I found something like using SQL Plus, but I could not use that in my Java codes.

I know there maybe ways in doing it Java, can anyone guide me on how can I do that.

Thanks in advance.

Ianthe
  • 5,559
  • 21
  • 57
  • 74
  • 3
    AFAIK the db doesn't know the size of the query until it has finished retrieving the last result, so no, unless you can find a way to estimate the number. How do you do it with sql+? – assylias Apr 18 '12 at 07:43
  • 2
    Which size in bytes? The number of bytes sent by the server including protocol overhead? The number of bytes used by the driver? The number of bytes as pure Java objects? The number of bytes your own objects use after reading the result? –  Apr 18 '12 at 08:12
  • @assylias, I found this link http://stackoverflow.com/questions/1410881/determine-the-size-of-a-sql-result-set-in-kb where the size can be display through bytes, I am hoping there is a similar way using SQL or Java codes – Ianthe Apr 18 '12 at 09:27
  • @sprenna I thought you wanted to know the number of records in a recordset in advance. You seem to want to evaluate the size of the recordset in bytes, which is different. And as commented above, that size depends on how you define it. I don't know if you can do it in Java. – assylias Apr 18 '12 at 09:32

2 Answers2

2

Docs:

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

ResultSet is not a collection, it is merely an abstraction of the cursor that is being used to get the data in a row-wise manner.

So, what exactly do need? The amount of memory needed to store the result? The size of the data in the database? ...? Why would it be nice?

You can always do SELECT COUNT(*) FROM and using a certain average size of row estimate the result size... Instead of using the SELECT COUNT(*) you can use a more convoluted way: go to the last element ResultSet.last() and get the row number: ResultSet.getRow().

Anonymous
  • 18,162
  • 2
  • 41
  • 64
  • in our application, we show the user a subset of the total dataset they will getting like maximum of 500 rows, but the user need to get a feel of what is total size of the data set, that is why it would be nice if through a sql query we can get the size of the dataset or in java methods that we can achieve this. – Ianthe Apr 18 '12 at 09:17
  • COUNT() method give us the number of rows from the query, is there a way to convert it into bytes? – Ianthe Apr 18 '12 at 09:19
  • Is there a specific reason for needing the bytes? Most normal users are satisfied with having 500 out of 50000 (similar) rows. – Anonymous Apr 18 '12 at 10:39
  • The user wants to know how long will it take to download the whole dataset, so they would like to see the size of the data displayed. But based from your comments, i believe this maybe not a doable way. will just let them know about it. Thank you for all your comments, appreciated. ^_^ – Ianthe Apr 19 '12 at 02:05
1

I know there maybe ways in doing it Java, can anyone guide me on how can I do that.

I'm pretty sure that the answer is that there is no easy way to do this.

The JDBC abstraction hides database driver implementation details from you, and the number of bytes used to encode the results set is very driver specific.

Once the resultset is materialized into the Java heap, it is theoretically possible to find out its size. But in practice, you have to traverse every heap node in the resultset ... which is not a simple problem.

COUNT() method give us the number of rows from the query, is there a way to convert it into bytes?

In a word, no. Not even in theory.

Stephen C
  • 698,415
  • 94
  • 811
  • 1,216