Estimate the size of a dataset returned from SQL

Question

Estimate the size of a dataset returned from SQL

c# dapper seq sql-server

Question

We've got a system that seems to be consuming a lot of data, it uses Dapper for database queries and Seq for logging. I was wondering if other than with SQL Profiler whether there was a way to add logging to Dapper to log the size of the dataset returned in MB -so we can flag large datasets for review?

This question has been asked a while ago but I was wondering whether there was now a way of doing it without wireshark and ideally without iterating over the rows/cells?

Tim

Popular Answer

You can estimate the size required by a row summing up each column type size, then multiply by the number of rows. It should be accurate if you don't have TEXT / VARCHAR in your query:

int rowSize = 0;
foreach(DataColumn dc in Dataset1.Tables[0].Columns) {
    rowSize += sizeof(dc.DataType);
}
int dataSize = rowSize * Dataset1.Tables[0].Rows.Count;

In case you need a more accurate figure, sum up the size of each individual value using Marshal.SizeOf:

int dataSize = 0;
foreach(DataRow dr in Dataset1.Tables[0].Rows) 
{
    int rowSize = 0;
    for (int i = 0; i < Dataset1.Tables[0].Columns.Count; i++)
    {
        rowSize += System.Runtime.InteropServices.Marshal.SizeOf(dr[i]);
    }
    dataSize += rowSize;
}

Ideas for performance gain if high accuracy is not a concern:

Compute the size of just a sample. Let's say, instead of iterating through all rows, pick 1 in every 100, then multiply your result by 100 in the end.
Use [Marshal.SizeOf]((https://msdn.microsoft.com/en-us/library/y3ybkfb3.aspx) to compute the size of each DataRow dr instead of iterating through all it's values. It will give you a higher number since a DataRow object has additional properties, but that's something you can tweak by subtracting the size of an empty DataRow.
Know the average size of a single row beforehand, by it's columns, and just multiply by the number of rows.

Paulo Amaral

View more on Stack Overflow

codingbadger · Accepted Answer · 8/8/2016 11:47:10 AM

I would configure Provider Statistics for SQL Server for the connection in the base repository class. You can add a config setting to switch it on and easily save this information off to a log file or where ever you want.

Example code from MSDN

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace CS_Stats_Console_GetValue
{
  class Program
  {
    static void Main(string[] args)
    {
      string connectionString = GetConnectionString();

      using (SqlConnection awConnection = 
        new SqlConnection(connectionString))
      {
        // StatisticsEnabled is False by default.
        // It must be set to True to start the 
        // statistic collection process.
        awConnection.StatisticsEnabled = true;

        string productSQL = "SELECT * FROM Production.Product";
        SqlDataAdapter productAdapter = 
          new SqlDataAdapter(productSQL, awConnection);

        DataSet awDataSet = new DataSet();

        awConnection.Open();

        productAdapter.Fill(awDataSet, "ProductTable");
        // Retrieve the current statistics as
        // a collection of values at this point
        // and time.
        IDictionary currentStatistics =
          awConnection.RetrieveStatistics();

        Console.WriteLine("Total Counters: " +
          currentStatistics.Count.ToString());
        Console.WriteLine();

        // Retrieve a few individual values
        // related to the previous command.
        long bytesReceived =
            (long) currentStatistics["BytesReceived"];
        long bytesSent =
            (long) currentStatistics["BytesSent"];
        long selectCount =
            (long) currentStatistics["SelectCount"];
        long selectRows =
            (long) currentStatistics["SelectRows"];

        Console.WriteLine("BytesReceived: " +
            bytesReceived.ToString());
        Console.WriteLine("BytesSent: " +
            bytesSent.ToString());
        Console.WriteLine("SelectCount: " +
            selectCount.ToString());
        Console.WriteLine("SelectRows: " +
            selectRows.ToString());

        Console.WriteLine();
        Console.WriteLine("Press any key to continue");
        Console.ReadLine();
      }

    }
    private static string GetConnectionString()
    {
      // To avoid storing the connection string in your code,
      // you can retrive it from a configuration file.
      return "Data Source=localhost;Integrated Security=SSPI;" + 
        "Initial Catalog=AdventureWorks";
    }
  }
}

Estimate the size of a dataset returned from SQL

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator