none
Regarding performance of TdsParser RRS feed

  • Question

  • Hi All,

    I was tuning up the performance for one of our application, which has been developed in managed vc++. We are using SQLDBCommand's ExecuteReader command
    to query table's data. (Select query which was sent to SQL DB is very simple it has a where clause and order by statement). I ran the performance monitor in sampling mode to measure the performance of the application. I got the TdsParser.Run() method in its hot path. Following is the call stack of the hot path shown in the Performance explorer. My questions is what is the logic behind the consumemetadata() and tdsparser calls. Why it is listed out as performance bottleneck. I would appreciate if anyone could answer the reason. Thanks

    Function Name Inclusive Samples Exclusive Samples Inclusive Samples % Exclusive Samples % Module Name
    System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() 253 1 6.83 0.03 System.Data.ni.dll
    System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(valuetype System.Data.CommandBehavior) 252 0 6.80 0.00 System.Data.ni.dll
    System.Data.SqlClient.SqlCommand.ExecuteReader(valuetype System.Data.CommandBehavior,string) 252 2 6.80 0.05 System.Data.ni.dll
    System.Data.SqlClient.SqlCommand.RunExecuteReader(valuetype System.Data.CommandBehavior,valuetype System.Data.SqlClient.RunBehavior,bool,string) 245 0 6.61 0.00 System.Data.ni.dll
    System.Data.SqlClient.SqlCommand.RunExecuteReader(valuetype System.Data.CommandBehavior,valuetype System.Data.SqlClient.RunBehavior,bool,string,class System.Data.Common.DbAsyncResult) 245 0 6.61 0.00 System.Data.ni.dll
    System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(valuetype System.Data.CommandBehavior,valuetype System.Data.SqlClient.RunBehavior,bool,bool) 225 3 6.07 0.08 System.Data.ni.dll
    System.Data.SqlClient.SqlCommand.FinishExecuteReader(class System.Data.SqlClient.SqlDataReader,valuetype System.Data.SqlClient.RunBehavior,string) 172 0 4.64 0.00 System.Data.ni.dll
    System.Data.SqlClient.SqlDataReader.get_MetaData() 169 0 4.56 0.00 System.Data.ni.dll
    System.Data.SqlClient.SqlDataReader.ConsumeMetaData() 169 1 4.56 0.03 System.Data.ni.dll
    System.Data.SqlClient.TdsParser.Run(valuetype System.Data.SqlClient.RunBehavior,class System.Data.SqlClient.SqlCommand,class System.Data.SqlClient.SqlDataReader,class System.Data.SqlClient.BulkCopySimpleResultSet,class System.Data.SqlClient.TdsParserStateObject) 168 4 4.53 0.11 System.Data.ni.dll

    Tuesday, August 11, 2009 6:51 PM

Answers

  • ConsumeMetaData is used to read and parse the TDS column metadata for the resultset.  Every select you send to the server results in the following set of TDS tokens:

    COLMETADATA * <Number of Columns>
    ROW <ColumnData> * <Number of Columns>
    ROW ...
    ROW ...
    DONE  (Meaning no more rows)

    So for example you have lots of small resultsets, then the overall time spend processing COLMETADATA is going to be proportionally higher.
    The COLMETADATA is required to describe the columns that follow, there is not a way to avoid it.

    In general you can return values to client either via resultsets or via output parameters.  If you have a small set of values coming back, it might be more optimal to return them using output parameters.  Each parameter has it's own metadata, one per value.  So for single row result, performance of resultset and parameters should be similar, perhaps output params will have a slight performance margin since they don't send ROW token and other parts of COLMETADATA not related to columns.  Resultsets will save you CPU once there is > 1 row since metadata is only sent once for each column.
    Monday, November 9, 2009 6:44 PM
    Moderator