How to Get the Byte Size of Resultset in an SQL Query

How to get the byte size of resultset in an SQL query?

select sum(row_size) 
from (
  select 
    char_length(column1)+
    char_length(column2)+
    char_length(column3)+
    char_length(column4) ... <-- repeat for all columns
  as row_size 
  from your_table
) as tbl1;

char_length for enum, set might not accurate, please take note

How do I get the size of a java.sql.ResultSet?

Do a SELECT COUNT(*) FROM ... query instead.

OR

int size =0;
if (rs != null) 
{
  rs.last();    // moves cursor to the last row
  size = rs.getRow(); // get row id 
}

In either of the case, you won't have to loop over the entire data.

How can I get size in bytes of a column from result set in java jdbc?

You can't derive this from the result set metadata. You can use DatabaseMetaData.getColumns(...), column CHAR_OCTET_LENGTH, but this is possibly not populated for data types other than char/varchar.

SQL Server Query Size of Results Set

You can turn on client statistics (Query menu, Include Client Statistics) which gives number of bytes returned from the server when the query is executed.

How to calculate size from my query MySQL?

If I understand your question correctly, ajreal has already provided a solution on this StackOverflow question. Quoted:

select sum(row_size) 
from (
  select 
    char_length(column1)+
    char_length(column2)+
    char_length(column3)+
    char_length(column4) ... <-- repeat for all columns
  as row_size 
  from your_table
) as tbl1;

This will give you the size of your query in bytes, divide by 1024 for kilobytes and again for megabytes.

Edit:
If you're pulling the full result set back to PHP anyway and want to know the size of it, you could calculate it in PHP using something like this:

<?php

$data = [
    [
        'item' => 'Apple',
        'type' => 'fruit',
        'in_stock' => true
    ],
    [
        'item' => 'Biscuits',
        'type' => 'confectionery',
        'in_stock' => false
    ],
    [
        'item' => 'Milk',
        'type' => 'dairy',
        'in_stock' => true
    ],
];

function get_array_size(&$array)
{
    $size = 0;

    foreach ($array as $key => $value) {
        if (is_array($value)) {
            $size += get_array_size($value);
        } else if (is_string($value)) {
            $size += strlen($value);
        } else if (is_bool($value)) {
            $size += 1;
        } /* else if ( some other type ) {

        } */
    }

    return $size;
}

echo get_array_size($data); // Outputs 43

This may or may not be acceptable to you depending on your use case. If you're looking to measure the physical bytes on the wire, this probably won't be accurate enough.

Is there a way to calculate the size of a MySQL query response?

To do this 100% in the DB, you could create a table from the query results:

 CREATE TABLE db_name.test_table SELECT a v1, b v2 FROM db_name.tbl2;

The query you want the result size of should replace SELECT a v1, b v2 FROM db_name.tbl2

Then get the size of the table

  SELECT round((data_length / 1024 / 1024), 2) "Size in MB" 
      FROM information_schema.TABLES 
    WHERE table_schema = "db_name" AND table_name = "test_table";

Then drop the test table:

 drop table db_name.test_table;

This will just give you the raw data size, but not any additional overhead like data packet headers being transfered back (if your result set is broken into many packets).

There may be even a clever way to do this with temp tables or in a stored procedure where you pass the query into it.

SQL Finding the size of query result

Actually, "Show Client Statistics" within SSMS query Editor Window will return the resultset size, Bytes Received from Server, etc

How to determine size in bytes of a result set from LINQ to SQL

Looks like you can grab the SqlConnection of your DataContext and turn on statistics.

One of the statistics is "bytes returned".

MSDN Reference Link

Get size_bytes for Query Results

to get size of result you actually need to generate result first! so, no, unfortunately - you cannot avoid getting actual result first

if you really want/need to avoid producing/paying for result - you can try to estimate result size based on original size of your data (see more here: Find out the amount of space each field takes in Google Big Query) and estimated number of rows in result

---

Related Topics