Duration of a simple object query
|
View:
New views
4 Messages
—
Rating Filter:
Alert me
|
 |
Duration of a simple object query
by Holger Rieß-2
::
Rate this Message:
My query ... Query query = em.createNamedQuery("findStockTableByItemNumber"); query.setParameter("itemnumber", itemNumber); retStockTable = (Stocktable) query.getSingleResult(); ... needs about 15 ms if there is a record in the database and about 70 ms if there is no record (profiler). The named query is ... @NamedQuery(name = "findStockTableByItemNumber", query = "SELECT st FROM Stocktable st WHERE st.itemnumber = :itemnumber AND st.dataset = 'DAT' AND st.cKatalog = '001'", hints = { @QueryHint(name = QueryHints.CACHE_USAGE, value = CacheUsage.CheckCacheThenDatabase), @QueryHint(name = QueryHints.QUERY_TYPE, value = QueryType.ReadObject) }), ... About 40% of my queries have no result - this is a great problem for the performance of my application. There is no additional code for the handling of NoResultException. _______________________________________________ eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|||||||||||
|
|
Betr.: Duration of a simple object query
by Clogs
::
Rate this Message:
Holger, In the database, do you have keys for the fields you are doing the query on? The cache usage only has a purpose when using the primary key for a record. Just another tip: precede the named query name with the name of the class it is defined in (usally the POJO of the return type), that makes it easier to find the named query back. regards, Willem Kunkels Java Developer Koopman International BV Distelweg 88 1031 HH Amsterdam The Netherlands Tel.: +31 20 494 7 893 www.koopmanint.com
My query ... Query query = em.createNamedQuery("findStockTableByItemNumber"); query.setParameter("itemnumber", itemNumber); retStockTable = (Stocktable) query.getSingleResult(); ... needs about 15 ms if there is a record in the database and about 70 ms if there is no record (profiler). The named query is ... @NamedQuery(name = "findStockTableByItemNumber", query = "SELECT st FROM Stocktable st WHERE st.itemnumber = :itemnumber AND st.dataset = 'DAT' AND st.cKatalog = '001'", hints = { @QueryHint(name = QueryHints.CACHE_USAGE, value = CacheUsage.CheckCacheThenDatabase), @QueryHint(name = QueryHints.QUERY_TYPE, value = QueryType.ReadObject) }), ... About 40% of my queries have no result - this is a great problem for the performance of my application. There is no additional code for the handling of NoResultException. _______________________________________________ eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users _______________________________________________ eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|||||||||||
|
|
Re: Duration of a simple object query
by christopher delahunt
::
Rate this Message:
Hello,
With the setting CheckCacheThenDatabase, you are telling EclipseLink to search its cache before going to the database. As you've seen, if the object is in the cache, you will get much better performance since it avoids the database hit. But when the object isn't in the cache, the 70ms will be a combination of a full cache search, a database hit and building the object from the results. The only other option that might help is to avoid the initial cache search, using DoNotCheckCache instead. This will force all non-pk queries to go the database directly, avoiding some of the delay when the objects do not exist in the cache. Because it is a trade off, its up to your usage patterns to tell if skipping the cache check when the objects are not there is worth the cost of going to the database when the objects are in the cache. Best Regards, Chris Holger Rieß wrote: > My query > ... > Query query = em.createNamedQuery("findStockTableByItemNumber"); > query.setParameter("itemnumber", itemNumber); > retStockTable = (Stocktable) query.getSingleResult(); > ... > needs about 15 ms if there is a record in the database and about 70 ms if there is no record (profiler). > > The named query is ... > @NamedQuery(name = "findStockTableByItemNumber", query = "SELECT st FROM Stocktable st WHERE st.itemnumber = :itemnumber AND st.dataset = 'DAT' AND st.cKatalog = '001'", hints = { > @QueryHint(name = QueryHints.CACHE_USAGE, value = CacheUsage.CheckCacheThenDatabase), > @QueryHint(name = QueryHints.QUERY_TYPE, value = QueryType.ReadObject) }), > ... > > About 40% of my queries have no result - this is a great problem for the performance of my application. > There is no additional code for the handling of NoResultException. > _______________________________________________ > eclipselink-users mailing list > eclipselink-users@... > https://dev.eclipse.org/mailman/listinfo/eclipselink-users > eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|||||||||||
|
|
Re: Duration of a simple object query
by James Sutherland
::
Rate this Message:
You may also consider using a query cache, if you wish to avoid database access.
See, http://www.eclipse.org/eclipselink/api/1.1.2/org/eclipse/persistence/config/QueryHints.html#QUERY_RESULTS_CACHE
James Sutherland EclipseLink, TopLink Wiki: EclipseLink, TopLink Forums: TopLink, EclipseLink Book: Java Persistence |
| Free embeddable forum powered by Nabble | Forum Help |
