You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by KathyS <sc...@yahoo.com> on 2008/12/20 00:08:21 UTC
query_governor_cost_limit setting not working with cayenne?
Hello,
I'm retrofitting an existing project to use Cayenne back end. We use SQL
Server, and we need to be able in certain cases to prevent long running
queries from starting. SQL Server does that by using
query_governor_cost_limit setting and comparing it with time estimation
prior to running the query. And it works with plain JDBC - executing in the
same connection "SET query_governor_cost_limit x" and the long running
select query results in the query being canceled. But it doesn't work with
Cayenne for some reason. I made a query chain, and added the governor as the
first query, then long running select. It all gets executed, which - in this
case - is a big problem! Log is shown below. I'd appreciate advice. Is
cayenne re-setting connection between queries in a chain? Is there any funky
stuff going with PreparedStatement?
INFO QueryLogger: SET query_governor_cost_limit 2
INFO QueryLogger: SELECT ...
INFO QueryLogger: === returned 2700 rows. - took 15594 ms.
Thanks!!
Kathy
--
View this message in context: http://www.nabble.com/query_governor_cost_limit-setting-not-working-with-cayenne--tp21099002p21099002.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: query_governor_cost_limit setting not working with cayenne?
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Dec 21, 2008, at 4:16 PM, KathyS wrote:
> Related question, if you have time. How can I hook onto connection
> creation
> in the pool? I'd love to be able to run the "set ..cost" statement
> for each
> new connection.
This is also fairly straightforward. Cayenne uses a
javax.sql.DataSource object to obtain connections. In many cases
DataSource is provided by the environment (e.g. webapp containers or
appservers) [1], so you can check the docs of you container on how to
install a custom DataSource there.
In cases when Cayenne manages its own DataSource (i.e. if you selected
"org.apache.cayenne.conf.DriverDataSourceFactory" for the DataNode
"DataSource Factory" in the Modeler), you can supply your own
DataSourceFactory (also via the Modeler).
FWIW, I won't be surprised if SQLServer JDBC driver would support
passing something in the URL that would set the "cost".
Andrus
[1] http://cayenne.apache.org/doc/using-jndi.html
Re: query_governor_cost_limit setting not working with cayenne?
Posted by KathyS <sc...@yahoo.com>.
Funny how simple things tend to be the answer! Thanks a lot, I'll try the
CostLimitQuery later this afternoon.
Related question, if you have time. How can I hook onto connection creation
in the pool? I'd love to be able to run the "set ..cost" statement for each
new connection.
--
View this message in context: http://www.nabble.com/query_governor_cost_limit-setting-not-working-with-cayenne--tp21099002p21115418.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: query_governor_cost_limit setting not working with cayenne?
Posted by Andrus Adamchik <an...@objectstyle.org>.
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.
>
>
Re: query_governor_cost_limit setting not working with cayenne?
Posted by KathyS <sc...@yahoo.com>.
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.
Re: query_governor_cost_limit setting not working with cayenne?
Posted by Andrus Adamchik <an...@objectstyle.org>.
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
Re: query_governor_cost_limit setting not working with cayenne?
Posted by KathyS <sc...@yahoo.com>.
I'm using Cayenne 2.
JDBC code is straightforward:
Statement stmnt = con.createStatement();
stmnt.execute("SET query_governor_cost_limit 2");
// now close this statement, prepare statement for long running query, run,
and it gets canceled by server
Cayenne code I used:
QueryChain chain = new QueryChain();
SQLTemplate cost = new SQLTemplate(x.class, "SET query_governor_cost_limit
2");
chain.addQuery(cost);
SelectQuery query = new SelectQuery(x.class, expression);
query.addPrefetch("xArray").setSemantics(PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
chain.addQuery(query);
List<x> hdrs = context.performQuery(chain);
It'd be great if you could help, Andrus, as this will be a showstopper for
us if no workaround. FYI I did my test with 1 connection specifiied as max
for the pool, plus using query chain I'm reasonably sure that both commands
ran on the same connection.
--
View this message in context: http://www.nabble.com/query_governor_cost_limit-setting-not-working-with-cayenne--tp21099002p21105642.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: query_governor_cost_limit setting not working with cayenne?
Posted by Andrus Adamchik <an...@objectstyle.org>.
Actually I think I already have sufficient information to try it out.
Still information about Cayenne version may be important.
Andrus
On Dec 20, 2008, at 10:22 AM, Andrus Adamchik wrote:
> Hi Kathy,
>
> Which version of Cayenne is that? Also do you have a working JDBC
> example - maybe I can try to reproduce it and compare to Cayenne.
>
> Andrus
>
>
> On Dec 20, 2008, at 1:08 AM, KathyS wrote:
>
>>
>> Hello,
>>
>> I'm retrofitting an existing project to use Cayenne back end. We
>> use SQL
>> Server, and we need to be able in certain cases to prevent long
>> running
>> queries from starting. SQL Server does that by using
>> query_governor_cost_limit setting and comparing it with time
>> estimation
>> prior to running the query. And it works with plain JDBC -
>> executing in the
>> same connection "SET query_governor_cost_limit x" and the long
>> running
>> select query results in the query being canceled. But it doesn't
>> work with
>> Cayenne for some reason. I made a query chain, and added the
>> governor as the
>> first query, then long running select. It all gets executed, which
>> - in this
>> case - is a big problem! Log is shown below. I'd appreciate advice.
>> Is
>> cayenne re-setting connection between queries in a chain? Is there
>> any funky
>> stuff going with PreparedStatement?
>>
>> INFO QueryLogger: SET query_governor_cost_limit 2
>> INFO QueryLogger: SELECT ...
>> INFO QueryLogger: === returned 2700 rows. - took 15594 ms.
>>
>> Thanks!!
>> Kathy
>> --
>> View this message in context: http://www.nabble.com/query_governor_cost_limit-setting-not-working-with-cayenne--tp21099002p21099002.html
>> Sent from the Cayenne - User mailing list archive at Nabble.com.
>>
>>
>
>
Re: query_governor_cost_limit setting not working with cayenne?
Posted by Andrus Adamchik <an...@objectstyle.org>.
Hi Kathy,
Which version of Cayenne is that? Also do you have a working JDBC
example - maybe I can try to reproduce it and compare to Cayenne.
Andrus
On Dec 20, 2008, at 1:08 AM, KathyS wrote:
>
> Hello,
>
> I'm retrofitting an existing project to use Cayenne back end. We use
> SQL
> Server, and we need to be able in certain cases to prevent long
> running
> queries from starting. SQL Server does that by using
> query_governor_cost_limit setting and comparing it with time
> estimation
> prior to running the query. And it works with plain JDBC - executing
> in the
> same connection "SET query_governor_cost_limit x" and the long running
> select query results in the query being canceled. But it doesn't
> work with
> Cayenne for some reason. I made a query chain, and added the
> governor as the
> first query, then long running select. It all gets executed, which -
> in this
> case - is a big problem! Log is shown below. I'd appreciate advice. Is
> cayenne re-setting connection between queries in a chain? Is there
> any funky
> stuff going with PreparedStatement?
>
> INFO QueryLogger: SET query_governor_cost_limit 2
> INFO QueryLogger: SELECT ...
> INFO QueryLogger: === returned 2700 rows. - took 15594 ms.
>
> Thanks!!
> Kathy
> --
> View this message in context: http://www.nabble.com/query_governor_cost_limit-setting-not-working-with-cayenne--tp21099002p21099002.html
> Sent from the Cayenne - User mailing list archive at Nabble.com.
>
>