CODEWITHSUNDEEP
javamysqlspringhibernategoogle-app-engine
jonny bordo
jonny bordo

Reputation: 549

Java + Spring + Hibernate: "order by" slow on production

I have a Java Spring server, working with Hibernate and MySQL, and I have a view in my database, which I've mapped as an entity with hibernate.

The view is completely flat, no references to other entities. No fetches etc.

I can query the view using a simple select with no problems, both directly on the sql server using "Workbench", and using hql.
Locally I can also use "order by" clauses with no noticeable impact on the performance.

My problem is that When I move to a dev server, the "order by" clause causes the query to be extremely slow, which causes a timeout.
This happens only when the server makes the hql query.
I can still query the dev DB directly using "Workbench" with no performance problems.

The only differences between the two servers (that I can think of) is that the dev server is deployed on Google's appengine, and that the hibernate drivers are different.
On the local server I use com.mysql.jdbc.Driver, and on appengine com.mysql.jdbc.GoogleDriver.

My local DB is a MySQL database, and the dev one is on Google's Cloud SQL, which is also MySQL (pretty much).

On my Dev DB I have about 28000 rows in total in the view, and on my local DB I have about 21000, so that also shouldn't make the difference.

I can add code if you think it could help. I wasn't sure where to start.

Thank you very much in advance.

Upvotes: 0

Views: 841

Answers (2)

K139
K139

Reputation: 3669

I would enable the 'show_sql=true' to see the generated queries, and also will use the 'GoogleDriver' with local DB to eliminate any issues with driver.

Would it be possible to connect to DEV DB from your local workspace? if so, try that option using 'com.mysql.jdbc.Driver' to eliminate any issues with DEV DB.

if possible connect to DEV_DB from Mysql Manager and run the query directly on DEV_DB.

28K is very less count to get problems with 'Order by' clause.

Edit

If you have not enabled the 'connector-j' before deploying to the appEngine, it will still try to use the 'mysql' driver, and will timeout eventually. 

<?xml version="1.0" encoding="utf-8"?>
<appengine-web-app xmlns="http://appengine.google.com/ns/1.0">
  ...
  <use-google-connector-j>true</use-google-connector-j>
</appengine-web-app>

More info.

Upvotes: 1

duffymo
duffymo

Reputation: 308948

You might want to look to see if an index on the ORDER BY field exists in the DEV server but does not on PROD. That can have a profound effect on both ordering and searching.

Upvotes: 0

Related Questions