My Java EE application works with rather large relational DB of two tables app. of 3.5 mln rows each. The two tables are named, say, 'User' and 'Device' - one to many with FK on 'Device' table. I need to extract devices for user. The query is pretty simple:
SELECT * FROM Device d WHERE d.user_id = 'some_id'
I am using SQL Server 2012, and this query needs less than 1 ms to run. But when I use Criteria API or JPQL or Native JPA Query it needs more than 1 second!
If I store a Collection in a Users's class and fetch the devices lazily, it needs over 200 ms.
I am using Hibernate.
Maybe there is a lack of information to answer my question, if so, please, say what information should be provided by me.
So, what may be the reason of such low performance and what are the main patterns when working with large data sets (3.5 mln, as I mentioned) with JPA?
Sry for poor English.
Aucun commentaire:
Enregistrer un commentaire