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.
>
>
This archive was generated by hypermail 2.0.0 : Sun Dec 21 2008 - 06:22:42 EST