Profiling Prepared Statements

[Michael Schall <mi...@gmail.com>]

I need to be able to profile statements and quickly get back to the id that
I gave the statement within the sql map file.  We are using MSSQL 2005 and
have always used sql traces to produce a top 10 longest running stored
procedures.  We have introduced dynamic sql statements and are now trying to
use the same trace method to profile the statements as they go through. The
sql trace only provides the prepared statement text without the id assigned
to the statement in the map file.

What I would like to do is add the statement id to the sql text as a comment
before it is prepared.  Adding the following code to the
DomSqlMapBuilder.csfor select tag (other tags would need similar code)
would solve my
problems.  I'm not sure if multiline comment syntax is common across
databases, but it could be added to providers.config if not.

How are others performance monitoring your statements?   Thoughts?

                if (_configScope.UseStatementNamespaces)
                {
                    select.Id = _configScope.ApplyNamespace(select.Id);
                }
                _configScope.ErrorContext.ObjectId = select.Id;

                //ADDED CODE
                XmlNode nameComment =
xmlNode.OwnerDocument.CreateCDataSection("/*" + select.Id + "*/");
                _configScope.NodeContext.PrependChild(nameComment);

                select.Initialize( _configScope );

Re: Profiling Prepared Statements

[Michael Schall <mi...@gmail.com>]

Thanks for the quick  response...

That is an option and how I'm testing it with the trace currently.  However,
it is a manual step that the developer will have to go back to do and
remember to do in the future.  Automating it will ensure it is always there.

It should also have a better identifier for easier parsing...
/*iBATIS.id:<namespace>.<id>*/

Thoughts?

Mike

On Feb 6, 2008 3:31 PM, Gilles Bayon <ib...@gmail.com> wrote:

> Why don't you put your comment in the mappping file
>
>     <statement id="DynamicJIRA168"
>                     parameterClass="Query"
>                     resultClass="Account">
>       */* DynamicJIRA168*/*
>       select
>       Account_ID            as Id,
>       Account_FirstName    as FirstName,
>       Account_LastName    as LastName,
>       Account_Email        as EmailAddress
>       from Accounts
>       <dynamic prepend="where">
>         <isParameterPresent>
>           <isNotEmpty prepend="and" property="DataObject.Id" >
>             Account_ID = #DataObject.Id#
>           </isNotEmpty>
>           <isNotEmpty prepend="and" property="DataObject.FirstName" >
>             Account_FirstName = #DataObject.FirstName#
>           </isNotEmpty>
>           <isNotEmpty prepend="and" property="DataObject.LastName"  >
>             Account_LastName = #DataObject.LastName#
>           </isNotEmpty>
>         </isParameterPresent>
>       </dynamic>
>     </statement>
>
>  you will in the trace no ?
> 2008/02/06 22:29:45:SSS [DEBUG]
> Apache.Ibatis.DataMapper.Model.Statements.PreparedStatementFactory -
> Statement Id: [DynamicJIRA168] Prepared SQL: [*/* DynamicJIRA168*/ *
> select        Account_ID   as Id,        Account_FirstName as
> FirstName,        Account_LastName as LastName,        Account_Email  as
> EmailAddress        from Accounts where   Account_ID =  @param0]
>
> --
> Cheers,
> Gilles
>

Re: Profiling Prepared Statements

[Gilles Bayon <ib...@gmail.com>]

Why don't you put your comment in the mappping file

    <statement id="DynamicJIRA168"
                    parameterClass="Query"
                    resultClass="Account">
      */* DynamicJIRA168*/*
      select
      Account_ID            as Id,
      Account_FirstName    as FirstName,
      Account_LastName    as LastName,
      Account_Email        as EmailAddress
      from Accounts
      <dynamic prepend="where">
        <isParameterPresent>
          <isNotEmpty prepend="and" property="DataObject.Id" >
            Account_ID = #DataObject.Id#
          </isNotEmpty>
          <isNotEmpty prepend="and" property="DataObject.FirstName" >
            Account_FirstName = #DataObject.FirstName#
          </isNotEmpty>
          <isNotEmpty prepend="and" property="DataObject.LastName"  >
            Account_LastName = #DataObject.LastName#
          </isNotEmpty>
        </isParameterPresent>
      </dynamic>
    </statement>

 you will in the trace no ?
2008/02/06 22:29:45:SSS [DEBUG]
Apache.Ibatis.DataMapper.Model.Statements.PreparedStatementFactory -
Statement Id: [DynamicJIRA168] Prepared SQL: [*/* DynamicJIRA168*/ *
select        Account_ID   as Id,        Account_FirstName as
FirstName,        Account_LastName as LastName,        Account_Email  as
EmailAddress        from Accounts where   Account_ID =  @param0]

-- 
Cheers,
Gilles