Issue Details (XML | Word | Printable)

Key: DNET-459
Type: Improvement Improvement
Status: Open Open
Priority: Minor Minor
Assignee: Jiri Cincura
Reporter: José Alfredo Cañas Alatorre
Votes: 1
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

Optimize GetSchemaTable method from FBDataReader.cs

Created: 18/Oct/12 11:58 PM   Updated: 19/Oct/12 12:00 AM
Component/s: ADO.NET Provider
Affects Version/s: 2.5.0, 2.5.1, 2.5.2, 2.6, 2.6.5, 2.7, 2.7.5, 2.7.7
Fix Version/s: None

File Attachments: 1. Text File FbDataReader.cs (28 kB)

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


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
José Alfredo Cañas Alatorre added a comment - 19/Oct/12 12:00 AM
FBDataReader.cs from .NET Provider 2.7.5