Attachment #176175 for bug #304923

Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

View | Details | Raw Unified | Return to bug 304923
Collapse All | Expand All




    }

    /**
     * PERF: Optimize out unnecessary joins.
     * Check for relation based on foreign keys, i.e. emp.address.id = :id, and avoid join.
     * @return null if cannot be optimized, otherwise the optimized normalized expression.
     */
    protected Expression checkForeignKeyJoinOptimization(Expression first, Expression second, ExpressionNormalizer normalizer) {
        if (first.isQueryKeyExpression()
                && (((QueryKeyExpression)first).getBaseExpression() != null)
                && ((QueryKeyExpression)first).getBaseExpression().isQueryKeyExpression()) {
            QueryKeyExpression mappingExpression = (QueryKeyExpression)((QueryKeyExpression)first).getBaseExpression();
            if ((mappingExpression.getBaseExpression() != null)
                    && mappingExpression.getBaseExpression().isObjectExpression()) {
                // Must ensure it has been normalized first.
                mappingExpression.getBaseExpression().normalize(normalizer);
                DatabaseMapping mapping = mappingExpression.getMapping();
                if ((mapping != null) && mapping.isOneToOneMapping()
                        && ((OneToOneMapping)mapping).isForeignKeyRelationship()
                        && (second.isConstantExpression() || second.isParameterExpression())) {
                    DatabaseField targetField = ((QueryKeyExpression)first).getField();
                    DatabaseField sourceField = ((OneToOneMapping)mapping).getTargetToSourceKeyFields().get(targetField);
                    if (sourceField != null) {
                        Expression optimizedExpression = this.operator.expressionFor(mappingExpression.getBaseExpression().getField(sourceField), second);
                        // Ensure the base still applies the correct conversion.
                        second.setLocalBase(first);
                        return optimizedExpression.normalize(normalizer);
                    }
                }
            }
        }
        return null;
    }
    
    /**
     * INTERNAL:
     * Check for object comparison as this requires for the expression to be replaced by the object comparison.
     */
    public Expression normalize(ExpressionNormalizer normalizer) {
        // PERF: Optimize out unnecessary joins.
        Expression optimizedExpression = checkForeignKeyJoinOptimization(this.firstChild, this.secondChild, normalizer);
        if (optimizedExpression == null) {
            optimizedExpression = checkForeignKeyJoinOptimization(this.secondChild, this.firstChild, normalizer);
        }
        if (optimizedExpression != null) {
            return optimizedExpression;
        }
        if (!isObjectComparison()) {
            return super.normalize(normalizer);
        } else {




 * @since TOPLink/Java 1.0
 */
public class IdentityMapManager implements Serializable, Cloneable {
    protected static final String MONITOR_PREFIX = "Info:CacheSize:";

    /** A table of identity maps with the key being the domain Class. */
    protected Map<Class, IdentityMap> identityMaps;

                releaseReadLock();
            }
            this.session.endOperationProfile(SessionProfiler.Caching);
            if (!this.session.isUnitOfWork() && cacheKey.getObject() == null) {
                this.session.updateProfile(MONITOR_PREFIX + domainClass.getSimpleName(), identityMap.getSize());
            }
        } else {

                releaseReadLock();
            }
            this.session.endOperationProfile(SessionProfiler.Caching);
            if (!this.session.isUnitOfWork() && cacheKey.getObject() == null) {
                this.session.updateProfile(MONITOR_PREFIX + domainClass.getSimpleName(), identityMap.getSize());
            }
        } else {

                releaseReadLock();
            }
            this.session.endOperationProfile(SessionProfiler.Caching);
            if (!this.session.isUnitOfWork() && cacheKey.getObject() == null) {
                this.session.updateProfile(MONITOR_PREFIX + domainClass.getSimpleName(), identityMap.getSize());
            }
        } else {

                releaseReadLock();
            }
            this.session.endOperationProfile(SessionProfiler.Caching);
            if (!this.session.isUnitOfWork() && cacheKey.getObject() == null) {
                this.session.updateProfile(MONITOR_PREFIX + domainClass.getSimpleName(), identityMap.getSize());
            }
        } else {

Lines 167-173 Link Here

(-)foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/tools/profiler/PerformanceMonitor.java (-1 / +1 lines)
167	return operationStartTimesByThread;	167	return operationStartTimesByThread;
168	}	168	}
169		169
170	protected Map<String, Object> getOperationTimings() {	170	public Map<String, Object> getOperationTimings() {
171	return operationTimings;	171	return operationTimings;
172	}	172	}
173		173





