JPA causes tons of SQL queries
I'm currently developing (free time project) a Java Server Faces application which uses the #JPA (very common to do so) over EJBs (Enterprise Java Beans). When I turned on logging of SQL queries, I thought I didn't see it right. For each entity ("row" in database table) a distinct query has been issued. Means, when I have 1,000 rows, the same amount of queries are being issued.
This can become a nightmare when "only" 100 users are on the server. Does someone know how to reconfigure the JPA (I use #Payara ) to issue only one SQL query per table?
Surely I have used JPQL (Java Persistence Query Language) and no WHERE
was used there. So normally the whole table should be fetched. But this is not the case for linked tables (e.g. with @OneToOne
) where each such referenced entity is being fetched distinctly.
So is there a way to prevent this from happening? RAM is not a issue here. 16 GB are installed and data will not grow so much.
Roland Häder🇩🇪
in reply to Roland Häder🇩🇪 • •Maybe I need to explain more about my program. On initialization of the backing bean (means when an instance is being created and the web container is creating a wrapper class around it) I load the entire table rows from the EJB to into the controller to cache it there (see #JCache #JS107 ).
This gives great performance improvement due to not with every request (POST/GET) data is not being loaded from database but taken from cache. This is what I call asynchronous loading of data, most PHP applications however are synchronous, means with every click data is fetched from database and scripts are loaded and parsed (OpCache is maybe a bit improving here) and are being "forgotten" after the request was finished.
But with a JSF application, the "controller" (backing bean) remains instanced (and wrapped) in the web container's heap until you redeploy the application or restart the web container.
So sure I want to use that advantage of having all loaded at "all" (not at the beginning, but you can implement a ServletContextListener interface where you can "hook" on the initialization phase) times. Still this issue is there.
As you can see, not on every request these SQL queries are performed (which is very good for overall performance) but still they are really a lot. Just imagine 1,000 users on your web server (you may need a cluster then) and several 10,000 records.
Sure, when you cache them all in RAM, then you need a lot RAM. The #Payara application server uses here #Hazelcast ( hazelcast.com/ ) for having a distributed heap (cluster members contribute their heap to the cluster) and JCache (JSR107).
So what now? I still found this a bit to much. Currently I use eager fetch-mode, I could switch to lazy but that only delays the problem as foreign entities are being lazyless (on getter invocation) loaded.
Roland Häder🇩🇪
in reply to Roland Häder🇩🇪 • •Roland Häder🇩🇪
in reply to Roland Häder🇩🇪 • •Roland Häder🇩🇪
in reply to Roland Häder🇩🇪 • •I understand and know what AJAX means and I'm not targeting PHP or labeling it as bad and "look how cool this or that is". No, that is not my style.
Back to my issue, the #JPA (Java Persistence API) fetches records from database, creates an instance for each record of your entity class and wraps it into a proxy class. Then that proxy class is being compiled and most JPA implementations are caching them (the entity manager does this). This has nothing to do with the application or that it uses AJAX. My main goal is to reduce invocations of EJB business methods as this is "expensive" (a lot code need to be executed).
Just imagine, you can deploy your model classes on an other server or even data center and from your backing bean's perspective you have to change nothing at all as all is encapsulated away for you (most #JavaEE application servers use #CORBA for serializing and deserializing data).
Means on one server (or cluster, doesn't make a difference in Java code) you have your web "controllers" (they are not called controllers, backing beans are the right words for them) and on the other your model classes are running. This means one thing: distributed application load.
BTW: Amazon is running on Java Server Faces, when you hit the "order" button, EJBs are being invoked.
Okay, I'm explaining to much off-topic. With "synchronous" I mean with every request (even AJAX requests as they are basically HTTP requests. too) data is being fetched from the database. With "asynchronous" this is not the case. And I mean with this, that data is not being fetched from on each database, but maybe from a cache (that needs to stay updated, of course).
Roland Häder🇩🇪
in reply to Roland Häder🇩🇪 • •Okay.
I have found something programmatic (with annotations) for this, but it looks a bit like an overhead when you have +20 entities. What I would prefer is a 3rd fetch type
ALL
to the existingEAGER
andLAZY
. That would have to go into JPA specification, of course which made all persistence providers, like #eclipselink , #hibernate, #datanucleus and so on unified as before every provider did it on their way.This is why I like the JPA, because it is dbms-independent and provider-independent at the same time. No need to worry if your data is stored in a SQLite, MsSQL, Oracle DB or good-old MySQL or even "exotic" database systems like MongoDB.
Roland Häder🇩🇪
in reply to Roland Häder🇩🇪 • •Roland Häder🇩🇪
in reply to Roland Häder🇩🇪 • •@NamedEntityGraph(s)
were the annotations I was looking for.Roland Häder🇩🇪
in reply to Roland Häder🇩🇪 • •Roland Häder🇩🇪
in reply to Roland Häder🇩🇪 • •Roland Häder🇩🇪
in reply to Roland Häder🇩🇪 • •Hard to say as you cannot compare PHP and Java as they are fundamentally different languages. PHP is type-lazy (allows no type at all) and Java is type-safe (unless you do "unsafe casts").
To answer your question, I like both, if that satisfies your question.