I'm testing paging through a table with 520 rows. I'm also watching the SQL as its passed across the wire to the Sybase server. As shown below, I'm trying to get the first page of 20 rows from this table.

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                LinqMetaData metadata = new LinqMetaData(adapter);
                var query = (from localStrings in metadata.LocalStrings
                             select localStrings).TakePage(1,20);
                foreach (LocalStringsEntity localStrings in query)
                {
                    Console.WriteLine(localStrings.String);
                }
            }

However, this is the query I see on the wire:

SELECT LPLA_1.LocaleId, LPLA_1.ProgramId, LPLA_1.StringId, LPLA_1.String FROM lookup.dbo.LocalStrings LPLA_1

From the application, the results look ok however it looks like all 520 rows are being returned and the paging is occuring on the client rather than at the server. Is this correct or am I doing something wrong?

Thanks,

Ken

This is the output level 4 tracing for SybaseAseDQE and LinqExpressionHandler

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[commonLookup.EntityClasses.LocalStringsEntity]).Select(localStrings => localStrings).TakePage(1, 20)
: GenericExpressionHandler::HandleExpression Start
:    GenericExpressionHandler::MethodCallExpression Start
:        >>> Method name: TakePage
:        GenericExpressionHandler::ExpressionList Start
:            GenericExpressionHandler::HandleExpression Start
:                GenericExpressionHandler::MethodCallExpression Start
:                    >>> Method name: Select
:                    GenericExpressionHandler::ExpressionList Start
:                        GenericExpressionHandler::HandleExpression Start
:                            GenericExpressionHandler::UnaryExpression Start
:                                GenericExpressionHandler::HandleExpression Start
:                                    GenericExpressionHandler::LambdaExpression Start
:                                        GenericExpressionHandler::HandleExpression Start
:                                        GenericExpressionHandler::HandleExpression End
: GenericExpressionHandler::HandleExpression Start
: GenericExpressionHandler::HandleExpression End
:                                    GenericExpressionHandler::LambdaExpression End
:                                GenericExpressionHandler::HandleExpression End
:                            GenericExpressionHandler::UnaryExpression End
:                        GenericExpressionHandler::HandleExpression End
:                    GenericExpressionHandler::ExpressionList End
:                GenericExpressionHandler::MethodCallExpression End
:            GenericExpressionHandler::HandleExpression End
:        GenericExpressionHandler::ExpressionList End
:    GenericExpressionHandler::MethodCallExpression End
: GenericExpressionHandler::HandleExpression End
: GenericExpressionHandler::HandleExpression Start
:    GenericExpressionHandler::MethodCallExpression Start
:        >>> Method name: TakePage
:        GenericExpressionHandler::ExpressionList Start
:            GenericExpressionHandler::HandleExpression Start
:                GenericExpressionHandler::MethodCallExpression Start
:                    >>> Method name: Select
:                    GenericExpressionHandler::ExpressionList Start
:                        GenericExpressionHandler::HandleExpression Start
:                            ConstantExpression start/end
:                        GenericExpressionHandler::HandleExpression End
:                        GenericExpressionHandler::HandleExpression Start
:                            GenericExpressionHandler::UnaryExpression Start
:                                GenericExpressionHandler::HandleExpression Start
:                                    GenericExpressionHandler::LambdaExpression Start
:                                        GenericExpressionHandler::HandleExpression Start
:                                        GenericExpressionHandler::HandleExpression End
:                                    GenericExpressionHandler::LambdaExpression End
:                                GenericExpressionHandler::HandleExpression End
:                            GenericExpressionHandler::UnaryExpression End
:                        GenericExpressionHandler::HandleExpression End
:                    GenericExpressionHandler::ExpressionList End
:                GenericExpressionHandler::MethodCallExpression End
:            GenericExpressionHandler::HandleExpression End
:            GenericExpressionHandler::HandleExpression Start
:                ConstantExpression start/end
:            GenericExpressionHandler::HandleExpression End
:            GenericExpressionHandler::HandleExpression Start
:                ConstantExpression start/end
:            GenericExpressionHandler::HandleExpression End
:        GenericExpressionHandler::ExpressionList End
:    GenericExpressionHandler::MethodCallExpression End
: GenericExpressionHandler::HandleExpression End
: GenericExpressionHandler::HandleExpression Start
:    PreProcessor::MethodCallExpression Start
:        PreProcessor::HandleMethodCallTakePage Start
:            GenericExpressionHandler::HandleExpression Start
:                PreProcessor::MethodCallExpression Start
:                    PreProcessor::MethodCallSelect Start
:                        GenericExpressionHandler::HandleExpression Start
:                            QueryExpressionBuilder::HandleConstantExpression Start
:                            QueryExpressionBuilder::HandleConstantExpression End
:                        GenericExpressionHandler::HandleExpression End
:                        GenericExpressionHandler::HandleExpression Start
:                            GenericExpressionHandler::LambdaExpression Start
:                                GenericExpressionHandler::HandleExpression Start
:                                    PreProcessor::HandleParameterExpression Start
:                                    PreProcessor::HandleParameterExpression End
:                                GenericExpressionHandler::HandleExpression End
:                            GenericExpressionHandler::LambdaExpression End
:                        GenericExpressionHandler::HandleExpression End
:                    PreProcessor::MethodCallSelect End
:                PreProcessor::MethodCallExpression End
:            GenericExpressionHandler::HandleExpression End
:            GenericExpressionHandler::HandleExpression Start
:                QueryExpressionBuilder::HandleConstantExpression Start
:                    ConstantExpression start/end
:                QueryExpressionBuilder::HandleConstantExpression End
:            GenericExpressionHandler::HandleExpression End
:            GenericExpressionHandler::HandleExpression Start
:                QueryExpressionBuilder::HandleConstantExpression Start
:                    ConstantExpression start/end
:                QueryExpressionBuilder::HandleConstantExpression End
:            GenericExpressionHandler::HandleExpression End
:            PreProcessor::HandleMethodCallTakePage Start
:            PreProcessor::MethodCallExpression End
:        GenericExpressionHandler::HandleExpression End
: GenericExpressionHandler::HandleExpression Start
:    GenericExpressionHandler::HandleSelectExpression Start
:        GenericExpressionHandler::HandleExpression Start
:            GenericExpressionHandler::HandleSelectExpression Start
:                GenericExpressionHandler::HandleExpression Start
:                GenericExpressionHandler::HandleExpression End
:                GenericExpressionHandler::HandleExpression Start
:                    GenericExpressionHandler::HandleProjectionExpression Start
:                        GenericExpressionHandler::HandleExpression Start
:                            GenericExpressionHandler::LambdaExpression Start
:                                GenericExpressionHandler::HandleExpression Start
:                                GenericExpressionHandler::HandleExpression End
:                            GenericExpressionHandler::LambdaExpression End
:                        GenericExpressionHandler::HandleExpression End
:                    GenericExpressionHandler::HandleProjectionExpression End
:                GenericExpressionHandler::HandleExpression End
:            GenericExpressionHandler::HandleSelectExpression End
:        GenericExpressionHandler::HandleExpression End
:    GenericExpressionHandler::HandleSelectExpression End
: GenericExpressionHandler::HandleExpression End
: GenericExpressionHandler::HandleExpression Start
:    GenericExpressionHandler::HandleSelectExpression Start
:        GenericExpressionHandler::HandleExpression Start
:            GenericExpressionHandler::HandleSelectExpression Start
:                GenericExpressionHandler::HandleExpression Start
:                GenericExpressionHandler::HandleExpression End
:                GenericExpressionHandler::HandleExpression Start
:                    GenericExpressionHandler::HandleProjectionExpression Start
:                        GenericExpressionHandler::HandleExpression Start
:                            GenericExpressionHandler::LambdaExpression Start
:                                GenericExpressionHandler::HandleExpression Start
:                                GenericExpressionHandler::HandleExpression End
:                            GenericExpressionHandler::LambdaExpression End
:                        GenericExpressionHandler::HandleExpression End
:                    GenericExpressionHandler::HandleProjectionExpression End
:                GenericExpressionHandler::HandleExpression End
:            GenericExpressionHandler::HandleSelectExpression End
:        GenericExpressionHandler::HandleExpression End
:    GenericExpressionHandler::HandleSelectExpression End
: GenericExpressionHandler::HandleExpression End
: GenericExpressionHandler::HandleExpression Start
:    QueryExpressionBuilder::HandleSelectExpression Start
:        GenericExpressionHandler::HandleSelectExpression Start
:            GenericExpressionHandler::HandleExpression Start
:                QueryExpressionBuilder::HandleSelectExpression Start
:                    GenericExpressionHandler::HandleSelectExpression Start
:                        GenericExpressionHandler::HandleExpression Start
:                        GenericExpressionHandler::HandleExpression End
:                        GenericExpressionHandler::HandleExpression Start
:                            QueryExpressionBuilder::HandleProjectionExpression Start
:                                GenericExpressionHandler::HandleExpression Start
:                                GenericExpressionHandler::HandleExpression End
:                            QueryExpressionBuilder::HandleProjectionExpression End
:                        GenericExpressionHandler::HandleExpression End
:                    GenericExpressionHandler::HandleSelectExpression End
:                QueryExpressionBuilder::HandleSelectExpression End
:            GenericExpressionHandler::HandleExpression End
:        GenericExpressionHandler::HandleSelectExpression End
:    QueryExpressionBuilder::HandleSelectExpression End
: GenericExpressionHandler::HandleExpression End
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT LPLA_1.LocaleId, LPLA_1.ProgramId, LPLA_1.StringId, LPLA_1.String FROM lookup.dbo.LocalStrings LPLA_1