import org.eclipse.persistence.testing.models.jpa.advanced.LargeProject;
import org.eclipse.persistence.testing.models.jpa.advanced.Project;
import org.eclipse.persistence.testing.framework.QuerySQLTracker;
import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;

import org.eclipse.persistence.sessions.DatabaseSession;

        suite.addTest(new JUnitJPQLComplexTestSuite("testComplexLike"));
        suite.addTest(new JUnitJPQLComplexTestSuite("testComplexIn"));
        suite.addTest(new JUnitJPQLComplexTestSuite("testQueryKeys"));
        suite.addTest(new JUnitJPQLComplexTestSuite("complexOneToOneJoinOptimization"));
        
        return suite;
    }

            EntityManager em = createEntityManager();
            String errorMsg = "";
            Query query;
            for (int i = 0; i < jpqlStrings.length; i++) {
                try {
                    query = em.createQuery(jpqlStrings[i]);
                    query.getResultList();
                } catch (Exception ex) {
                    ex.printStackTrace();
                    errorMsg += '\t' + jpqlStrings[i] + " - " + ex + '\n';
                }
            }
            closeEntityManager(em);

    
    /* Test nested functions with FUNC*/
    public void testNestedFUNCs() {
        if (!getServerSession().getPlatform().isOracle()) {
            warning("The test testNestedFUNCs is supported on Oracle only");
            return;
        } else {
            String jpqlString = "select FUNC('NVL',FUNC('TO_NUMBER', FUNC('DECODE', FUNC('SUBSTRB', e.firstName,1,1),' ', NULL,FUNC('SUBSTRB',e.lastName,1,10)), null), -99) from Employee e";
            EntityManager em = createEntityManager();
            Query query;
            try {
                query = em.createQuery(jpqlString);
                query.getResultList();
            } catch (Exception ex) {
                ex.printStackTrace();
                fail("nested fucntions don't work, the error is" + ex.getMessage());
            } finally {
                closeEntityManager(em);
            }
        }
    }
    
    public void testFunctionInSelect() {
        EntityManager em = createEntityManager();
        Query query = em.createQuery("Select UPPER(e.firstName) from Employee e");

        query.getResultList();
        closeEntityManager(em);
    }
    
    /**
     * Test that id comparisons across relationships avoid the join.
     */
    public void complexOneToOneJoinOptimization()
    {
        // Count SQL.
        QuerySQLTracker counter = new QuerySQLTracker(getServerSession());
        try {
            EntityManager em = createEntityManager();
            String jpql = "SELECT e FROM Employee e where e.address.ID = 5";
            Query query = em.createQuery(jpql);
            query.getResultList();
            String sql = (String)counter.getSqlStatements().get(0);
            if (sql.indexOf("CMP3_ADDRESS") != -1) {
                fail("Join to address should have been optimized.");
            }
        } finally {
            if (counter != null) {
                counter.remove();
            }
        }
    }
}

Return to bug 304923

Lines 468-477 Link Here

