Determine the Size of a SQL Result Set in Kb

Determine the size of a SQL result set in KB

In SQL*Plus:

SET AUTOTRACE ON

SELECT *
FROM emp
WHERE rownum <= 100;

        27  recursive calls
         0  db block gets
        19  consistent gets
         4  physical reads
         0  redo size
     **11451  bytes sent via SQL*Net to client**
       314  bytes received via SQL*Net from client
         8  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
       100  rows processed

To use AUTOTRACE requires the PLUSTRACE role, which is not granted by default. Find out more.

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

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

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.

How to find Memory SIZE of records returned in a SQL Query in Oracle?

Generally you can use Java's instrumentation capabilities to determine the memory consumption at runtime. For some information on that have a look here:

• In Java, what is the best way to determine the size of an object?
• https://www.baeldung.com/java-size-of-object

However, it is not always that easy to termine the actual memory consumption for various reasons, some of them being:

• Frameworks, libraries and even the JVM might create copies of your data or cache and reuse it
• Queries might return differently sized results, especially when variable length column types like VARCHAR are used. You'd have to read that data to determine the actual size of the corresponding objects
• Some objects might be referenced by multiple others so their size might be included incorrectly (e.g. if some enum constant is used that might be counted into the object size but it wouldn't actually add to the increase because it's most likely already been loaded anyway).

Additionally in most business applications you don't need to bother with determining the exact memory consumption a piece of code results in. Again there are various reasons, e.g.:

• Memory is cheap so if you run into problems it's often easier to (at least temporarily) increase available memory than (micro-) optimizing a piece of code.
• System usage and load often isn't as predictable due to changing situations (e.g. number of active users, changes in data etc.)
• The JVM often is able to efficiently use garbage collection to reclaim memory for other things.

This doesn't mean that you shouldn't think about memory usage though, e.g. do you really need all those 10k rows in memory at once? How long do you need that data and what are you doing with it?

That being said it's often helpful to roughly estimate memory consumption and in the case of a query that could return a lot of strings you should estimate the worst case, i.e. assume maximum length strings.

To do that you'll need some knowledge about what your rows will contain, e.g. whether integer numbers Integer, Long or BigInteger instances or how many columns there could be. Additionally you'll need to know at least the memory requirements for the data types, i.e. we don't take any caching, duplication, overhead for ResultSet and the like into account.

The sizes for Java objects depend on various things, e.g. which JVM you're using, whether it is a 32- or 64-bit JVM etc. Various sources state that an object's memory consumption can be calculated from the Object header (which often is stated to be 12 bytes in size) and the size of the object's fields.

Using that we'll assume Integer to have a size of 16 bytes (12b header and 4b int), Date would be 24 bytes (12b header, 8b fastTime and 4b cdate reference), String would be 12b header, 4b char[] reference, 8b other fields, 12h char[] header and 2*length bytes for the characters themselves (or 36 + 2 * length in total).

Thus let's say your 85 columns are split into 20 integers, 10 dates and 55 strings of 256 bytes max length. One row would that need at least 20 * 16 + 10 * 24 + 55 * 548 = 30700 bytes. 10k rows would thus need 307000000 bytes or roughly 300 MB (when all strings are at maximum length).

If I copy whole data and save it in file it shows only 604 KB for 10,000 records having 8 columns.

Let's break that down a little as well:

• 604 KB would be 618496 bytes (1024 being one KB)
• Devide that by 10k and you get 61.8 bytes per row on average
• Devide that by 8 and you get 7.7 bytes per column (if we don't take any row or column delimiters into account)
• Let's round it up to 8 bytes per column and let's assume your text file is Latin-1 encoded (so 1 byte per char), thus each text column has 8 chars on average which is pretty short

In an easier calculation, if we use the same asumptions as above, 604KB would mean your data would consist of roughly 604k characters, which in Java would need 1208k bytes (or about 1.2 MB) just for the character data alone. Add to that the overhead for 80k strings, i.e. 36 bytes * 80k which is about 2.8 MB more so that data would need about 4 MB in memory.

Sql. Calculating the size in KB of each row

If you already have some representative sample data, just run the following to find the average row size:

sp_spaceused 'Tablename'

Otherwise you can estimate its size without indexes with this function:

CREATE FUNCTION available_tablerowsize 
(
-- Add the parameters for the function here
@tablename char(50)
)
RETURNS int
AS
BEGIN
-- variables to track fixed and variable column sizes   
DECLARE @num_columns int
DECLARE @result int
DECLARE @num_fixed_columns int
DECLARE @fixed_data_size int
DECLARE @var_data_size int
DECLARE @num_var_columns int
DECLARE @max_var_size int
DECLARE @null_bitmap_size int
DECLARE @row_size int

-- Find the total number of columns
select @num_columns = count(*)
from syscolumns,systypes 
where syscolumns.id=object_id(@tablename) 
and syscolumns.xtype=systypes.xtype 

-- Find the size occupied by fixed length columns (Note: not possible to exist outside the 8060 bytes limit)
select @num_fixed_columns = count(*)
from syscolumns,systypes 
where syscolumns.id=object_id(@tablename) 
and syscolumns.xtype=systypes.xtype and systypes.variable=0

select @fixed_data_size = sum(syscolumns.length) 
from syscolumns,systypes 
where syscolumns.id=object_id(@tablename) 
and syscolumns.xtype=systypes.xtype and systypes.variable=0

-- Find the size occupied by variable length columns within the 8060 page size limit 

-- number of variable length columns
select @num_var_columns=count(*)
from syscolumns, systypes
where syscolumns.id=object_id(@tablename) 
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- max size of all variable length columns
select @max_var_size =max(syscolumns.length) 
from syscolumns,systypes 
where syscolumns.id=object_id(@tablename) 
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- calculate variable length storage
begin
if @num_var_columns>0
set @var_data_size=2+(@num_var_columns*2)+@max_var_size
--set @var_data_size = @num_var_columns*24
else
set @var_data_size=0
end

-- If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability.
select @null_bitmap_size = 2 + ((@num_columns+7)/8)

-- Calculate total rowsize
select @row_size = @fixed_data_size + @var_data_size + @null_bitmap_size + 4

-- Return the available bytes in the row available for expansion
select @result = 8060 - @row_size 

RETURN @result

END
GO

Oracle SQL / Java : Get the size of returned dataset from a query

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().

---

Related Topics