I don't know how insane my question is, but is it possible to do lazy loading on a supertype?

My problem now is that when I do a paged (with CTE) query on a table that is part of a supertype/subtype hierarchy it takes about 7 seconds for the data to be returned. When I modify the query manually so that it does only return columns from the subtype (and not from the supertype) the same query takes about 2 seconds.

As my pages are only 10 rows, it would be more efficient to lazy load those 10 supertype rows separately to get the data I need to display from the supertype.

Regards,

Patrick

Hi Patrick.

Please read this: Patrick http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7576

David, thanks for the reply.

My situation is something else than described in that thread. I don't perform a query on the supertype but on the subtype. So by querying the subtype table it is not necessary to find out which subtype it is, as this can only be that subtype contained in the queried table.

I'll explain my problem more detailed: I perform a query for a paged view in a website. Each page has 10 rows. In this particular case 115,160 rows are returned so we have 11,516 pages. (Of course you could argue about the usability of an overview of 11,516 pages but that is a different story.) Retrieving the first pages is fast enough. However going to the last page takes about 7 seconds.

This is the query that is generated by LLBLGen for the last page (table and column names have been changed). It takes about 7 seconds.

WITH __actualSet AS 
( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM 
(SELECT DISTINCT TOP 115160 
[MyDatabase].[dbo].[SuperType].[SuperTypeID] AS [F0], 
[MyDatabase].[dbo].[SuperType].[ColumnA] AS [F1], 
[MyDatabase].[dbo].[SuperType].[ColumnB] AS [F2], 
[MyDatabase].[dbo].[SuperType].[ColumnC] AS [F3], 
[MyDatabase].[dbo].[SuperType].[ColumnD] AS [F4], 
[MyDatabase].[dbo].[SuperType].[ColumnE] AS [F5], 
[MyDatabase].[dbo].[SuperType].[ColumnF] AS [F6], 
[MyDatabase].[dbo].[SuperType].[ColumnG] AS [F7], 
[MyDatabase].[dbo].[SuperType].[ColumnH] AS [F8], 
[MyDatabase].[dbo].[SuperType].[GUID] AS [F9], 
[MyDatabase].[dbo].[SubType].[SuperTypeID] AS [F10], 
[MyDatabase].[dbo].[SubType].[ColumnA] AS [F11], 
[MyDatabase].[dbo].[SubType].[ColumnB] AS [F12], 
[MyDatabase].[dbo].[SubType].[ColumnC] AS [F13] 
FROM ( [MyDatabase].[dbo].[SuperType]  
INNER JOIN [MyDatabase].[dbo].[SubType]  
ON  [MyDatabase].[dbo].[SuperType].[SuperTypeID]=[MyDatabase].[dbo].[SubType].[SuperTypeID] )
WHERE ( [MyDatabase].[dbo].[SubType].[SuperTypeID] IS NOT NULL) 
ORDER BY [MyDatabase].[dbo].[SubType].[ColumnB] ASC,[MyDatabase].[dbo].[SubType].[ColumnA] ASC) AS _tmpSet) 
SELECT * FROM __actualSet WHERE [__rowcnt] > 115150 AND [__rowcnt] <= 115160 ORDER BY [__rowcnt] ASC

Now just by changing this query to the following, the query only takes 2 seconds:

WITH __actualSet AS 
( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM 
(SELECT DISTINCT TOP 115160 
[MyDatabase].[dbo].[SubType].[SuperTypeID] AS [F10], 
[MyDatabase].[dbo].[SubType].[ColumnA] AS [F11], 
[MyDatabase].[dbo].[SubType].[ColumnB] AS [F12], 
[MyDatabase].[dbo].[SubType].[ColumnC] AS [F13] 
FROM ( [MyDatabase].[dbo].[SuperType]  
INNER JOIN [MyDatabase].[dbo].[SubType]  
ON  [MyDatabase].[dbo].[SuperType].[SuperTypeID]=[MyDatabase].[dbo].[SubType].[SuperTypeID] )
WHERE ( [MyDatabase].[dbo].[SubType].[SuperTypeID] IS NOT NULL) 
ORDER BY [MyDatabase].[dbo].[SubType].[ColumnB] ASC,[MyDatabase].[dbo].[SubType].[ColumnA] ASC) AS _tmpSet) 
SELECT * FROM __actualSet WHERE [__rowcnt] > 115150 AND [__rowcnt] <= 115160 ORDER BY [__rowcnt] ASC

Note that all joins are left intact. I only change the result set so that it contains columns from only the subtype.

Hence, my wish for being able to do some kind of lazy loading on the supertype or maybe just the ability to indicate which columns should be returned in the result set.

Regards,

Patrick

or maybe just the ability to indicate which columns should be returned in the result set.

You are using LLBLGEN Pro Version 2.5, and there it has a new feature that allows you to execlude fields from being fetched.

Please check different overloads of the fetch method, and there you will may find some overloads that accepts a list of fields to be execluded.

It's now possible for an entity fetch to exclude fields or specify the subset of fields to fetch for an entity, a set of entities or a prefetch path node. This offers the ability to for example not load a large data field for an entity or set of entities to speed up performance. You can then load the data for the excluded fields later on into the same entity objects using specialized methods on (SelfServicing) the entity or entity collection or (Adapter) the DataAccessAdapter classes. This also gives the ability to specify that for example only field A, B and C should be fetched for entity E.

Walaa wrote:

You are using LLBLGEN Pro Version 2.5, and there it has a new feature that allows you to execlude fields from being fetched.

Yep, that would be great , if it would work

I use the following code (entity names have been changed)

            ExcludeIncludeFieldsList includedFields = new ExcludeIncludeFieldsList();
            includedFields.ExcludeContainedFields = false;
            includedFields.Add(SubTypeFields.ID);
            includedFields.Add(SubTypeFields.ColumnA);
            includedFields.Add(SubTypeFields.ColumnB);
            includedFields.Add(SubTypeFields.ColumnC);
            includedFields.Add(SubTypeFields.ColumnD);

            SubTypeCollection subTypeCollection = new SubTypeCollection();
            subTypeCollection.GetMulti(filterExpression, 0, sortExpression, readRelations, prefetchPath, includedFields,
                                            pageNumber, maximumRows);

However, the query is still the same as without specifying the include list.

You can't exclude: - PK fields - FK fields - discriminator fields

so if you exclude any of these, the exclusion of that field is ignored.

Going to the last page is slower indeed, because it has to fetch 110,000 rows of data, and indeed if you could exclude the fields from the supertype, it would be OK.

One other thing you could try is fetching a datareader and projecting it onto the subtype entity. That also allows paging, and you can specify the fields you need. As you fetch 10 entities you won't notice the slightly performance hit of projecting data instead of using the more optimized entity fetch code internally.

Ok, that explains it.

In the mean time I have taken an approach where I use a typed list that only returns the PK and does the paging for me and then read the entities separately.

Thanks for your input.

Patrick