0

The following query generated by hibernate takes 13+ seconds and locks the table: 

SELECT COUNT(auditentit0_.audit_id) AS col_0_0_ FROM Audit auditentit0_ WHERE 1=1;

The growing Microsoft SQL server database table contains 90+ million rows.

For Microsoft SQL server, I have found an accurate meta data way of getting the same information very quickly.

However, I would rather not write custom code for Microsoft sql server and oracle (the next database) if hibernate has a way of getting this information.

Here is an example meta data query for Microsoft sql server that is accurate and almost instant:

SELECT SUM (row_count) FROM sys.dm_db_partition_stats WHERE object_id=OBJECT_ID('huge_audit_table') AND (index_id=0 or index_id=1);

Is there a way to have hibernate issue a similar query for a table row count?

One posted answer has indicated that a view could be of use. I'm investigating this post to see if it can solve the issue:

https://vladmihalcea.com/map-jpa-entity-to-view-or-sql-query-with-hibernate/

D-Klotz
  • 1,973
  • 1
  • 15
  • 37
  • 2
    1) what exactly do you mean by "locks the table"? I have never seen a SELECT query locking anything before. 2) full table scan doesn't look right, I would expect quick index scan on th PK column. 3) why WHERE 1=1? the condition is not necessary. 4) is it Oracle or MS SQL? 5) meta data is rather an estimate, than the right value – Kousalik Jul 30 '18 at 23:05
  • 1
    Why do you need it? result of stats. will never be accurate. But practically no table row count can be accurate - at least on Oracle. Reading is always non-blocking, and therefore the row count can change during reading. – ibre5041 Jul 30 '18 at 23:06
  • @ibre5041 it does block. Take a look here for more information: https://sqlperformance.com/2014/10/t-sql-queries/bad-habits-count-the-hard-way – D-Klotz Jul 30 '18 at 23:11
  • @Kousalik Yes it does block. Take a look here: https://sqlperformance.com/2014/10/t-sql-queries/bad-habits-count-the-hard-way. The example meta data query I provide is exact. Recall that we are talking about 90+ million rows, it takes over 14 seconds with the high performance system in use and during that time, the table is locked. Nothing else can be executed. Perhaps this is a sql server specific issue. I'd like to find a hibernate solution. – D-Klotz Jul 30 '18 at 23:11
  • 1
    @D-Klotz change the tags from Oracle11g to MSSQL – Kousalik Jul 30 '18 at 23:17
  • @Kousalik isn't sql-server the microsoft tag? I wish to keep the oracle tag because that is our next target. However, more to the point, I'd like to find a hibernate solution that will work with both sql server and Oracle behind the scenes if it exists. I fear it doesn't. – D-Klotz Jul 30 '18 at 23:19
  • 2
    select count in Oracle doesn't block and meta data dictionaries are different. I don't think there is a solution like that. gl – Kousalik Jul 30 '18 at 23:23
  • @Kousalik thank you for that information. +1 to you. – D-Klotz Jul 30 '18 at 23:24
  • 2
    without looking at the implementation details, I would look at a possibility of creating your own view and hiding the RDBMS differences behind the view definition. Then you can map the view to a simple Hibernate entit. Does that make sense? Could be a stored procedure as well. – Kousalik Jul 30 '18 at 23:29
  • @Kousalik I've found references to views before. Thanks. I will dig and see if it works. – D-Klotz Jul 30 '18 at 23:37

2 Answers2

1

In hibernate you should use projections like in the link you provided in order to guarantee that it works on multiple dbms: 

protected Long countByCriteria(DetachedCriteria criteria) {
   Criteria crit = criteria.getExecutableCriteria(getSession());
   crit.setProjection(Projections.rowCount());
   return (Long)crit.uniqueResult();
}

What engine are you using in mysql? I never had a blocking problem with row count in MySql or Oracle. Maybe the following link will help you: Any way to select without causing locking in MySQL?

Also, after some quick reading i see that Sql Server does indeed block on count.

Maybe you could use a stored procedure or some other mechanism to pass the problem to the dbms.

Edit:

Projections in Hibernate are used to select the columns to fetch, the columns to group elements by, and to use built-in aggregate functions (sum, count, avg, max, min, countDistinct).

It helps you keep your application database-agnotic. Remember that hibernate supports around 30 databases.

In your case you have an specific problem with mssql as the count blocks the table prioritizing accuracy. And using the system views is really quick as you get an estimate but isn´t standard.

You could encapsulate the problem into a view or stored procedure dbms dependant. Or maybe you could try with a NOLOCK hint or READ UNCOMMITED in hibernate (in a count of an audit table it should be acceptable).

Sergio Daniel Coronel Malvarez
  • 327
  • 5
  • 17
  • I am not familiar with projections other than a quick google. What does the above do for you? Recall that the issue is within microsoft sql server when doing a select count (*). It locks the table during its (i'm assuming index) scan. How does the above projection bypass that? It is to bad that hibernate doesn't have something special for count(*) and ms sql server to avoid this. – D-Klotz Jul 31 '18 at 01:33
  • Thanks for the information. The NOLOCK would help but the query still takes 13+ seconds which is unacceptable. I will study a "view" and see if it can hide the Microsoft sql server specific code. If it works, I'll +1 and mark as answer. Thanks. – D-Klotz Jul 31 '18 at 13:01
  • The advantage of the view is that you can use other dbms specific methods to get row count estimates. In mysql you could get the estimate from the INFORMATION_SCHEMA or TABLE STATUS but you should research the error margin. – Sergio Daniel Coronel Malvarez Jul 31 '18 at 13:15
  • To solve this particular problem we stepped back and changed how the UI functions. Through a collaborative effort between UIX and UI developers we agreed that unfiltered queries will NOT ask for total counts. The initial screen load will show only a page full of data. No page 1 of 60,000 controls will exists. Only when the user enters specific criteria will the total count come into play. Those queries should be very fast. Now... it is possible for the user to still setup a query that will be just as bad as the original problem. It should be the exception versus the norm. – D-Klotz Aug 02 '18 at 21:13
  • 1
    I remember that once we had a similar problem and we what we did was display something like "Page 1 - 2 - 3 - 4 - ... - 10 of Many". Not ideal but it avoids the problem. – Sergio Daniel Coronel Malvarez Aug 03 '18 at 11:32
1

To solve this particular problem we stepped back and changed how the UI functions. Through a collaborative effort between UIX and UI developers we agreed that unfiltered queries will NOT ask for total counts. The initial screen load will show only a page full of data. No page 1 of 60,000 controls will exists. Only when the user enters specific criteria will the total count come into play. Those queries should be very fast. Now... it is possible for the user to still setup a query that will be just as bad as the original problem. It should be the exception versus the norm.

So there really is not a solid answer for the OP. If you are faced with this type of problem, if you have control of the UI and API, then it is time to rethink the solution. Think of how google handles paging from a UI perspective. The days of showing a "page 1 of (XX)" are gone IMHO.

D-Klotz
  • 1,973
  • 1
  • 15
  • 37