I see in the doc it's impossible to use the 2 features together As I allready use paging almost every where (with the third party DataAccessAdapter - but I modify to now use the normal way), does it mean I ll never use prefetch path ? In the future, it'll be possible ? I've a way I've to fetch page of 100 entities with another entity, right now I'm making 101 queries, I expected to do only 2 query with the prefetch ..

Paging is in theory a GUI-tier oriented feature. To make this more efficient, paging could be done on the server. However paging is a feature that is not part of SQL. This means that all kind of dirty tricks have to be added to make paging on the server a reality.

Firebird is the most easiest, it actually supports paging in SQL. Oracle does not, but can be tricked into using rownum with a nested subquery. For sqlserver it is very dirty: a temptable or table variable has to be used (no, other paging systems on sqlserver don't work in all situations)

Prefetch paths work like this: you want a set of objects of type A and via a prefetch path also the objects of type B.

It first fetches the objects of type A with a query Qa. It then uses a new query Qb which fetches B objects and filters on the rows using a subquery (fieldcomparset) using the query Qa (with only the PK fields of A)

This thus means that if A was a paging query, this will not work, as paging requires mangling of the SQL with constructs to trick the database engine in doing a paging action.

That's why paging is not compatible with prefetch paths.

Doing a lot of paging is not always what you want either, you could also use more restrictive filters for example to limit the data returned. (i.e.: no user can cope with 10,000 rows anyway)

Hum yes but paging is always needed when you want to list something For example I've to list all employees. I don't want to display all at once, but instead I made paging and only display 100 by page.

I've an idea, tell me if it's possible Right now, a select with paging looks like this :

CREATE TABLE #TempTable ...
INSERT INTO #TempTable 
    SELECT ... FROM TABLE1 WHERE PK=2
SELECT * FROM #TempTable WHERE [__rowcnt] > 0 AND [__rowcnt] <= 50;
DROP TABLE #TempTable

and a select with prefetch path look like this :

SELECT ... FROM TABLE2 WHERE FK IN (SELECT PK FROM TABLE1 WHERE PK=2)

I think it could be quite simple to make this :

CREATE TABLE #TempTable ...
INSERT INTO #TempTable 
    SELECT ... FROM TABLE1 WHERE PK=2
SELECT * FROM #TempTable WHERE [__rowcnt] > 0 AND [__rowcnt] <= 50;
//(DROP TABLE removed)

and then :

SELECT ... FROM TABLE2 WHERE FK IN (SELECT PK FROM #TempTable WHERE [__rowcnt] > 0 AND [__rowcnt] <= 50)

The drop table can be put : - just before the create table (after a select to see if the temp table exist) - when closing connection

This is to do only with the first level of prefetch path, as for next level nothing change In this way it could be possible to use both together

Nice

There is a problem though: the paging info is not known in the subquery. so if you have a prefetch path of, say, 4 nodes deep, the 4th node will have a deep nested query. Now, to apply paging, the query generated is mangled, i.e.: altered, to get paging to work. But which query to alter? this is unknown when the query is generated.

It can only be done with a lot of extra info send to the query engine, for just a specific case. I'll add it to the todo, for re-investigation.

I've not look into the code source, but I suppose a prefetch node ask to its parent the query used, so it can put it in the subquery. Why not rewrite the "query used" instead of "SELECT PK FROM TABLE1 WHERE PK=2" you put the query used in the paging.

For example, the QueryObject have a property "QueryForPreFetchNode" (string) set by default on the real query ("SELECT PK FROM TABLE1 WHERE PK=2"). When the QueryObject enter in the paging module, this property is overwrited to put the query on the temptable ("SELECT * FROM #TempTable WHERE [__rowcnt] > 0 AND [__rowcnt] <= 50;"). It don't care about page number or page size, it only a matter of string

It should also work if QueryForPreFetchNode is a QueryObject, in this case pageSize and pageCount are allready changed into PredicateFilter ("[__rowcnt] > 0 AND [__rowcnt] <= 50").

And it does't matter how deep the tree is, because the paging is only applied on the main select (the first one), and so only the first level of prefetch have to change it query to fetch from temptable.

Fabrice wrote:

Hum yes but paging is always needed when you want to list something For example I've to list all employees. I don't want to display all at once, but instead I made paging and only display 100 by page.

If I understand the question correctly, I would use a view in this situation. For simple lists, it is overkill to bring back full collections and full entities when I only need a few fields from each entity.

Fabrice wrote:

I've not look into the code source, but I suppose a prefetch node ask to its parent the query used, so it can put it in the subquery.

No it reuses predicates and relations. You can't re-use the query, because per PK field in the parent, a subquery has to be created which only returns 1 field.

Why not rewrite the "query used" instead of "SELECT PK FROM TABLE1 WHERE PK=2" you put the query used in the paging.

For example, the QueryObject have a property "QueryForPreFetchNode" (string) set by default on the real query ("SELECT PK FROM TABLE1 WHERE PK=2"). When the QueryObject enter in the paging module, this property is overwrited to put the query on the temptable ("SELECT * FROM #TempTable WHERE [__rowcnt] > 0 AND [__rowcnt] <= 50;"). It don't care about page number or page size, it only a matter of string

This won't work in all cases.

And it does't matter how deep the tree is, because the paging is only applied on the main select (the first one), and so only the first level of prefetch have to change it query to fetch from temptable.

No, the page query ends up deeper and deeper in the query teh more nodes are in the hierarchy.

As Shannon suggests, a view or typed list is more appropriate.

The view or the typed list don't change anything to the number of rows. It will not replace a paging... It's not a solution here. I can't send thousands records to the client, even if these records are smal because the typed list only fetch a few fields.

Fabrice wrote:

The view or the typed list don't change anything to the number of rows. It will not replace a paging... It's not a solution here. I can't send thousands records to the client, even if these records are smal because the typed list only fetch a few fields.

I haven't installed the latest version yet but according the the announcement, paging is supported for typed lists and typed views.

Fabrice wrote:

The view or the typed list don't change anything to the number of rows. It will not replace a paging... It's not a solution here. I can't send thousands records to the client, even if these records are smal because the typed list only fetch a few fields.

Why are you sending thousands of records? No client can cope with that . (and as I said: paging is a gui issue, so paging is used for convenience for the human user, not for code consuming the results). Perhaps it's wise to limit the resultset with more appropriate filters? (i.e.: if a user searches on the data and the resultset contains 50,000 rows, the user will not look at all those 50,000 rows. So the users are helped with a smaller resultset as well.

(and indeed, paging is supported for typed lists/views)

Otis wrote:

Fabrice wrote:

The view or the typed list don't change anything to the number of rows. It will not replace a paging... It's not a solution here. I can't send thousands records to the client, even if these records are smal because the typed list only fetch a few fields.

Why are you sending thousands of records? No client can cope with that . (and as I said: paging is a gui issue, so paging is used for convenience for the human user, not for code consuming the results). Perhaps it's wise to limit the resultset with more appropriate filters? (i.e.: if a user searches on the data and the resultset contains 50,000 rows, the user will not look at all those 50,000 rows. So the users are helped with a smaller resultset as well.

(and indeed, paging is supported for typed lists/views)

If you read me carefully, it's just what I said I can't send thousand record. It's why I need paging. For me, in a webservices environment, paging is not a gui issue but a server issue. When the user make a search and get 1000 results, I don't want to send all results and let him make the paging ... it take a lot of bandwith and it's very useless.

The hard side is that I can't use typed list because the return result is an (collection of) entity and several related entities determinated by a timeline. Typed list and DB view cannot determinate the related entity because there is a parameter (list/view don't take any parameters).

I allready fetch the related entity one by one but it's a lot of queries (1 + n => n for the number of elements in the collection), and the prefetch patch could reduce that to 2 queries (one for all main entities, and one for related entities). But if it's not possible to use prefetch path with paging ... I cannot use prefetch path at all.

And it's very strange to add 2 very usefull and wanted features and don't make it compatible. I upgraded mainly for these 2 features... and I'm almost sure it's possible to use them both, because it seem to be only a matter of transforming the original query to put it on the temp table. After that, all prefetch nodes use it as it will use the normal query...

Hope I'm not too muche boring

(I had a long reply, but lost it because explorer crashed and it took firefox with it sob

Fabrice wrote:

Otis wrote:

Fabrice wrote:

The view or the typed list don't change anything to the number of rows. It will not replace a paging... It's not a solution here. I can't send thousands records to the client, even if these records are smal because the typed list only fetch a few fields.

Why are you sending thousands of records? No client can cope with that . (and as I said: paging is a gui issue, so paging is used for convenience for the human user, not for code consuming the results). Perhaps it's wise to limit the resultset with more appropriate filters? (i.e.: if a user searches on the data and the resultset contains 50,000 rows, the user will not look at all those 50,000 rows. So the users are helped with a smaller resultset as well. (and indeed, paging is supported for typed lists/views)

If you read me carefully, it's just what I said I can't send thousand record. It's why I need paging. For me, in a webservices environment, paging is not a gui issue but a server issue. When the user make a search and get 1000 results, I don't want to send all results and let him make the paging ... it take a lot of bandwith and it's very useless.

I'll explain it with theory, I hope you'll forgive me for that . No user will be able to cope with 1000 results. 1000 results will be 50 pages of 20 results. Who browses further than 10 at google? (ok, perhaps in google images, when you're searching for a hotel in the capitol of France ). The user will have to make a decision which result to work with. How is this done? Most likely on criteria hte user could have specified when the search was performed.

One way to solve this is to define a certain limit, say 100. If a search results in 100 results or less, everything is send, and the client pages on the client. If the results exceed the limit, the user will get problems selecting the right result. So the user is offered to search within that resultset, with a specialized query, which effectively means just more predicates for the query. This will limit the resultset, which is better for the user, as the decision which result to use is easier.

The hard side is that I can't use typed list because the return result is an (collection of) entity and several related entities determinated by a timeline. Typed list and DB view cannot determinate the related entity because there is a parameter (list/view don't take any parameters).

You can filter on a view and you can filter on a typed list, and on a typed list even with multi-entity queries. This can make it worth using.

I allready fetch the related entity one by one but it's a lot of queries (1 + n => n for the number of elements in the collection), and the prefetch patch could reduce that to 2 queries (one for all main entities, and one for related entities). But if it's not possible to use prefetch path with paging ... I cannot use prefetch path at all.

You could offer a 2-step plan: the typed list for the paging and letting the user select which result to use, then with a fieldcomparerange predicate you fetch all entities which are selected by the user and use a prefetch path for the related data.

And it's very strange to add 2 very usefull and wanted features and don't make it compatible. I upgraded mainly for these 2 features... and I'm almost sure it's possible to use them both, because it seem to be only a matter of transforming the original query to put it on the temp table. After that, all prefetch nodes use it as it will use the normal query... Hope I'm not too muche boring

Trust me, if I could have added it, even if it would delay the release, I would have added it . The problem remains: the paging functionality on sqlserver is too cumbersome to make it work in prefetch paths, as it would require a custom prefetch path routine for just sqlserver (and even then...). I'll check if I can find a way around it, but I doubt it.

Fabrice wrote:

The hard side is that I can't use typed list because the return result is an (collection of) entity and several related entities determinated by a timeline. Typed list and DB view cannot determinate the related entity because there is a parameter (list/view don't take any parameters).

I am using the SelfServicing, not Adaptor, so maybe there is a difference or something I don't understand about your requirements. The way I deal with this (and it may be different that what you need to do) is to set up a view with the fields that I will show the user in my list. Then I use a filter when I fill the list (only Students who are registered for this Semester, for example. The view would include the Students 's info, the timespan, some entity names joined via foreign keys in the Students table). When the user selects a Student, then I pull all needed information for that user and display it for editing/viewing/whatever. Pulling full related entities for even 100 users when only 3 will be edited means that 97% of that information was not needed initially. There will be 3 more trips to the database but the reduction of data is worth it and if you want paging, then you're probably okay with the extra trips to the DB.

