Is there any way to get rid of TOP from the sub query generated by ScalarQueryExpression? The TOP 1 in the query really affecting the performance of the query.

When I removed TOP from the sub query execution time changed from 41 second to 4 seconds.

I am using version 2.0. Or is there any other way to write a sub query other than using ScalarQueryExpression? I am using aggregate function in the subquery and I have a relationcollection.

Is there any way to get rid of TOP from the sub query generated by ScalarQueryExpression? The TOP 1 in the query really affecting the performance of the query.

Please elaborate more. Subquery should always return 1 value, it doesn't include the TOP clause, I think that (include TOP clause in subquery) would decrease performance (see execution plans).

Are you using the subquery on the select clause? please post the example snippet.

This is the code :

ResultsetFields fields = new ResultsetFields(3); fields.DefineField(RemittanceBatchFields.BatchNumber, 0); fields.DefineField(RemittanceBatchFields.TotalAmount, 1); RelationCollection relationPosted = new RelationCollection(); relationPosted.Add(RemittanceEntity.Relations.RemittanceAllocationEntityUsingRemittanceFkid, JoinHint.Inner); fields.DefineField(new EntityField2("TotalPosted", new ScalarQueryExpression( RemittanceAllocationFields.AppliedAmount.SetAggregateFunction(AggregateFunction.Sum), RemittanceBatchFieldsFields.Id == RemittanceFields.RemittanceBatchFkid, relationPosted)), 2);

        List<BatchSummaryDomain> results = new List<BatchSummaryDomain>();

        DataProjectorToCustomClass<BatchSummaryDomain> projector =
                new DataProjectorToCustomClass<BatchSummaryDomain>(results);
        List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
        valueProjectors.Add(new DataValueProjector("BatchNumber", 0, typeof(string)));
        valueProjectors.Add(new DataValueProjector("BatchTotal", 1, typeof(decimal)));
        valueProjectors.Add(new DataValueProjector("TotalPostedInvoice", 2, typeof(decimal)));

        Using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            adapter.FetchProjection(valueProjectors, projector, fields,
                    bucket, searchCriteria.Pager.MaximumRecords,
                    sortExpression,false,
                    searchCriteria.Pager.PageIndex, searchCriteria.Pager.PageSize);
        }

The ScalarQueryExpression is adding TOP 1 to the sub query.

Is there any way to get rid of TOP clause or is there any other way to use a sub query? I am using version 2.0

Please try the following options:

1- Try it without a JOIN/Relation in the subQuery, no need for it. As the following works in Northwind:

select CustomerID, (select TOP 1 SUM(freight) from orders where customers.CustomerID = orders.CustomerID)
from customers

2- For the best performance I think you should not use a SubQuery, but rather a normal query with a Join/relation to the other table, and a Group BY, something like this (Northwind):

select c.CustomerID, SUM(o.freight) 
from customers c
Inner JOIN orders o on c.CustomerID = o.CustomerID
Group BY c.CustomerID

This piece of code works fine :

fields("BalanceDateTo").ExpressionToApply = New ScalarQueryExpression(TransactionDetailItemFields.Quantity.SetAggregateFunction(AggregateFunction.Sum), _
                                                                                          balanceDateToFilter, mainBalanceRelations, False)

This sets a custom expression (subselect) on the field BalanceDateTo using a SUM on the Quantity field in the TransactionDetailItem table.

balanceDateToFilter = predicateexpression mainBalanceRelations = relationcollection

The False value is used for the ForceRowLimit parameter of the ScalarQueryExpression function.

gtrz, Danny

Danny is right I've missed this out.

For reference please check this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10736

public ScalarQueryExpression( 
   IEntityFieldCore selectField,
   IPredicate filterToUse,
   IRelationCollection relationsToUse,
   bool forceRowLimit
)

Reference wrote:

forceRowLimit If set to true, it will force a TOP 1 clause to be emitted into the SQL (or equivalent if the db doesn't support TOP)

I think ForceRowLimit paramter is not available in the version I am using. Anyway I removed subquery from the select and I am using a view to access the data.

So if I pass False to the ForceRowLimit paramater, whether it remove TOP Clause from the sub query. I am using Version 2.0

Thanks for the replies