Hi,

I tried this code

LINQ2LLBL:

MyLlbl.LLBL.Linq.LinqMetaData md = new MyLlbl.LLBL.Linq.LinqMetaData();
var q = from c in md.SomeTable select c;
var q2 = q.Skip(30000).Take(100);
... access q2 to really fetch data...

and equivalent LINQ2SQL:

MyLlblDataContext md = new MyLlblDataContext();
var q = from c in md.SystemEvents select c;
var q2 = q.Skip(30000).Take(100);

and "good old" LLBL example

MyTableCollection sec = new MyTableCollection();
sec.GetMulti(null, 0, null, null, null, 300, 100);

all should return the same results, but it does not take the same time (MyTable has >300k records) LINQ2LLBL = more than 30 000 ms = timeout (ok, I could increase it but why) LINQ2SQL = 3 000 ms LLBL = LINQ2LLBL

These are the queries (please forget the table names, SystemEvent = MyTable )

LINQ2SQL

{SELECT [t1].[IdSystemEvent], [t1].[CodeEventType], [t1].[Name], [t1].[Message], [t1].[Created], [t1].[CodeProject], [t1].[Hash], [t1].[Subject], [t1].[UserAgent]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[IdSystemEvent], [t0].[CodeEventType], [t0].[Name], [t0].[Created], [t0].[CodeProject], [t0].[Hash], [t0].[Subject], [t0].[UserAgent]) AS [ROW_NUMBER], [t0].[IdSystemEvent], [t0].[CodeEventType], [t0].[Name], [t0].[Message], [t0].[Created], [t0].[CodeProject], [t0].[Hash], [t0].[Subject], [t0].[UserAgent]
    FROM [dbo].[SystemEvent] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
}

LINQ2LLBL

    Query: 
CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT NULL,[IdSystemEvent][Int] NULL,[CodeEventType][Int] NULL,[Name][NVarChar](256) NULL,[Message][NText] NULL,[Created][DateTime] NULL,[CodeProject][Char](3) NULL,[Hash][NVarChar](256) NULL,[Subject][NVarChar](256) NULL,[UserAgent][NVarChar](512) NULL);

INSERT INTO #TempTable ([IdSystemEvent],[CodeEventType],[Name],[Message],[Created],[CodeProject],[Hash],[Subject],[UserAgent]) SELECT TOP 30101 [LPLA_1].[IdSystemEvent], [LPLA_1].[CodeEventType], [LPLA_1].[Name], [LPLA_1].[Message], [LPLA_1].[Created], [LPLA_1].[CodeProject], [LPLA_1].[Hash], [LPLA_1].[Subject], [LPLA_1].[UserAgent] FROM [SystemMonitor].[dbo].[SystemEvent] [LPLA_1] ;
SELECT [IdSystemEvent],[CodeEventType],[Name],[Message],[Created],[CodeProject],[Hash],[Subject],[UserAgent] FROM #TempTable WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC;DROP TABLE #TempTable
    Parameter: @__rownoStart : Int32. Length: 4. Precision: 10. Scale: 0. Direction: Input. Value: 30000.
    Parameter: @__rownoEnd : Int32. Length: 4. Precision: 10. Scale: 0. Direction: Input. Value: 30100.

Is there some way to increase performance of LLBL paging (maybe to force query generator not to use temporary tables)?

What is the difference between Take/Skip and TakePage?

What SQLServer version? LLBLGen build and runtime library version?

For paging, use TakePage, to make sure the query pages correctly (as llblgen pro doesn't support skipping, so if you specify a skip value which isn't a multiply of the take value, the paging doesn't work)

As you're using sqlserver 2005 I think (according to the linqto sql query), you should set the sqlserver DQE to compatibility mode 2

You're using the default, which is compatibility mode 1, and which uses a temptable. So the queries are not equal: the temptable approach llblgen pro uses can work very well, but if your tempdb database is very small on disk, and the set is big, the tempdb has to resize on disk which takes time.

As you're using sqlserver 2005 I think (according to the linqto sql query), you should set the sqlserver DQE to compatibility mode 2

Yes, my fault, I suspected it, but wanted to try it after weekend. (You are just too fast with answers for me ).

So now LINQ2LLBL is equally fast as LINQ2SQL Problem solved.

FYI: I want to use LLBL with Dynamic Data and as for now it looks promisingly. I will inform you about my progress.