I keep encountering situations where RDBMS users think that one second for query execution is anything near fast. Most recently, in this Stack Overflow question:
Hibernate SQL In clause making CPU usage to 100%
The poster’s original question was why a similar query executes in one second when executed in SQL Server Management Studio whereas the (seemingly) same query executes in 60 seconds when executed from Hibernate. The query looks similar to this:
select Student_Id
from Student_Table
where Roll_No in ('A101','A102','A103',.....'A250');There might be many reasons for this difference. Most likely, there’s also a Hibernate N+1 issue hidden somewhere. But the most striking message here is:
Please do not believe that 1 second is fast.
Databases are incredibly fast and simple queries like the one above should execute in virtually no time, even on mediocre servers. Even on your laptop! Markus Winand has made it a point to tell you that in 80% of all performance issues, all you have to do is to add that missing index. And that’s the case here as well!
The poster’s original table contains only two indexes:
CREATE TABLE student_table (
Student_Id BIGINT NOT NULL IDENTITY
, Class_Id BIGINT NOT NULL
, Student_First_Name VARCHAR(100) NOT NULL
, Student_Last_Name VARCHAR(100)
, Roll_No VARCHAR(100) NOT NULL
, PRIMARY KEY (Student_Id)
, CONSTRAINT UK_StudentUnique_1
UNIQUE (Class_Id, Roll_No)
);There is an index to implement the PRIMARY KEY, and there’s another index for the UNIQUE constraint, but both indexes aren’t really very useful, as the query predicate filters on Roll_No, which is only the second column of the UNIQUE constraint. When executing the above query on roughly 8 million rows, I’m getting an index scan on the UNIQUE index and the query runs in three seconds:
This “Index Scan” operation is not good at all. I’m actually running through all of the index to find all the applicable Roll_No values in each index leaf node. This is explained well in Use The Index Luke’s page about concatenated indexes
The solution
But the good news is, SQL Server Management Studio gives you immediate tuning advice. Just right click on the execution plan and choose “Missing Index Details…” to get the following advice:
/*
Missing Index Details from SQLQuery1.sql -
LUKAS-ENVY\SQLEXPRESS.test
The Query Processor estimates that implementing the
following index could improve the query cost
by 87.5035%.
*/
/*
USE [test]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index>]
ON [dbo].[student_table] ([Roll_No])
INCLUDE ([Student_Id])
GO
*/This doesn’t necessarily mean that the above index is the optimal choice for all your queries, but the fact that you’re querying using a predicate on Roll_No should be a strong-enough indicator that you should have an index on at least this Roll_No column. The simplest possible index here is simply:
CREATE INDEX i_student_roll_no ON student_table (Roll_No);
With that index in place, we’ll now get an “Index Seek” operation, which runs instantly:
Covering index
In this particular case, a “covering index” as suggested by Vlad Mihalcea in his answer might be appropriate. For instance:
CREATE INDEX i_student_roll_no ON student_table (Roll_No, Student_Id);
The advantage of a covering index is that all the information needed for the query execution is already contained in the index. This is true in this particular case, but it can also be dangerous as:
- The covering index needs more space, and if the table is already large, space might become an issue
- The covering index only adds value as long as the query doesn’t also use additional columns (e.g. for projections, calculations, additional filtering, sorting, etc.). This is probably not the case in this simple example, which might change quickly in the near future
Thus, a covering index shouldn’t be your default choice in such cases. Better be conservative and add only those columns in the index, that add immediate value for filtering.
Conclusion
I’d like to repeat this time and again:
Do NOT think that one second is fast
In fact:
Do NOT think that anything beyond 2-3ms is fast!
Unless you’re doing heavy reporting or batch processing, where processing time obviously might take a bit longer, simple queries like these should never be anything slower than instant. And most of the time, you can achieve this speed by adding an index.
On a side-note
The poster of the aforementioned question obviously has other issues as well. None of the above explain the execution speed difference between Hibernate and “plain SQL”. But again, the message here is that if your “plain SQL” already takes more than one second, you have a very low-hanging fruit to fix.
| Reference: | Do Not Think That One Second is Fast for Query Execution from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