(-)foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/expressions/RelationExpression.java (+40 lines)
468	}	468	}
469		469
470	/**	470	/**
		471	* PERF: Optimize out unnecessary joins.
		472	* Check for relation based on foreign keys, i.e. emp.address.id = :id, and avoid join.
		473	* @return null if cannot be optimized, otherwise the optimized normalized expression.
		474	*/
		475	protected Expression checkForeignKeyJoinOptimization(Expression first, Expression second, ExpressionNormalizer normalizer) {
		476	if (first.isQueryKeyExpression()
		477	&& (((QueryKeyExpression)first).getBaseExpression() != null)
		478	&& ((QueryKeyExpression)first).getBaseExpression().isQueryKeyExpression()) {
		479	QueryKeyExpression mappingExpression = (QueryKeyExpression)((QueryKeyExpression)first).getBaseExpression();
		480	if ((mappingExpression.getBaseExpression() != null)
		481	&& mappingExpression.getBaseExpression().isObjectExpression()) {
		482	// Must ensure it has been normalized first.
		483	mappingExpression.getBaseExpression().normalize(normalizer);
		484	DatabaseMapping mapping = mappingExpression.getMapping();
		485	if ((mapping != null) && mapping.isOneToOneMapping()
		486	&& ((OneToOneMapping)mapping).isForeignKeyRelationship()
		487	&& (second.isConstantExpression() \|\| second.isParameterExpression())) {
		488	DatabaseField targetField = ((QueryKeyExpression)first).getField();
		489	DatabaseField sourceField = ((OneToOneMapping)mapping).getTargetToSourceKeyFields().get(targetField);
		490	if (sourceField != null) {
		491	Expression optimizedExpression = this.operator.expressionFor(mappingExpression.getBaseExpression().getField(sourceField), second);
		492	// Ensure the base still applies the correct conversion.
		493	second.setLocalBase(first);
		494	return optimizedExpression.normalize(normalizer);
		495	}
		496	}
		497	}
		498	}
		499	return null;
		500	}
		501
		502	/**
471	* INTERNAL:	503	* INTERNAL:
472	* Check for object comparison as this requires for the expression to be replaced by the object comparison.	504	* Check for object comparison as this requires for the expression to be replaced by the object comparison.
473	*/	505	*/
474	public Expression normalize(ExpressionNormalizer normalizer) {	506	public Expression normalize(ExpressionNormalizer normalizer) {
		507	// PERF: Optimize out unnecessary joins.
		508	Expression optimizedExpression = checkForeignKeyJoinOptimization(this.firstChild, this.secondChild, normalizer);
		509	if (optimizedExpression == null) {
		510	optimizedExpression = checkForeignKeyJoinOptimization(this.secondChild, this.firstChild, normalizer);
		511	}
		512	if (optimizedExpression != null) {
		513	return optimizedExpression;
		514	}
475	if (!isObjectComparison()) {	515	if (!isObjectComparison()) {
476	return super.normalize(normalizer);	516	return super.normalize(normalizer);
477	} else {	517	} else {

Lines 50-56 Link Here

(-)foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/identitymaps/IdentityMapManager.java (-5 / +5 lines)
50	* @since TOPLink/Java 1.0	50	* @since TOPLink/Java 1.0
51	*/	51	*/
52	public class IdentityMapManager implements Serializable, Cloneable {	52	public class IdentityMapManager implements Serializable, Cloneable {
53	protected static final String MONITOR_PREFIX = "Info:Cache:";	53	protected static final String MONITOR_PREFIX = "Info:CacheSize:";
54		54
55	/** A table of identity maps with the key being the domain Class. */	55	/** A table of identity maps with the key being the domain Class. */
56	protected Map<Class, IdentityMap> identityMaps;	56	protected Map<Class, IdentityMap> identityMaps;
Lines 104-110 Link Here
104	releaseReadLock();	104	releaseReadLock();
105	}	105	}
106	this.session.endOperationProfile(SessionProfiler.Caching);	106	this.session.endOperationProfile(SessionProfiler.Caching);
107	if (cacheKey.getObject() == null) {	107	if (!this.session.isUnitOfWork() && cacheKey.getObject() == null) {
108	this.session.updateProfile(MONITOR_PREFIX + domainClass.getSimpleName(), identityMap.getSize());	108	this.session.updateProfile(MONITOR_PREFIX + domainClass.getSimpleName(), identityMap.getSize());
109	}	109	}
110	} else {	110	} else {
Lines 135-141 Link Here
135	releaseReadLock();	135	releaseReadLock();
136	}	136	}
137	this.session.endOperationProfile(SessionProfiler.Caching);	137	this.session.endOperationProfile(SessionProfiler.Caching);
138	if (cacheKey.getObject() == null) {	138	if (!this.session.isUnitOfWork() && cacheKey.getObject() == null) {
139	this.session.updateProfile(MONITOR_PREFIX + domainClass.getSimpleName(), identityMap.getSize());	139	this.session.updateProfile(MONITOR_PREFIX + domainClass.getSimpleName(), identityMap.getSize());
140	}	140	}
141	} else {	141	} else {
Lines 166-172 Link Here
166	releaseReadLock();	166	releaseReadLock();
167	}	167	}
168	this.session.endOperationProfile(SessionProfiler.Caching);	168	this.session.endOperationProfile(SessionProfiler.Caching);
169	if (cacheKey.getObject() == null) {	169	if (!this.session.isUnitOfWork() && cacheKey.getObject() == null) {
170	this.session.updateProfile(MONITOR_PREFIX + domainClass.getSimpleName(), identityMap.getSize());	170	this.session.updateProfile(MONITOR_PREFIX + domainClass.getSimpleName(), identityMap.getSize());
171	}	171	}
172	} else {	172	} else {
Lines 197-203 Link Here
197	releaseReadLock();	197	releaseReadLock();
198	}	198	}
199	this.session.endOperationProfile(SessionProfiler.Caching);	199	this.session.endOperationProfile(SessionProfiler.Caching);
200	if (cacheKey.getObject() == null) {	200	if (!this.session.isUnitOfWork() && cacheKey.getObject() == null) {
201	this.session.updateProfile(MONITOR_PREFIX + domainClass.getSimpleName(), identityMap.getSize());	201	this.session.updateProfile(MONITOR_PREFIX + domainClass.getSimpleName(), identityMap.getSize());
202	}	202	}
203	} else {	203	} else {

Lines 56-61 Link Here

(-)jpa/eclipselink.jpa.test/src/org/eclipse/persistence/testing/tests/jpa/jpql/JUnitJPQLComplexTestSuite.java (-13 / +39 lines)
56		56
57	import org.eclipse.persistence.testing.models.jpa.advanced.LargeProject;	57	import org.eclipse.persistence.testing.models.jpa.advanced.LargeProject;
58	import org.eclipse.persistence.testing.models.jpa.advanced.Project;	58	import org.eclipse.persistence.testing.models.jpa.advanced.Project;
		59	import org.eclipse.persistence.testing.framework.QuerySQLTracker;
59	import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;	60	import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;
60		61
61	import org.eclipse.persistence.sessions.DatabaseSession;	62	import org.eclipse.persistence.sessions.DatabaseSession;
Lines 225-230 Link Here
225	suite.addTest(new JUnitJPQLComplexTestSuite("testComplexLike"));	226	suite.addTest(new JUnitJPQLComplexTestSuite("testComplexLike"));
226	suite.addTest(new JUnitJPQLComplexTestSuite("testComplexIn"));	227	suite.addTest(new JUnitJPQLComplexTestSuite("testComplexIn"));
227	suite.addTest(new JUnitJPQLComplexTestSuite("testQueryKeys"));	228	suite.addTest(new JUnitJPQLComplexTestSuite("testQueryKeys"));
		229	suite.addTest(new JUnitJPQLComplexTestSuite("complexOneToOneJoinOptimization"));
228		230
229	return suite;	231	return suite;
230	}	232	}
Lines 3042-3054 Link Here
3042	EntityManager em = createEntityManager();	3044	EntityManager em = createEntityManager();
3043	String errorMsg = "";	3045	String errorMsg = "";
3044	Query query;	3046	Query query;
3045	for(int i=0; i < jpqlStrings.length; i++) {	3047	for (int i = 0; i < jpqlStrings.length; i++) {
3046	try{	3048	try {
3047	query = em.createQuery(jpqlStrings[i]);	3049	query = em.createQuery(jpqlStrings[i]);
3048	List result = query.getResultList();	3050	query.getResultList();
3049	}catch(Exception ex){	3051	} catch (Exception ex) {
3050	ex.printStackTrace();	3052	ex.printStackTrace();
3051	errorMsg += '\t' + jpqlStrings[i] + " - "+ex+'\n';	3053	errorMsg += '\t' + jpqlStrings[i] + " - " + ex + '\n';
3052	}	3054	}
3053	}	3055	}
3054	closeEntityManager(em);	3056	closeEntityManager(em);
Lines 3061-3084 Link Here
3061		3063
3062	/* Test nested functions with FUNC*/	3064	/* Test nested functions with FUNC*/
3063	public void testNestedFUNCs() {	3065	public void testNestedFUNCs() {
3064	if(!getServerSession().getPlatform().isOracle()) {	3066	if (!getServerSession().getPlatform().isOracle()) {
3065	warning("The test testNestedFUNCs is supported on Oracle only");	3067	warning("The test testNestedFUNCs is supported on Oracle only");
3066	return;	3068	return;
3067	}else{	3069	} else {
3068	String jpqlString = "select FUNC('NVL',FUNC('TO_NUMBER', FUNC('DECODE', FUNC('SUBSTRB', e.firstName,1,1),' ', NULL,FUNC('SUBSTRB',e.lastName,1,10)), null), -99) from Employee e";	3070	String jpqlString = "select FUNC('NVL',FUNC('TO_NUMBER', FUNC('DECODE', FUNC('SUBSTRB', e.firstName,1,1),' ', NULL,FUNC('SUBSTRB',e.lastName,1,10)), null), -99) from Employee e";
3069	EntityManager em = createEntityManager();	3071	EntityManager em = createEntityManager();
3070	Query query;	3072	Query query;
3071	try{	3073	try {
3072	query = em.createQuery(jpqlString);	3074	query = em.createQuery(jpqlString);
3073	List result = query.getResultList();	3075	query.getResultList();
3074	}catch(Exception ex){	3076	} catch (Exception ex) {
3075	ex.printStackTrace();	3077	ex.printStackTrace();
3076	fail("nested fucntions don't work, the error is" + ex.getMessage());	3078	fail("nested fucntions don't work, the error is" + ex.getMessage());
3077	}finally{	3079	} finally {
3078	closeEntityManager(em);	3080	closeEntityManager(em);
3079	}	3081	}
3080	}	3082	}
3081	}	3083	}
		3084
