Re: FetchSpec setFetchLimit??
Re: FetchSpec setFetchLimit??
- Subject: Re: FetchSpec setFetchLimit??
- From: Kieran Kelleher <email@hidden>
- Date: Mon, 12 Jul 2010 14:58:39 -0400
You can use ERXSqlHelper.limitExpressionForSQL, or here is a utility method I used once to convert a fetchspec to one with limited SQL in the SQL hints. The code is only used in one place in one project, so no guarantee of reliability applies to it right now:
Usage Example:
// Fetch the most recent shipped message
ERXSortOrderings s = CTMessage.XKEY_OID.descs();
ERXFetchSpecification<CTMessage> fs = new ERXFetchSpecification<CTMessage>(CTMessage.ENTITY_NAME,null,s);
fs = WKEOUtils.limitedFetchSpecification(ec, fs, 0, 1);
Utility method:
/**
* @param <T>
* @param ec
* @param spec
* @param start
* @param end
* @return a {@link ERXFetchSpecification} clone having the custom SQL
* "hints" dictionary populated with the fetch SQL and the SQL has
* fetch LIMIT clause based on start and end params
*/
public static <T extends EOEnterpriseObject> ERXFetchSpecification<T> limitedFetchSpecification(
EOEditingContext ec, ERXFetchSpecification<T> spec, int start, int end) {
EOEntity entity = ERXEOAccessUtilities.entityNamed(ec, spec.entityName());
EOModel model = entity.model();
spec = (ERXFetchSpecification<T>) spec.clone();
EOQualifier qualifier = spec.qualifier();
if (qualifier != null) {
qualifier = EOQualifierSQLGeneration.Support._schemaBasedQualifierWithRootEntity(qualifier, entity);
}
if (qualifier != spec.qualifier()) {
spec.setQualifier(qualifier);
}
if (spec.fetchLimit() > 0) {
spec.setFetchLimit(0);
spec.setPromptsAfterFetchLimit(false);
}
NSArray<EOAttribute> attributes = attributesToFetchForEntity(spec, entity);
String sql = null;
EODatabaseContext dbc = EOUtilities.databaseContextForModelNamed(ec, model.name());
EOAdaptor adaptor = dbc.adaptorContext().adaptor();
// Not sure why I have to lock this, but doing so because
// ERXEOControlUtilities#sqlExpression... does
dbc.lock();
try {
EOSQLExpressionFactory sqlFactory = adaptor.expressionFactory();
EOSQLExpression sqlExpr = sqlFactory.selectStatementForAttributes(attributes, false, spec, entity);
sql = sqlExpr.statement();
// We completely ignore any existing SQL "hints" dictionary
ERXSQLHelper sqlHelper = ERXSQLHelper.newSQLHelper(dbc);
if (end >= 0) {
sql = sqlHelper.limitExpressionForSQL(sqlExpr, spec, sql, start, end);
}
} catch (Exception e) {
throw new NestableRuntimeException(e);
} finally {
dbc.unlock();
}
NSDictionary<String, String> hints = new NSDictionary<String, String>(sql,
EODatabaseContext.CustomQueryExpressionHintKey);
spec.setHints(hints);
return spec;
}
On Jul 12, 2010, at 11:27 AM, Mark Gowdy wrote:
>
> On 2 Mar 2009, at 23:43, Chuck Hill wrote:
>
>>
>> On Mar 2, 2009, at 1:42 PM, Kieran Kelleher wrote:
>>
>>> I always thought that EOFetchSpecification.setFetchLimit( int n) would generate SQL with LIMIT n tacked onto the end of the SQL statement, but it appears not to.
>>
>> This is the default behavior as not all databases implement this. Specific plugins add this, IIRC.
>>
>> Chuck
>
>
> Hi guys,
>
> I have just stumbled into the same issue here:
>
> I need to fetch the latest item from a table in a MySQL database, where there are almost half a million items
> I thought that fetchSpec.setFetchLimit(1) would tell the DB to do the hard work, but this appears not to be the case.
> Consequently, my app is exploding with 'out of memory' issues.
>
> Is there a work around for this problem (some plug-in I am unaware of), or do I need to get creative with raw-rows?
>
> A solution would be good, because I was using these all over the place (without knowledge of the consequences)
>
> Thanks again,
>
> Mark
>
>>
>>> Instead it fetches the all qualifying records and does the LIMIT in memory?! Has this always been the case .... or is it just the MySQL driver?
>>>
>>> For example:
>>>
>>> <snip>
>>> // Fetch the most recent shipped message
>>> ERXSortOrderings s = CTMessage.XKEY_OID.descs();
>>>
>>> ERXFetchSpecification<CTMessage> fs = new ERXFetchSpecification<CTMessage>(CTMessage.ENTITY_NAME,null,s);
>>>
>>> fs.setFetchLimit(1);
>>>
>>> System.out.println("MailingAddressUtilitiesTests.suppressionTest(): fs.SQL = " + ERXEOAccessUtilities.sqlForFetchSpecification(ec, fs));
>>>
>>> NSArray<CTMessage> objects = fs.fetchObjects(ec);
>>> </snip>
>>>
>>>
>>> ..... generates the following SQL without "LIMIT 1" !!!
>>>
>>> SELECT t0.oid, t0.oidcampaign, t0.oiddatarecord, t0.oidjob, t0.oidlocation, t0.oidmediatemplate, t0.recaddtime, t0.recadduser, t0.recmodtime, t0.recmoduser, RTRIM(t0.workflowstate) FROM message t0 ORDER BY t0.oid DESC;
>>> _______________________________________________
>>> Do not post admin requests to the list. They will be ignored.
>>> Webobjects-dev mailing list (email@hidden)
>>> Help/Unsubscribe/Update your Subscription:
>>>
>>> This email sent to email@hidden
>>>
>>
>> --
>> Chuck Hill Senior Consultant / VP Development
>>
>> Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems.
>> http://www.global-village.net/products/practical_webobjects
>>
>>
>>
>>
>>
>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list (email@hidden)
>> Help/Unsubscribe/Update your Subscription:
>>
>> This email sent to email@hidden
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden