Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Optimize GetSchemaTable method from FBDataReader.cs [DNET459] #451

Open
firebird-automations opened this issue Oct 19, 2012 · 2 comments
Open

Comments

@firebird-automations
Copy link

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.

   /// <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;

        #&#x2060;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\.            
        #&#x2060;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.

@firebird-automations
Copy link
Author

Commented by: José Alfredo Cañas Alatorre (alfredo.canas)

FBDataReader.cs from .NET Provider 2.7.5

@firebird-automations
Copy link
Author

Modified by: José Alfredo Cañas Alatorre (alfredo.canas)

Attachment: FbDataReader.cs [ 12243 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants