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