Xrhrft

I have a text log file (log4net) with many SQL queries in parametrized format and with stacktraces. It also contains output from other loggers which should be ignored.

An example of one log entry:

2016-06-27 22:17:14,181 [98] DEBUG NHibernate.SQL [(null)] - select usertourna0_.id as id64_0_, tournament1_.id as id44_1_, usertourna0_.timestamp as timestamp64_0_ from some_table_name where usertourna0_.id=:p0 and  not (1=0) order by usertourna0_.timestamp desc limit :p1;:p0 = 8570 [Type: Int32 (0)]

    at NHibernate.AdoNet.Util.SqlStatementLogger.LogCommand(String message, IDbCommand command, FormatStyle style) in p:nhibernate-coresrcNHibernateAdoNetUtilSqlStatementLogger.cs:line 56

   at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) in p:nhibernate-coresrcNHibernateAdoNetAbstractBatcher.cs:line 216

   at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) in p:nhibernate-coresrcNHibernateLoaderLoader.cs:line 1200

   at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in p:nhibernate-coresrcNHibernateLoaderLoader.cs:line 419

   at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in p:nhibernate-coresrcNHibernateLoaderLoader.cs:line 251

   at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in p:nhibernate-coresrcNHibernateLoaderLoader.cs:line 1564

   at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in p:nhibernate-coresrcNHibernateLoaderLoader.cs:line 1472

   at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) in p:nhibernate-coresrcNHibernateHqlAstANTLRLoaderQueryLoader.cs:line 288

   at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) in p:nhibernate-coresrcNHibernateHqlAstANTLRQueryTranslatorImpl.cs:line 109

   at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) in p:nhibernate-coresrcNHibernateEngineQueryHQLQueryPlan.cs:line 106

   at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) in p:nhibernate-coresrcNHibernateImplSessionImpl.cs:line 654

   at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) in p:nhibernate-coresrcNHibernateImplAbstractSessionImpl.cs:line 93

   at NHibernate.Impl.ExpressionQueryImpl.List() in p:nhibernate-coresrcNHibernateImplExpressionQueryImpl.cs:line 58

   at NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) in p:nhibernate-coresrcNHibernateLinqDefaultQueryProvider.cs:line 103

   at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) in p:nhibernate-coresrcNHibernateLinqDefaultQueryProvider.cs:line 35

   at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression) in p:nhibernate-coresrcNHibernateLinqDefaultQueryProvider.cs:line 40

...

I want to analyze which queries are executed more often than others and show the list of stacktraces for them.

How can I do it?

As previously suggested, this problem is far more tractable through programming than through SQL (if at all). Not that SQL is not programming, but it's not procedural, it's declarative. The problem with that is that this means that you have to have a good idea of the data's underlying structure in order for SQL to work.

Posrgresql (the only one of the F/LOSS databases up to the task IMHO), Oracle and MS SQL Server have very sophisticated procedural programming languages of their own which might do the job, which when combined with regular expressions might get what you want done.

A further MAJOR problem is that you'll have to implement it differently for each RDBMS - more code, more errors, more debugging, more testing, more time and more money, not to mention tedious.

Same goes for parsing this file through Python or C or whatever takes your fancy. Only advantage here is that you only have to go through the pain once.

However, I would rather take a "why reinvent the wheel?" approach.

A Google of "hibernate sql logging log4j" led me here and also to here.

You're using .net, so I Googled "hibernate sql logging log4net" and arrived here.

From that page:

Running without Log4Net

If you only want NHibernate to log the queries it sends to the data
source when running unit tests you don't have to configure Log4Net at
all. It suffices to add the show_sql key to the NHibernate
configuration. If you are using a separate xml file to configure
NHibernate (e.g. the hibernate.cfg.xml) then its content might look
similar to the one below

This would appear to be a much more profitable approach, i.e. rather than spending hours programming, "hey presto", just flick a switch.

Another alternative you may wish to consider is using the logging capabilities of your database server itself. You haven't mentioned your RDBMS, but they all have logging features of some sort or another - you may wish to make use of them.

So, in reverse order (IMHO) of desirability:

• Programming on the server.




• Programming using one of the standard languages.




• Using the RDBMS's own logging facilities.




• Using your ORM's own logging facilities.

The reason that I'm marginally in favour of using the ORM over the RDBMS is the same as for my preference for a standard language over server programming: works for any server.

Hoewever, having said that, switching on a server's logging facility is noramlly as simple as modifying a .config file and bouncing.