cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: query_governor_cost_limit setting not working with cayenne?
Date Sun, 21 Dec 2008 11:22:08 GMT
Ok, I confirmed that "SET query_governor_cost_limit..." only has  
effect when run via JDBC Statement, and has zero effect when run via  
PreparedStatement. So we figured why it won't work via SQLTemplate  
that is executed via PreparedStatement. To modify JDBC behavior you  
will need a custom query, overriding 'createSQLAction' method to run  
cost limit statement. Here is a working example:

EJBQLQuery mainQuery = new EJBQLQuery(
   "SELECT a FROM Artist a WHERE a.artistName "
   + "IN (SELECT x.artistName FROM Artist x WHERE x.artistName LIKE 'XX 
%')");

QueryChain chain = new QueryChain();
chain.addQuery(new CostLimitQuery(mainQuery, 3));
chain.addQuery(mainQuery);

List<Artist> result = context.performQuery(chain);

Here is how CostLimitQuery light look like:

package test;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;

import org.apache.cayenne.CayenneRuntimeException;
import org.apache.cayenne.access.OperationObserver;
import org.apache.cayenne.access.QueryLogger;
import org.apache.cayenne.map.DataMap;
import org.apache.cayenne.map.EntityResolver;
import org.apache.cayenne.query.Query;
import org.apache.cayenne.query.QueryMetadata;
import org.apache.cayenne.query.QueryRouter;
import org.apache.cayenne.query.SQLAction;
import org.apache.cayenne.query.SQLActionVisitor;

public class CostLimitQuery implements Query {

	private static final String SET_COST_STATEMENT = "SET  
query_governor_cost_limit ";

	private String queryString;
	private Query delegate;

	public CostLimitQuery(Query delegate, int limit) {
		this.queryString = SET_COST_STATEMENT + limit;
		this.delegate = delegate;
	}

	public String getName() {
		return delegate.getName();
	}

	public QueryMetadata getMetaData(EntityResolver resolver) {
		return delegate.getMetaData(resolver);
	}

	public void route(QueryRouter router, EntityResolver resolver,
			Query substitutedQuery) {

		DataMap map = getMetaData(resolver).getDataMap();

		if (map == null) {
			throw new CayenneRuntimeException(
					"No DataMap found, can't route query " + this);
		}

		router.route(router.engineForDataMap(map), this, substitutedQuery);
	}

	public SQLAction createSQLAction(SQLActionVisitor visitor) {
		return new SQLAction() {
			public void performAction(Connection connection,
					OperationObserver observer) throws SQLException, Exception {

				QueryLogger.logQuery(queryString, Collections.emptyList());

				Statement statement = connection.createStatement();

				try {
					statement.execute(queryString);
				} finally {
					statement.close();
				}
			}
		};
	}
}





On Dec 20, 2008, at 5:00 PM, KathyS wrote:

>
> Prepared vs plain statement may change execution plan for the  
> governor query
> itself, but as long as it runs, and I verified it runs by watching sql
> server trace, it should "work". I was more concerned seeing  
> sp_prepare /
> sp_unprepare surrounding each query, even inside of the chain.
>
>
> Andrus Adamchik wrote:
>>
>>
>> On Dec 20, 2008, at 4:48 PM, KathyS wrote:
>>
>>> JDBC code is straightforward:
>>> Statement   stmnt = con.createStatement();
>>> stmnt.execute("SET query_governor_cost_limit 2");
>>
>> The only difference with Cayenne internal execution path would be  
>> that
>> Cayenne will use PreparedStatement here. Anyways, I need to try it I
>> guess...
>>
>> Andrus
>>
>>
>>
>
> -- 
> View this message in context: http://www.nabble.com/query_governor_cost_limit-setting-not-working-with-cayenne--tp21099002p21105729.html
> Sent from the Cayenne - User mailing list archive at Nabble.com.
>
>


Mime
View raw message