dotnet / dotnet/efcore

Parameterized Offset/Fetch makes query slower

Open
#28,632 13 comments 1 reaction 0 assignees View on GitHub
area-perf area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

## scenario

Recently I created an column store index to improve the performance. It was fine if I directly specify the input of Offset/Fetch. However, I found that when I executed the sql generated by EF which is parameterized, the performance got almost 10 times slower than the former. It could be a disaster if there are extra entities to join.

I wonder if there is an solution for my scenario or maybe is that an opportunity for EF? The Skip()/Take() only accept the int parameter, so I guess it is relatively safe and might be unnecessary to parameterize the input for Offset/Fetch.

## samples

**Parameterized Offset/Fetch with 1 row**
![image](https://user-images.githubusercontent.com/58463695/183619947-40066d8b-39c9-48e7-bff9-1d1e6c0c94aa.png)
![image](https://user-images.githubusercontent.com/58463695/183620036-85d85e54-0bf9-4006-9086-1d7f1b9a1109.png)

**Non-parameterized Offset/Fetch with 1 row**
![image](https://user-images.githubusercontent.com/58463695/183619158-a42ada98-ea38-4561-96a0-c9ea68578bc8.png)
![image](https://user-images.githubusercontent.com/58463695/183619245-2b2d1c58-2538-43b0-8deb-474117bffed2.png)

**Non-parameterized Offset/Fetch with 900 row**
![image](https://user-images.githubusercontent.com/58463695/183619548-edbd20ca-cdf4-48f1-b4d3-fd1b9092269b.png)
![image](https://user-images.githubusercontent.com/58463695/183619649-8f65326c-bf07-4b15-ba77-6962088a96be.png)

## version information

EF Core version: 3.1
Database provider: SQL Server in Azure

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.