We allready have a typed list for fetching only the employee name and firstname, and so only fill a list. But it's not enougth. With typed list it's impossible to fetch the main entity and the related ones, trust me I allready tried. The problems are - I'll have a datatable merging the entities, I need 2 differents entities because clients can delete and add a related entity... - I cannot made a group by on related, so I'll fall in some situation used in the previous version of llblgen : do it by code The main problem is the first point ... I only speak about 1 main and 1 entity but in fact it's 1 main and 1 related for one group, and 1 main and 5 related for a second group.

I'm in a webservices environment, not only users will consume the webservice but also automatic process program. If such a program request 1000 users, I've to send it. For example, we have a background process that will compute time registration for all employees. If the process request 1000 employees, I've to give it (page by page of course, but he will use the whole result). I can't say "no ... sorry ... max 100 .. you have to make a search" The webservice have to work in all situations, so like I said paging is for me a server issue, it's not on the client side. And if it's a user, there are for example 105 employees in the corp. Well, he'll not understand why he have to make a search only to view the last 5 ... Human resource people (My program is for hr management) are not very "computer addict", often the director itself can only start word and print ... I speak by experience.

Otis wrote:

You could offer a 2-step plan: the typed list for the paging and letting the user select which result to use, then with a fieldcomparerange predicate you fetch all entities which are selected by the user and use a prefetch path for the related data.

