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
Currently the .Net Provider from version 1.7 until the latest has been more and less performing the same way when it comes to fill DataTable Metadata when being called by the FillSchema method. The current method is underperforming specially when a table has 30 fields or even more because it executes a Metadata query for each field inside the sql statement. With new proposed change I busted my app by a factor of 1X and much less round trips by using a Dynamic Query Generator that makes a single query for a batch of fields, this means that if we have a 40 field table instead of making 40 query execute reader roundtrips it will only make 1 or 2. Here is an example of the methods to be replaced and a couple of auxiliary methods to make it happen, of course there will be room for improvement (like benchamarking memory overhead for using collections) but I think this is a step in the right direction.
Here are some metadata benchmarks I did on a localhost Firebird Database performing a FillSchema and executing a Fill from DataAdapter:
Before patch:
TABLE FIELDS PK RECORDS TIME (MS) %IMPROVEMENT
CFG_EMP 32 1 1 30 ms 0
CFG_REC 20 1 1 21 ms 0
CFG_VTA 49 1 1 40ms 0
After patch:
CFG_EMP 32 1 1 10 ms 300%
CFG_REC 20 1 1 7 ms 300%
CFG_VTA 49 1 1 13 ms 307%
I haven't documented a benchmark while requesting medatata from a client to a server on a local network, I suspect the improvement will be larger because there will be almost no network roundtrips.
As a summary here is a quick dirty example of how to make the changes on FBDataReader.cs
1.- Add new Method GetSchemaCommandTextBase().
2.- Add class RDBTableInfo.
3.- Add auxiliary method GetParamExpression.
4.- Modify GetSchemaTable.
/// <summary\>
/// Get Sql Schema Base Query for generating Dynamic Querys
/// </summary\>
/// <returns\>Base Metadata query</returns\>
private static string GetSchemaCommandTextBase\(\)
\{
string sql =
@"SELECT
fld\.rdb$computed\_blr AS computed\_blr,
fld\.rdb$computed\_source AS computed\_source,
\(SELECT COUNT\(\*\) FROM rdb$relation\_constraints rel
INNER JOIN rdb$indices idx ON rel\.rdb$index\_name = idx\.rdb$index\_name
INNER JOIN rdb$index\_segments seg ON idx\.rdb$index\_name = seg\.rdb$index\_name
WHERE rel\.rdb$constraint\_type = 'PRIMARY KEY'
AND rel\.rdb$relation\_name = rfr\.rdb$relation\_name
AND seg\.rdb$field\_name = rfr\.rdb$field\_name\) AS primary\_key,
\(SELECT COUNT\(\*\) FROM rdb$relation\_constraints rel
INNER JOIN rdb$indices idx ON rel\.rdb$index\_name = idx\.rdb$index\_name
INNER JOIN rdb$index\_segments seg ON idx\.rdb$index\_name = seg\.rdb$index\_name
WHERE rel\.rdb$constraint\_type = 'UNIQUE'
AND rel\.rdb$relation\_name = rfr\.rdb$relation\_name
AND seg\.rdb$field\_name = rfr\.rdb$field\_name\) AS unique\_key,
fld\.rdb$field\_precision AS numeric\_precision
FROM rdb$relation\_fields rfr
INNER JOIN rdb$fields fld ON rfr\.rdb$field\_source = fld\.rdb$field\_name
WHERE ";
return sql;
\}
/// <summary\>
/// Class for keeping FB Column MetaData
/// </summary\>
private sealed class RDBTableInfo
\{
public string RelationName = String\.Empty;
public string FieldName = String\.Empty;
public int Ordinal = 0;
public bool isKeyColumn = false;
public bool isUnique = false;
public bool isReadOnly = false;
public int precision = 0;
public bool isExpression = false;
public Int16 BatchID = 0;
\}
/// <summary\>
/// Allows to get complete field's param expression for batch query\. Example: \(?,?,?,?\)
/// </summary\>
/// <param name="iParams"\>Size of batch param List</param\>
/// <returns\>Param Expression</returns\>
private string GetParamExpression\(int iParams\)
\{
List<string\> lParams = new List<string\>\(iParams\);
for \(Int16 i= 0; i < iParams; i\+\+\)
lParams\.Add\("?"\);
return String\.Format\("\(\{0\}\)",String\.Join\(",",lParams\.ToArray\(\),0,iParams\)\);
\}
public override DataTable GetSchemaTable\(\)
\{
this\.CheckState\(\);
if \(this\.schemaTable \!= null\)
return this\.schemaTable;
#⁠region Variables
DataRow schemaRow = null;
int tableCount = 0;
string currentTable = string\.Empty;
this\.schemaTable = GetSchemaTableStructure\(\);
const Int16 batchLimit = 50; //Could be adjusted as needed\. Could be 50 till 90
Int16 paramCounter = 0; //Counter for the whole batch process
Int16 batchRounds = 0; //counter for each batch \(limited by batchlimit\)
Hashtable relationList = new Hashtable\(\); //HashTable to store the query's unique Field Tables Names\.
List<RDBTableInfo\> fieldList = new List<RDBTableInfo\>\(this\.fields\.Count\+1\); //List to store the whole statement Schema Field Values\.
const Int16 metadataColSize = 31; //Firebird MAX Column Size\.
Int16 batchID=0; //Batch marker\. When batchlimit reaches its limit it increases by one the value\.
StringBuilder sb = new StringBuilder\(\); //Stores dynamic generated schema query\.
#⁠endregion
// Prepare statement for schema fields information
//Asign current active schema command connection and transaccion
FbCommand schemaCmd = new FbCommand\(\);
schemaCmd\.Connection = this\.command\.Connection;
schemaCmd\.Transaction = this\.command\.Connection\.InnerConnection\.ActiveTransaction;
for \(paramCounter = 0; paramCounter < this\.FieldCount; paramCounter\+\+\)
\{
if \(batchRounds \>= batchLimit\) //Process field params until batch limit is reached\.
\{
batchID\+\+;
batchRounds = 0;
\}
RDBTableInfo rdbinfo = new RDBTableInfo\(\);
rdbinfo\.Ordinal = paramCounter;
rdbinfo\.FieldName = this\.fields\[paramCounter\]\.Name;
rdbinfo\.RelationName = this\.fields\[paramCounter\]\.Relation;
rdbinfo\.BatchID = batchID;
fieldList\.Add\(rdbinfo\);
batchRounds\+\+;
\}
//Process batch schema query
for \(Int16 i = 0; i <= batchID; i\+\+\)
\{
sb\.Length = 0;
relationList\.Clear\(\);
List<RDBTableInfo\> rdblBatch = new List<RDBTableInfo\>\(this\.fields\.Count\+1\);
//Find all RDBTableInfo elements according to batchID
rdblBatch = fieldList\.FindAll\(rdbti=\>rdbti\.BatchID==i\);
//Just add the needed tables according to the fieldnames on the current batch\.
for \(Int16 j = 0; j < rdblBatch\.Count; j\+\+\)
\{
//Keep a list of unique relation names \(tables\) from all the fieldlist\.
if \(\!relationList\.ContainsValue\(rdblBatch\[j\]\.RelationName\)\)
relationList\.Add \(relationList\.Count,rdblBatch\[j\]\.RelationName\);
\}
if \(schemaCmd\.Parameters\.Count \> 0\) //Clear previous command parameters\.
schemaCmd\.Parameters\.Clear\(\);
//Get the Base Squema query to start generating Dynamic Schema query
sb\.Append\(GetSchemaCommandTextBase\(\)\);
//Perform batch field query against table schema
//Add relation \(table names\) to schemaCmd
for \(int j = 0; j < relationList\.Count; j\+\+\)
\{
if \(j \> 0\) //More than one table in query statement
sb\.Append\(" OR "\);
List<RDBTableInfo\> tmpList = rdblBatch\.FindAll\(rdbti=\>rdbti\.RelationName\.Equals\(relationList\[j\]\)\);
sb\.AppendFormat\(" \(rfr\.rdb$field\_name in \{0\} AND rfr\.rdb$relation\_name='\{1\}'\) ", GetParamExpression\(tmpList\.Count\), relationList\[j\]\);
for \(int k = 0; k < tmpList\.Count; k\+\+\)
schemaCmd\.Parameters\.Add\("@COLUMN\_NAME", FbDbType\.Char, metadataColSize\)\.Value = tmpList\[k\]\.FieldName;
tmpList=null;
\}
//set order to schema query
sb\.Append\(" ORDER BY rfr\.rdb$relation\_name, rfr\.rdb$field\_position"\);
schemaCmd\.CommandText = sb\.ToString\(\);
schemaCmd\.Prepare\(\);
schemaTable\.BeginLoadData\(\);
//Reset Column Values
int Ordinal = 0;
int batchCount = 0;
//perform batch query
using \(FbDataReader r = schemaCmd\.ExecuteReader\(\)\)
\{
batchCount = 0;//reset batch counter
while \(r\.Read\(\)\)
\{
rdblBatch\[batchCount\]\.isReadOnly = \(IsReadOnly\(r\) \|\| IsExpression\(r\)\) ? true : false;
rdblBatch\[batchCount\]\.isKeyColumn = \(r\.GetInt32\(2\) == 1\) ? true : false;
rdblBatch\[batchCount\]\.isUnique = \(r\.GetInt32\(3\) == 1\) ? true : false;
rdblBatch\[batchCount\]\.precision = r\.IsDBNull\(4\) ? \-1 : r\.GetInt32\(4\);
rdblBatch\[batchCount\]\.isExpression = IsExpression\(r\);
batchCount\+\+;
\}
\}
for \(int j = 0; j < rdblBatch\.Count; j\+\+\)
\{
Ordinal = rdblBatch\[j\]\.Ordinal;
// Create new row for the Schema Table
schemaRow = schemaTable\.NewRow\(\);
schemaRow\["ColumnName"\] = this\.GetName\(Ordinal\);
schemaRow\["ColumnOrdinal"\] = Ordinal;
schemaRow\["ColumnSize"\] = this\.fields\[Ordinal\]\.GetSize\(\);
if \(fields\[Ordinal\]\.IsDecimal\(\)\)
\{
schemaRow\["NumericPrecision"\] = schemaRow\["ColumnSize"\];
if \(rdblBatch\[j\]\.precision \> 0\)
\{
schemaRow\["NumericPrecision"\] = rdblBatch\[j\]\.precision;
\}
schemaRow\["NumericScale"\] = this\.fields\[Ordinal\]\.NumericScale \* \(\-1\);
\}
schemaRow\["DataType"\] = this\.GetFieldType\(Ordinal\);
schemaRow\["ProviderType"\] = this\.GetProviderType\(Ordinal\);
schemaRow\["IsLong"\] = this\.fields\[Ordinal\]\.IsLong\(\);
schemaRow\["AllowDBNull"\] = this\.fields\[Ordinal\]\.AllowDBNull\(\);
schemaRow\["IsRowVersion"\] = false;
schemaRow\["IsAutoIncrement"\] = false;
schemaRow\["IsReadOnly"\] = rdblBatch\[j\]\.isReadOnly;
schemaRow\["IsKey"\] = rdblBatch\[j\]\.isKeyColumn;
schemaRow\["IsUnique"\] = rdblBatch\[j\]\.isUnique;
schemaRow\["IsAliased"\] = this\.fields\[Ordinal\]\.IsAliased\(\);
schemaRow\["IsExpression"\] = rdblBatch\[j\]\.isExpression;
schemaRow\["BaseSchemaName"\] = DBNull\.Value;
schemaRow\["BaseCatalogName"\] = DBNull\.Value;
schemaRow\["BaseTableName"\] = this\.fields\[Ordinal\]\.Relation;
schemaRow\["BaseColumnName"\] = this\.fields\[Ordinal\]\.Name;
schemaTable\.Rows\.Add\(schemaRow\);
if \(\!String\.IsNullOrEmpty\(this\.fields\[Ordinal\]\.Relation\) && currentTable \!= this\.fields\[Ordinal\]\.Relation\)
\{
tableCount\+\+;
currentTable = this\.fields\[Ordinal\]\.Relation;
\}
\}
schemaTable\.EndLoadData\(\);
rdblBatch=null;
\}//Finish Batch Round Iteration
schemaCmd\.Close\(\);
if \(tableCount \> 1\)
\{
foreach \(DataRow row in schemaTable\.Rows\)
\{
row\["IsKey"\] = false;
row\["IsUnique"\] = false;
\}
\}
//Dispose command
schemaCmd\.Dispose\(\);
relationList = null;
fieldList = null;
return schemaTable;
\}
Cheers.
The text was updated successfully, but these errors were encountered:
Submitted by: José Alfredo Cañas Alatorre (alfredo.canas)
Attachments:
FbDataReader.cs
Votes: 1
Currently the .Net Provider from version 1.7 until the latest has been more and less performing the same way when it comes to fill DataTable Metadata when being called by the FillSchema method. The current method is underperforming specially when a table has 30 fields or even more because it executes a Metadata query for each field inside the sql statement. With new proposed change I busted my app by a factor of 1X and much less round trips by using a Dynamic Query Generator that makes a single query for a batch of fields, this means that if we have a 40 field table instead of making 40 query execute reader roundtrips it will only make 1 or 2. Here is an example of the methods to be replaced and a couple of auxiliary methods to make it happen, of course there will be room for improvement (like benchamarking memory overhead for using collections) but I think this is a step in the right direction.
Here are some metadata benchmarks I did on a localhost Firebird Database performing a FillSchema and executing a Fill from DataAdapter:
Before patch:
TABLE FIELDS PK RECORDS TIME (MS) %IMPROVEMENT
CFG_EMP 32 1 1 30 ms 0
CFG_REC 20 1 1 21 ms 0
CFG_VTA 49 1 1 40ms 0
After patch:
CFG_EMP 32 1 1 10 ms 300%
CFG_REC 20 1 1 7 ms 300%
CFG_VTA 49 1 1 13 ms 307%
I haven't documented a benchmark while requesting medatata from a client to a server on a local network, I suspect the improvement will be larger because there will be almost no network roundtrips.
As a summary here is a quick dirty example of how to make the changes on FBDataReader.cs
1.- Add new Method GetSchemaCommandTextBase().
2.- Add class RDBTableInfo.
3.- Add auxiliary method GetParamExpression.
4.- Modify GetSchemaTable.
Cheers.
The text was updated successfully, but these errors were encountered: