You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
The nanodbc 2.14 introduced optional binding to support out-of-order retrieval of unbound columns with SQLGetData (see #236). Comments to related issue #228 discussing use of SQLGetData for all columns, without binding buffers, pointed out potential of performance hit.
I've run a very basic benchmark based on 10K, 100K and 1000K rows with numeric as well as large binary and text values fetching then using both strategies: bound columns (followed by unbound in SELECT) and unbound columns.
I've used local instance of SQL Server 2019 and Visual Studio 2022 to build the benchmark program (attached below).
TL;TR: Unbinding all or offending only columns of a statement result does not impose a clear performance hit.
Results
The table shows times from three benchmark runs against the three sizes of tables for each binding/unbinding strategy:
#include<nanodbc/nanodbc.h>
#include<algorithm>
#include<cassert>
#include<chrono>
#include<iomanip>
#include<iostream>
#include<string>
#include<vector>auto connection_string = NANODBC_TEXT("Driver={ODBC Driver 17 for SQL Server};Server=mloskot;Database=nanodbc;UID=sa;PWD=Password!123;");
enumclassbinding_strategy { bound_then_unbound, unbound_offending, unbound_all };
autobenchmark_bound_followed_by_unbound(nanodbc::string const& connection_strinb, nanodbc::string const& table, binding_strategy binding)
-> std::tuple<double, std::size_t, std::size_t, std::size_t>
{
// i, f are bound// s, b, t are unbound
nanodbc::string select;
if (binding == binding_strategy::bound_then_unbound)
select = NANODBC_TEXT("SELECT i, f, s, b, t FROM ") + table;
elseselect = NANODBC_TEXT("SELECT f, s, b, i, t FROM ") + table;
nanodbc::connection c(connection_string);
nanodbc::statement s(c, select);
nanodbc::result r = s.execute();
std::vector<unsignedchar> vb;
nanodbc::string vs, vt;
double vf;
int vi;
std::size_t max_vb = 0, max_vt = 0;
std::size_t row_count = 0;
double duration = 0;
if (binding == binding_strategy::bound_then_unbound)
{
auto t0 = std::chrono::high_resolution_clock::now();
while (r.next())
{
// bound
vi = r.get<int>(0);
vf = r.get<double>(1);
// unbound
vs = r.get<nanodbc::string>(2);
vb = r.get<std::vector<unsignedchar>>(3);
vt = r.get<nanodbc::string>(4);
max_vb = std::max(max_vb, vb.size());
max_vt = std::max(max_vt, vt.size());
row_count++;
}
auto t1 = std::chrono::high_resolution_clock::now();
duration = std::chrono::duration<double, std::milli>(t1 - t0).count();
}
else// unbind offending or all
{
// Two ways to unbind column `i` to avoid "Invalid Descriptor Index" errorif (binding == binding_strategy::unbound_offending)
r.unbind(3);
elseif (binding == binding_strategy::unbound_all)
r.unbind();
elseassert(0);
auto t0 = std::chrono::high_resolution_clock::now();
while (r.next())
{
vf = r.get<double>(0);
vs = r.get<nanodbc::string>(1);
vb = r.get<std::vector<unsignedchar>>(2);
vi = r.get<int>(3);
vt = r.get<nanodbc::string>(4);
max_vb = std::max(max_vb, vb.size());
max_vt = std::max(max_vt, vt.size());
row_count++;
}
auto t1 = std::chrono::high_resolution_clock::now();
duration = std::chrono::duration<double, std::milli>(t1 - t0).count();
}
assert(row_count >= 10000);
assert(max_vb == 8000);
assert(max_vt >= 1024);
return { duration, row_count, max_vb, max_vt };
}
voidprint_header(std::wostream& os)
{
os << std::setw(21) << std::left << L"Binding,";
os << std::setw(16) << std::left << L"Fetched Rows,";
os << std::setw(11) << std::left << L"Time (ms)";
os << std::endl;
}
voidprint_result(std::wostream& os, std::wstring tag, double duration, std::size_t row_count, std::size_t max_vb, std::size_t max_vt)
{
os << std::setw(20) << std::left << tag << L',';
os << std::setw(15) << std::left << row_count << L',';
os.precision(2);
os << std::setw(10) << std::left << std::fixed << duration << L',';
#ifdef _DEBUG
os << "\tmax_vb = " << max_vb << L", max_vt = " << max_vt;
#endif
os << std::endl;
}
intmain()
{
try
{
print_header(std::wcout);
#ifdef _DEBUG
for (auto table : { L"t10000" })
#elsefor (auto table : { L"t10000", L"t100000", L"t1000000" })
#endif
{
auto [et1, rc1, sb1, st1] = benchmark_bound_followed_by_unbound(connection_string, table, binding_strategy::bound_then_unbound);
print_result(std::wcout, L"bound then unbound", et1, rc1, sb1, st1);
auto [et2, rc2, sb2, st2] = benchmark_bound_followed_by_unbound(connection_string, table, binding_strategy::unbound_offending);
print_result(std::wcout, L"unbind offending", et2, rc2, sb2, st2);
auto [et3, rc3, sb3, st3] = benchmark_bound_followed_by_unbound(connection_string, table, binding_strategy::unbound_all);
print_result(std::wcout, L"unbind all", et3, rc3, sb3, st3);
}
}
catch (std::exceptionconst& e)
{
std::cerr << e.what() << std::endl;
}
}
Test Data
DROPTABLE IF EXISTS [t10000];
DROPTABLE IF EXISTS [t100000];
DROPTABLE IF EXISTS [t1000000];
GO
CREATE TABLE [t10000] ([fid] INT IDENTITY(1, 1) PRIMARY KEY, [s] VARCHAR(60) NULL, [i] [INT] NULL, [b] [VARBINARY](MAX) NULL, [f] [float] NULL, [t] TEXTNULL);
CREATE TABLE [t100000] ([fid] INT IDENTITY(1, 1) PRIMARY KEY, [s] VARCHAR(60) NULL, [i] [INT] NULL, [b] [VARBINARY](MAX) NULL, [f] [float] NULL, [t] TEXTNULL);
CREATE TABLE [t1000000] ([fid] INT IDENTITY(1, 1) PRIMARY KEY, [s] VARCHAR(60) NULL, [i] [INT] NULL, [b] [VARBINARY](MAX) NULL, [f] [float] NULL, [t] TEXTNULL);
GO
DECLARE @count INT=1000000;
DECLARE @i INT=0;
DECLARE @f FLOAT =1.0;
DECLARE @si VARCHAR(60);
DECLARE @sf VARCHAR(60);
DECLARE @s VARCHAR(60);
DECLARE @t VARCHAR(MAX);
DECLARE @tr VARCHAR(MAX)
WHILE @i < @count
BEGINSET @i = @i +1;
SET @f = @f + (RAND() *100);
SET @si = CAST(@i ASVARCHAR(60));
SET @sf = CAST(@f ASVARCHAR(60));
SET @tr = (SELECT r FROM OpenJson((SELECT Crypt_Gen_Random(1024) r FOR json path)) WITH (r VARCHAR(max)))
SET @s ='Item number '+ RIGHT('000000'+ CAST(@i ASVARCHAR(6)), 6);
SET @t = @s +' Random: '+ @tr;
IF @i <10001BEGININSERT INTO [t10000] ([i],[f],[s],[t],[b]) VALUES (@si,@sf,@s,@t,(SELECT Crypt_Gen_Random(8000)));
INSERT INTO [t100000] ([i],[f],[s],[t],[b]) VALUES (@si,@sf,@s,@t,(SELECT Crypt_Gen_Random(8000)));
INSERT INTO [t1000000] ([i],[f],[s],[t],[b]) VALUES (@si,@sf,@s,@t,(SELECT Crypt_Gen_Random(8000)));
END
ELSE
IF @i <100001BEGININSERT INTO [t100000] ([i],[f],[s],[t],[b]) VALUES (@si,@sf,@s,@t,(SELECT Crypt_Gen_Random(8000)));
INSERT INTO [t1000000]([i],[f],[s],[t],[b]) VALUES (@si,@sf,@s,@t,(SELECT Crypt_Gen_Random(8000)));
END
ELSE
BEGININSERT INTO [t1000000]([i],[f],[s],[t],[b]) VALUES (@si,@sf,@s,@t,(SELECT Crypt_Gen_Random(8000)));
END
END;
GO
SELECT'[t10000]'AS [table], (SELECTcount(*) FROM [t10000]) AS [rows], (SELECT TOP(1) [fid] FROM [t10000] ORDER BY [fid] ASC) AS [first fid], (SELECT TOP(1) [fid] FROM [t10000] ORDER BY [fid] DESC) AS [last fid];
SELECT'[t100000]'AS [table], (SELECTcount(*) FROM [t100000]) AS [rows], (SELECT TOP(1) [fid] FROM [t100000] ORDER BY [fid] ASC) AS [first fid], (SELECT TOP(1) [fid] FROM [t100000] ORDER BY [fid] DESC) AS [last fid];
SELECT'[t1000000]'AS [table], (SELECTcount(*) FROM [t1000000]) AS [rows], (SELECT TOP(1) [fid] FROM [t1000000] ORDER BY [fid] ASC) AS [first fid], (SELECT TOP(1) [fid] FROM [t1000000] ORDER BY [fid] DESC) AS [last fid];
GO
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
The nanodbc 2.14 introduced optional binding to support out-of-order retrieval of unbound columns with
SQLGetData
(see #236). Comments to related issue #228 discussing use ofSQLGetData
for all columns, without binding buffers, pointed out potential of performance hit.I've run a very basic benchmark based on 10K, 100K and 1000K rows with numeric as well as large binary and text values fetching then using both strategies: bound columns (followed by unbound in
SELECT
) and unbound columns.I've used local instance of SQL Server 2019 and Visual Studio 2022 to build the benchmark program (attached below).
TL;TR: Unbinding all or offending only columns of a statement result does not impose a clear performance hit.
Results
The table shows times from three benchmark runs against the three sizes of tables for each binding/unbinding strategy:
nanodbc_fetch_bound_and_unbound.csv
Benchmark Program
Test Data
Beta Was this translation helpful? Give feedback.
All reactions