It's what I was looking at yesterday but now it's too much change to the webservice interface as old methods will not work as before. So I'll continue with paging without prefetch (manual prefetch for each entity). Maybe I'll try in the overrided FetchEntityCollection to collect all id to make a query with fieldcomparerange predicate, like you said, and merge the result.

Fabrice wrote:

We allready have a typed list for fetching only the employee name and firstname, and so only fill a list. But it's not enougth. With typed list it's impossible to fetch the main entity and the related ones, trust me I allready tried. The problems are - I'll have a datatable merging the entities, I need 2 differents entities because clients can delete and add a related entity... - I cannot made a group by on related, so I'll fall in some situation used in the previous version of llblgen : do it by code The main problem is the first point ... I only speak about 1 main and 1 entity but in fact it's 1 main and 1 related for one group, and 1 main and 5 related for a second group.

Ok, but the main problem is: what does the user want? -> data to edit. Not endless lists of data to browse through . I'm generally speaking now, but a lot of developers get lost in their thinking when they think about the HOW, which sql to execute etc, while they spent not a lot thinking on what exactly they want to retrieve and WHY. If your user is editing a single entity, the search results should be a portal to get that data, not 1000 times that data, as the user is going to edit 1, not 1000 entities. (I hope this makes sense )

I'm in a webservices environment, not only users will consume the webservice but also automatic process program. If such a program request 1000 users, I've to send it. For example, we have a background process that will compute time registration for all employees. If the process request 1000 employees, I've to give it (page by page of course, but he will use the whole result). I can't say "no ... sorry ... max 100 .. you have to make a search"

