Generated SQL

[matt.downing]

Hi,
I sent this to support, thought I'd try the forum as well!

I'm firing two queries against a database with 200k rows using EF and dotConnect. I'm trying to introduce paging into my app. The second query is what I'm trying to do; the first I simply include for reference. It is missing the Skip() chain method of the second.

First:
Message[] newMess4a = dataStore.Messages.OrderBy(m => m.Id).Where(pred).Take(10).ToArray();

Second:
Message[] newMess4 = dataStore.Messages.Where(pred).OrderBy(m => m.Id).Skip(10).Take(10).ToArray();

The first query takes 0.01 seconds. The second takes 11 seconds.

[Note: I know the Order() and Where() methods are different ways round. Having them the same makes no identifiable difference to the timings, and if anything the second query has it the more effiecient way.]

Here's the ToTraceString of the first:
SELECT
top.STATUS_VALUE AS STATUS_VALUE,
top.ID AS ID,
top.TRANSACTION_ID AS TRANSACTION_ID,
top.BUSINESS_ID AS BUSINESS_ID,
top.QUEUE AS QUEUE,
top.ENVIRONMENT AS ENVIRONMENT,
top.DATE_CREATED AS DATE_CREATED,
top.DATE_PROCESSED AS DATE_PROCESSED,
top.DATE_INSERTED AS DATE_INSERTED,
top.SOURCE_SYSTEM_ID AS SOURCE_SYSTEM_ID, top.TARGET_SYSTEM_ID AS TARGET_SYSTEM_ID FROM ( SELECT Extent1.ID AS ID, Extent1.TRANSACTION_ID AS TRANSACTION_ID, Extent1.BUSINESS_ID AS BUSINESS_ID, Extent1.STATUS_VALUE AS STATUS_VALUE,
Extent1.QUEUE AS QUEUE, Extent1.ENVIRONMENT AS ENVIRONMENT, Extent1.DATE_CREATED AS DATE_CREATED, Extent1.DATE_PROCESSED AS DATE_PROCESSED,
Extent1.DATE_INSERTED AS DATE_INSERTED, Extent1.SOURCE_SYSTEM_ID AS SOURCE_SYSTEM_ID, Extent1.TARGET_SYSTEM_ID AS TARGET_SYSTEM_ID,
Extent1.HEADER AS HEADER, Extent1.PAYLOAD AS PAYLOAD, Extent1.EXTENDED_INFO AS EXTENDED_INFO
FROM MW_ARCHIVE.MESSAGES Extent1
WHERE Extent1.STATUS_VALUE > 0
ORDER BY Extent1.ID ASC
) top
WHERE ROWNUM 0
ORDER BY Extent1.ID ASC
) Filter1
WHERE Filter1.row_number > 10
ORDER BY Filter1.ID ASC
) top
WHERE ROWNUM <= 10


Firstly, what's going wrong here, how can retrieving 10 records take 11 seconds just because of a skip()?

Secondly, clearly paging will be working for other people (in less than 11 seconds). What is the recommended approach?

Cheers
Matt

[AndreyR]

Thank you for the report.
We have reproduced the performance difference, however, not in 1000 times.
I will post here about the results of our investigation.

[matt.downing]

I repeated the tests again (having run them quite a few times before mailing support).
The database now has 400k records.
This time instead of going via EF, I used Rapid Sql to fire the SQL generated by the two requests into the database. The first (no skip) query takes less than a second. The second query now takes 20 seconds.

[AndreyR]

We have almost doubled the performance in case of Skip() and Take() usage.
The fixed build is available in Registered Users' Area (for registered users only), and here (trial version).