Method Exit: CreateSelectDQ
Generated Sql query: 
    Query: SELECT LPLA_1.LocaleId, LPLA_1.ProgramId, LPLA_1.StringId, LPLA_1.String FROM lookup.dbo.LocalStrings LPLA_1

Method Exit: CreatePagingSelectDQ

Linq to LLBLGen Pro's paging mechanism is the same as the paging mechanism build into the querying API of the LLBLGen Pro framework, so it's supposed not related to Linq2LLBL. Did you see the same behavior without LINQ2LLBL?

Please update to the latest build and try again. And... What ASE Server and ASE ADO.NET versions?

Do you have an IMAGE/TEXT field in that entity? If so, it can't apply DISTINCT to the query and reverses to client-side paging. Client side paging is: it reads till it has read enough rows (using a datareader). So it's not passing all rows to the client.

The reason is that the DQE (the engine which creates the queries) for Sybase gets a parameter passed in which says "no duplicates" and a page has to be requested. Paging only works if there's certainty that there are no duplicate rows, otherwise you'll end up with the wrong page .

Take this example: var q = (from c in metaData.Customer select c).TakePage(3, 10);

this is a simple paging query. Though, what if we change it a bit: var q = (from c in metaData.Customer select new { c.City, c.Country} ).TakePage(3, 10);