Understood.

The webservice have to work in all situations, so like I said paging is for me a server issue, it's not on the client side. And if it's a user, there are for example 105 employees in the corp. Well, he'll not understand why he have to make a search only to view the last 5 ... Human resource people (My program is for hr management) are not very "computer addict", often the director itself can only start word and print ... I speak by experience.

It's a problem that's not easily solved, I admit. If paging and prefetchpaths were combinable, it would be easier. Still a lot of data would be transfered to the client when a human is operating the system, for example 150 results, when 1 has to be edited. that's 149 times too many data sent. For a process which has to process ALL data, it requires all data anyway, so send it in large chunks (or use logic in the service to process the data without having to send it to an external process.). If efficiency is a requirement, I'd opt for 2 interfaces on the service, or 2 methods on the service, one for the automatic process and one for clients operated by humans.

Otis wrote:

You could offer a 2-step plan: the typed list for the paging and letting the user select which result to use, then with a fieldcomparerange predicate you fetch all entities which are selected by the user and use a prefetch path for the related data.

It's what I was looking at yesterday but now it's too much change to the webservice interface as old methods will not work as before. So I'll continue with paging without prefetch (manual prefetch for each entity). Maybe I'll try in the overrided FetchEntityCollection to collect all id to make a query with fieldcomparerange predicate, like you said, and merge the result.

That would be an option as a page is often not that large.

Actually I allready have 3 methods : 1) fetch a list of all employees (typed list) 2) fetch 1 employee (entity returned) 3) fetch a list of employee with paging (entities returned) So If he want to edit an employee, he only use the 1 and 2 methods. The background process will use the 3rd method.

But .. yes there is always a but The problem is, when he have to display in the list more data than the data available in the typed list . In this case, the control switch to the 3rd method. It's why I have to make the 3rd method with paging functionalities. But I'll not continue to take your time with my problems héhé

