Optimize GetSchemaTable method from FBDataReader.cs
---------------------------------------------------
Key: DNET-459
URL: http://tracker.firebirdsql.org/browse/DNET-459
Project: .NET Data provider
Issue Type: Improvement
Components: ADO.NET Provider
Affects Versions: 2.7.7, 2.7.5, 2.7, 2.6.5, 2.6, 2.5.2, 2.5.1, 2.5.0
Environment: DB:Firebird 2.5.3 X64 OS:Windows 7 Library:Firebird .Net
Provider 2.7.5 .NET Framework 2.0-3.5 PC: ASUS G74S LAPTOP INTEL I7 2.7 Ghz 8G
RAM HD 1TB 7200 RPM
Reporter: José Alfredo Cañas Alatorre
Assignee: Jiri Cincura
Priority: Minor
Attachments: FbDataReader.cs
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_sfd2d_oct
_______________________________________________
Firebird-net-provider mailing list
Firebird-net-provider@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