3082	public void testFunctionInSelect() {	3085	public void testFunctionInSelect() {
3083	EntityManager em = createEntityManager();	3086	EntityManager em = createEntityManager();
3084	Query query = em.createQuery("Select UPPER(e.firstName) from Employee e");	3087	Query query = em.createQuery("Select UPPER(e.firstName) from Employee e");
Lines 3205-3208 Link Here
3205	query.getResultList();	3208	query.getResultList();
3206	closeEntityManager(em);	3209	closeEntityManager(em);
3207	}	3210	}
		3211
		3212	/**
		3213	* Test that id comparisons across relationships avoid the join.
		3214	*/
		3215	public void complexOneToOneJoinOptimization()
		3216	{
		3217	// Count SQL.
		3218	QuerySQLTracker counter = new QuerySQLTracker(getServerSession());
		3219	try {
		3220	EntityManager em = createEntityManager();
		3221	String jpql = "SELECT e FROM Employee e where e.address.ID = 5";
		3222	Query query = em.createQuery(jpql);
		3223	query.getResultList();
		3224	String sql = (String)counter.getSqlStatements().get(0);
		3225	if (sql.indexOf("CMP3_ADDRESS") != -1) {
		3226	fail("Join to address should have been optimized.");
		3227	}
		3228	} finally {
		3229	if (counter != null) {
		3230	counter.remove();
		3231	}
		3232	}
		3233	}
3208	}	3234	}