So I'll try to collect IDs and manually merge.

If you've questions how to do an effective merge, let me know

Ah any help is welcome Actually I work wit hashtable :

            // make the FieldCompareRangePredicate
            Hashtable mergeTable    = new Hashtable();
            int[]   list            = new int[parentCollection.Count];
            int     cpt             = 0;
            foreach (IEntity2 entity in parentCollection) 
            {
                list[cpt]   = (int)entity.Fields[this.ParentPkField.Name].CurrentValue;
                mergeTable.Add(list[cpt], entity);
                cpt++;
            }
            
            IRelationPredicateBucket predicate  = new RelationPredicateBucket();
            predicate.PredicateExpression.Add(new FieldCompareRangePredicate(this.ParentPkField, null, list));
            predicate.PredicateExpression.Add();

            // fetch
            EntityCollection childCollection    = new EntityCollection(this.ChildFactoryToUse);
            adapter.FetchEntityCollection(childCollection, predicate);

            // merge
            PropertyInfo TlInfo                 = parentCollection[0].GetType().GetProperty("TLInformation");
            foreach (IEntity2 entity in childCollection) 
            {
                TlInfo.SetValue(mergeTable[entity.Fields[this.ParentPkField.Name].CurrentValue], entity,null);
            }

So, I create an hastable, and fill it with my pk as key and the object as value Then when I've fetched the child entities, I assign :

mergeTable[child.parentFkField].childEntity = child

I'm my case it's specific because the field is always named "TLInformation" (TimeLine informations). For exemple, I've an Employee, and an EmployeeTl entities. When I fetch all employees at a specific date (ie today) I've to fetch the employee (general information) and foreach Employee I've to fetch EmployeeTl valid for today (TLInformation).

hope I'm clear ... don't think so but ..

PS : under dev, not tested the code above. Compiling only.

Sounds good. I use a more generic approach based on the entity relation defined between the two entities, so that entity relation object is used to get from child to parent using a hashtable. Hashes are stored using the GetHashValue() returned from an entity, which is the hashvalue for the PK. These can contain duplicates which is the reason for the extra code to find the exact match.

If you don't need all that, this approach is pretty simple and effective

Yes my case is a lot more simple because I'll never hav duplicate and I allready know the relations because this code is called by a derived factory where I put needed informations.

I also need to implement paging in the middle tier and while I fully agree with Frans that paging is a human user concern there are many cases where this concept needs to be pushed back to the middle tier for performance reasons.

For instance I pull data from the DB and perform expensive processing and transformations on that data before sending it out to the presentation tier (LLBLGen Entities never leave the middle tier). There's no sense in processing data that will never be seen on the client and hence I need to push the paging directive back down to the middle tier.

I also make entensive use of prefetch paths and didn't realise that paging and prefetchs are not the best of friends just yet .

My solution like Fabrice is to pull out all the prefetches and replace with my own custom prefetch code (similar to that above what is discussed above). "Given a collection of entities fetch all the particular related entities for the entire collection".

I decided that it might be more performant to write a template to generate the cusom prefetches rather than go for a generic solution. It's a pity the LLBLgen Merge functionality is not exposed via the API...

I am having difficulty understanding how best to handle the Many to Many merges however. My understanding so far is that this is going to require a minimum of two queries, one for intermediary table and one for the target or "end" table. Of course a single query should be possible if I took a lower level approach, i.e. selecting all fields in the "end" table and joining with the intermediary table to get the linking ID for the "start" table, but this would require building up my own Entites from the raw data and things start to get messy as I would end up duplicating a lot of the LLBLGen template stuff.

Otis wrote:

If you've questions how to do an effective merge, let me know

I'm letting you know

Marcus

Marcus wrote:

I'm letting you know Marcus

It's okay... I've found your MergeManyToMany() and see that you have used a TypedList to solve the 2 query problem. I need to change your prefetch path routines to make them work with paging in my particular situation... Do you mind if I re-use parts of this code in a seperate assembly until LLBLGen support these two features together?

Frans,

I have now implemented Prefetch and Paging in DataAccessAdapterBase for my projects's requirement.

Now I've got to convince you to merge the changes into the mainline

