What I'd suggest doing is increasing the verbosity of logging from Hibernate
to the point you can get it to log the SQL queries it is doing in check to make
sure that the *number* of queries it is doing is reasonable. One possible reason
that a large query might be slow remotely is that it is doing one query to
fetch the set of rows and then an addititional query per-row. The per-query
latency will be greater with a remote database, and even a millisecond or
two per row can add up to a lot.
(If you do find thousands of queries, it may be that some relationships on your entities
need to be made lazy)
I think you hit it on the nail. I've looked at the logs and there are a couple of hundred ones, but definitely not thousands, of 1-row queries.
Also this issue has gone away with time, without me touching anything. That must be caching in action.
I'll investigate further, add laziness and do a fresh start to see if this will improve things.
This tip is very much appreciated!
I am also having the same problem as you.
I am using the Micrsoft Sql server 2000 and am getting the connection using data source.
And i am calling a stored procedre from the client. When i run this stored procedure directly using the
query analyzer i am getting the results much faster. But its taking much time to fetch the datas when i am calling the same stored procedure via jBoss. This is very much similar to the problem that you faced. Did you find any solution to the problem. If so kindly let me know what to do.
And also people who come across such problems help me to find solution for the problem
Thanks in advance,
While I tried to dig deeper into this, a couple of days after it started happening, it stopped... without me doing anything. I was just going through the logs.
I've restarted all servers and I cannot replicate the problem. It must have been some sort of caching problem.
For my own assurance, I've consequently gone through the design of my application and removed unneeded EAGER joins. I saw that those could cause a lot of unnecessary table look-ups.
It is indeed strange for the same to happen with a stored procedure. I would suggest going through the JBoss logs at the exact location/time when you call the stored procedure.
For fetching one entity from my DB, JBoss dumped over 1MB of log data in its log file. It seems daunting to look at all of this stuff at first, but you get the hang of it.
let me know how it goes.