0

I am new to Hibernate. How would I find which query gets executed the most?

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
uday
  • 1
  • Do you have any way to profile your site? You could log the queries but something tells me you're looking to optimize the most used query. – Makoto Jun 05 '16 at 19:25
  • Curious: Why do you care? What is your goal? – Andreas Jun 05 '16 at 19:29
  • i went for the hibernate interview there they asked me this Q? and i tell them by analyzing log we can do that but they are not that much responsive....so i want to know.. – uday Jun 05 '16 at 19:49

2 Answers2

0

Enable the logging of all the generated SQL statements to the console

<!--hibernate.cfg.xml -->
<property name="show_sql">true</property>

For more details:

Display Hibernate SQL to console – show_sql , format_sql and use_sql_comments

UPDATE:

For finding most executed query: you can follow

This DMV will show the actual queries sorted by highest execution count since the last time SQL Server was restarted. These queries could be either adhoc or queries that reside within stored procedures. These are important because often times optimizing these will yield huge results in performance. In order to find specific procedures that have been executed a lot, refer to the Most Executed Procedures DMV.

SELECT TOP 50
        qs.execution_count
        ,OBJECT_NAME(objectid)
        ,query_text = SUBSTRING(
                    qt.text,
                    qs.statement_start_offset/2,         
                    (CASE WHEN qs.statement_end_offset = -1 
                        THEN len(CONVERT(nvarchar(MAX), qt.text)) * 2 
                        ELSE qs.statement_end_offset 
                        END - qs.statement_start_offset)/2) 
        ,qt.dbid
        ,dbname = db_name(qt.dbid)
        ,qt.objectid 
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC
SkyWalker
  • 28,384
  • 14
  • 74
  • 132
  • That logs *all* queries in their raw SQL form, and requires you to do some advanced processing on the logs to figure out the most frequently used one. – Makoto Jun 05 '16 at 19:24
  • @Makoto advanced processing..? if you can provide some basic detail that would be help full. – uday Jun 05 '16 at 19:26
  • @uday Please have a look http://stackoverflow.com/questions/1710476/print-query-string-in-hibernate-with-parameter-values first. Hope it will help you. – SkyWalker Jun 05 '16 at 19:29
0

You should look into profilers. I know that jprofiler can monitor hibernate queries and do exactly what you want, but it's not free. There is an evaluation version if you want to try.

Simon-Okp
  • 687
  • 7
  • 28