No seriously, it was a piece of cake.

I added a new FetchEntityCollection overload:

public virtual void FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, int maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, int pageNumber, int pageSize)

and made a copy of the FetchPrefetchPath method and called it FetchPrefetchPathWithPaging. In that method (which only gets called for paging + prefetch) I simply changed the FieldCompareSetPredicates to use a FieldCompareValuePredicates which is build from all the rootEntities key values. The theory here is that the rootEntities will have already been paged down to size in this method and therefore the number will be small.

EG:

if(currentElement.TypeOfRelation==RelationType.ManyToMany)
{
    // Construct the filter based on the given relation, which is the relation between the start entity and the intermediate entity.
    // Start entity in the relation is always the parent entity for this fetch. 

    for (int m = 0; m < rootEntities.Count; m++)
    {
        IPredicateExpression filter = new PredicateExpression();

        for (int j = 0; j < currentElement.Relation.AmountFields; j++)
        {
            IEntityFieldCore pkField = rootEntities[m].Fields[currentElement.Relation.GetPKEntityFieldCore(j).Name];

            // start entity is always the PK side.
            rootEntitiesArePkSide = true;

            filter.Add(new FieldCompareValuePredicate(
                currentElement.Relation.GetFKEntityFieldCore(j), 
                null, 
                ComparisonOperator.Equal,  
                pkField.CurrentValue, 
                ((EntityRelation)currentElement.Relation).AliasFKSide));
        }

        elementFilter.PredicateExpression.AddWithOr(filter);
    }
}

Of course there is a further optimization that can be made here. If currentElement.Relation.AmountFields == 1, then FieldCompareRangePredicate can be used instead by comparing to an array of the relevant keys.

So I'm happy , but do you think we could get this feature included into the mainline? Of course I can zip up the class and send to you.

Marcus

I'll look at it closely in the morning

Paging and prefetch paths is a bit problematic because say you have 10 customers and each customer has 100 orders, and you prefetch the orders for the customers, but you have to fetch page 3.

How would you do that? .

Also, the filter for the root entities, in this case the customers, will be used to fetch the orders. However, if we've requested a given page, say 14, and on that page are those 10 customers, how am I to filter on the orders, using the filter of the customers? that's pretty hard.

Otis wrote:

I'll look at it closely in the morning

Paging and prefetch paths is a bit problematic because say you have 10 customers and each customer has 100 orders, and you prefetch the orders for the customers, but you have to fetch page 3.

How would you do that? .

Maybe I'm missing some complexity that you have already thought about... but in your example "10 customers and each customer has 100 orders". Im not considering paging on the prefetched entities...

Say you have 10,000 customers and each has 100 orders. I only want to be able to page the customers and also get their orders at the same time. I don't want to get customers 30-40 (page 3 with a page size of 10) and also get orders 60-70 (page 6, pagesize 10). That's too difficult to specify in a single fetch. I only want to get customers on page 3 (30-40) and their respective orders...

All my changes allow is for you to fetch a paged collection and apply the normal prefetch to the resulting rows found on the selected page.

Otis wrote:

Also, the filter for the root entities, in this case the customers, will be used to fetch the orders. However, if we've requested a given page, say 14, and on that page are those 10 customers, how am I to filter on the orders, using the filter of the customers? that's pretty hard.

Not at all... once we have the PK IDs for the 10 cutomers on the page, getting their order is a piece of cake, because it just 10 OR predicates based on the customerID. I'm not reusing the predicate because I'm moved away from FieldCompareSetPredicate. Instead I build a new FieldCompareValuePredicate which comprises: (A AND B) OR (C AND D) OR (E AND F) where A,B is the composite PK and so on... every time FetchPrefetchPathWithPaging is called.

While this kind of predicate is very long winded for a large number of records, the fact that we have already narrowed the selection down to the set of rows found on the page only... the set is small... as the routine recurses, the new RootEntities are passed in as normal, but the predicates are rebuilt so you also avoid the SELECT * IN (SELECT * IN (SELECT * IN))) problem... which means that the routine is now much more performant.

I haven't fully tested it, but my app is working perfectly now with paging and prefetches .

As i said maybe there are cases that I haven't considered, but I think this basic set of functionality is essential, certainly for me...