We have a Sybase database we are querying with a very simple query.
Select * from ts_logs where time_sheet_id = 1234
The table has approximately 1.1 million records and up until 3 days ago it has been a very quick query performing in approximately a half a second.
Our application uses hibernate and the sql output shows that exact same query. As of three days ago we noticed a tremendous performance decrease where the query now takes 9 to 11 seconds to execute.
We tried running the query within the NetBeans database visualizer where we discover the same performance issue. We initially started determining this to be a database performance issue and decided to further diagnose the issue in dbvisualizer, but to our surprise dbvisualizer was executing the same exact query in less than a half a second.
We are using jtds 1.3 in every environment.
Same database in all environments.
Same query in all environments.
What could be causing the performance bottle neck in hibernate / netbeans, but not dbvisualizer? Any thoughts on the next place to look?
CREATE TABLE ts_log (id numeric identity(19) NOT NULL GENERATED ALWAYS AS IDENTITY, log_comment varchar(2500), log_entry varchar(2500) NOT NULL, time_sheet_id numeric(19) NOT NULL, PRIMARY KEY (id));
What has likely happened is that query plan has changed, probably due to an increase in the number of rows. You should run UPDATE STATISTICS, and check the query plan. If the search argument is not sufficiently selective to retrieve the desired rows, you should rethink the table/query design.