if we've more than one customer in the same city/country, this will lead to duplicate rows, so 'DISTINCT' has to be applied into the query. In this case, that's possible, both fields aren't of a type which conflicts with DISTINCT (like Image or Ntext)

THough what if we would do this: var q = (from e in metaData.Employee select new { e.LastName, e.Photo} ).TakePage(3, 10);

Now, distinct can't be applied and it has to be done client side.

A DQE gets a set of fields to create a query for. It doesn't know if these fields form a single query or that the fields are a subset of an entity (like the examples above). therefore, it can't rely on the test if there are joins or not: if distinct can't be applied, server-side paging can't be performed.

After v2.6 was released we received a couple of reports that this isn't always efficient: for example the northwind query: var q = (from e in metaData.Employee select e).TakePage(2, 2);

is done on the client, because Employee contains two distinct conflicting fields, which means that DISTINCT can't be applied and because the DQE doesn't know that the fields in the list of fields passed to it form an entity, so the PK fields in the query are the true PK fields of the entity they're in, it can't decide if the resultset is duplicate free, hence it flips to client-side paging.

We're currently looking at adding a flag somewhere in the internal code so the DQE's know the fetch is for an entity and no duplicates can occur so it can decide to do server-side paging no matter what. Till then, you'll see these client-side paging queries.

It's a bit problematic: adding better checking requires a breaking change. the reason is that the current routine which checks whether duplicate rows can occur only receives the relations for the query, not the fields (as that wouldn't matter anyway). If we would add a flag to the fields object, (internal) which would state the fields form an entity, and pass the fields to that routine, the routine could take that flag into account. The DQE's call this routine which is in the ORMSupportClasses. this means that a change in the signature of the routine will make that all DQE's are updated too. It's not said that someone updates the DQE if hte ORMSupportClasses dll is updated as well.... (which causes a method not found exception thrown at runtime by the CLR)

(edit) Due to the breaking change, we can't push this into the current version, so we'll stick with the client-side paging for some queries, which isn't that inefficient anyway, so we avoid the breaking change, which is something we can't have.

David

I haven' tried it with the native querying API of LLBLGen Pro. If you think its worth it I will.

I'm using 15.0.2 of Sybase ASE Server and 1.15.152.0 of the ADO.NET provider.

I'm using 2.6 of LLBLGen Pro June 6th release. I assume this is the latest, right?

Ken

This is a pretty simple table it has 4 columns, the first 3 of which are primary keys:

int LocaleId
int ProgramId
int StringId
text String

Sounds like the reason its resorting to client side paging is because of the text field? So, using the client side paging does not have a large effect on the client since it uses the datareader to get only what it needs. However, the server has still executed the query to get all the rows in the table to return so its probably putting an unnecessary load there, correct?

Thanks,

Ken

kwo wrote:

This is a pretty simple table it has 4 columns, the first 3 of which are primary keys:

int LocaleId
int ProgramId
int StringId
text String

Sounds like the reason its resorting to client side paging is because of the text field? So, using the client side paging does not have a large effect on the client since it uses the datareader to get only what it needs. However, the server has still executed the query to get all the rows in the table to return so its probably putting an unnecessary load there, correct?

Thanks,

Ken

Correct. You can limit this by excluding the Text field, if you don't need it. This will avoid the conflict with DISTINCT (as in that case there's no field with a distinct conflicting type) and you'll get serverside paging. Excluding is done with the .Exclude(n=>n.FieldName) extension method, but it might not be applicable in your situation.

Paging on the server still fetches all the rows though, it inserts it into a temp table from which the page is retrieved, so the query is still executed in full (server side paging applies a TOP (pagesize*pagenumber)+1 to limit the total rowset though. )