Return-Path: 
 <user-return-4955-apmail-cayenne-user-archive=cayenne.apache.org@cayenne.apache.org>
Delivered-To: apmail-cayenne-user-archive@www.apache.org
Received: (qmail 62887 invoked from network); 21 Dec 2008 11:22:39 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 21 Dec 2008 11:22:39 -0000
Received: (qmail 93047 invoked by uid 500); 21 Dec 2008 11:22:38 -0000
Delivered-To: apmail-cayenne-user-archive@cayenne.apache.org
Received: (qmail 93027 invoked by uid 500); 21 Dec 2008 11:22:38 -0000
Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-help@cayenne.apache.org>
List-Unsubscribe: <mailto:user-unsubscribe@cayenne.apache.org>
List-Post: <mailto:user@cayenne.apache.org>
List-Id: <user.cayenne.apache.org>
Reply-To: user@cayenne.apache.org
Delivered-To: mailing list user@cayenne.apache.org
Received: (qmail 93016 invoked by uid 99); 21 Dec 2008 11:22:38 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 21 Dec 2008 03:22:38 -0800
X-ASF-Spam-Status: No, hits=0.2 required=10.0
	tests=SPF_PASS,WHOIS_MYPRIVREG
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: local policy)
Received: from [208.78.103.231] (HELO vorsha.objectstyle.org) (208.78.103.231)
    by apache.org (qpsmtpd/0.29) with SMTP; Sun, 21 Dec 2008 11:22:30 +0000
Received: (qmail 25344 invoked from network); 21 Dec 2008 11:22:09 -0000
Received: from unknown (HELO ?IPv6:::1?) (127.0.0.1)
  by localhost with SMTP; 21 Dec 2008 11:22:09 -0000
Message-Id: <5E306D45-3600-4E81-99F0-4347338A07C4@objectstyle.org>
From: Andrus Adamchik <andrus@objectstyle.org>
To: user@cayenne.apache.org
In-Reply-To: <21105729.post@talk.nabble.com>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0 (Apple Message framework v929.2)
Subject: Re: query_governor_cost_limit setting not working with cayenne?
Date: Sun, 21 Dec 2008 13:22:08 +0200
References: <21099002.post@talk.nabble.com>
 <77FB38E5-7E1D-490C-B841-EEE0A8323AFB@objectstyle.org>
 <CF0DBE01-49CB-403D-9377-C023DFA7F8B7@objectstyle.org>
 <21105642.post@talk.nabble.com>
 <7CEB3A3A-01F0-447B-90CE-07717F1001C5@objectstyle.org>
 <21105729.post@talk.nabble.com>
X-Mailer: Apple Mail (2.929.2)
X-Virus-Checked: Checked by ClamAV on apache.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.
>
>