Telerik Linq Extremly Slow When Using Query with OrderBy

Question

I am having trouble managing large dataset with linq and telerik model, I cant figure out the problem or how linq is working to execute the query.

I am queryng the database using linq to a 300000+ records and it seems that linq is executing the query before applying the take and skip paramenters.

I am executing this query using linq:

var result = repository.Documents.Where(p => p.TenantId = 1 && p.TipoDocumento == SriDocType && p.RucReceptor == ruc).OrderByDescending(p => p.FechaEmision).Take(20).Skip(0).ToList();

Then I run the same query by using sql:

var result = ((EddocumentRepository)repository).Model.ExecuteQuery<Riverminds.ShardLayer.Eddocument>("SELECT TOP 20 * FROM eddocuments WHERE TenantId = 1 and TipoDocumento = 1 and RucReceptor = '0990017514001' Order By FechaEmision Desc", (new List<System.Data.Common.DbParameter>()).ToArray()).ToList();

On the first Query I get a timeout exception, it takes more than a minute the query, if I change the maxexecutiontime, it will work but will take like 2 minutes.

Now If I run the second query that basically is the same thing but with sql text, it takes just a second or 2 seconds. It is really crazy but it is happening, and I need to use linq because I am working with Kendo Asp Net Mvc and using the ToDataSourceResult that is the same thing than linq. It takes a lot of time executing the query.

Any Idea?.

UPDATE

Doing some Linq Queries with the help of the posted comments, I can figure out that the problem is the paramenters, when I use "ruc" paramenters the LINQ query throw a timeout exception because it takes a lot of time to execute it, The same query with SQL takes 1 second.

Deleting the ruc condition, with Linq takes the same time as SQL, 1 second, I check mappings and it seems ok, the column is a 255 nvarchar nullable, so I think something is wrong with the parameter ruc. I am posting the mapping detail of the column and the linq and sql that takes 1 second.

configuration.HasProperty(x => x.RucReceptor).HasFieldName("_rucReceptor").WithDataAccessKind(DataAccessKind.ReadWrite).ToColumn("RucReceptor").IsNullable().HasColumnType("nvarchar").HasLength(255); 

var result = repository.Documents.Where(p => p.TenantId = 1 && p.TipoDocumento == SriDocType).OrderByDescending(p => p.FechaEmision).Take(20).Skip(0).ToList();

var result = ((EddocumentRepository)repository).Model.ExecuteQuery<Riverminds.ShardLayer.Eddocument>("SELECT TOP 20 * FROM eddocuments WHERE TenantId = 1 and TipoDocumento = 1 Order By FechaEmision Desc", (new List<System.Data.Common.DbParameter>()).ToArray()).ToList();

Thanks for your Help

Santiago Munoz

Answer 1

This difference in the query speed usually is due to one of the following:

1. The generated SQL statement is not the most efficient, in your case I don't believe it is the case as your query is pretty straight forward. However you could inspect the generated SQL statement by executing

   string sql = repository.Documents.Where(p => p.TenantId = 1 && p.TipoDocumento == SriDocType && p.RucReceptor == ruc).OrderByDescending(p => p.FechaEmision).Take(20).Skip(0).ToString()
   

2. There might be parameter type mismatch causing implicit type conversion at server side that prevents the SQL server to utilize the existing indexes. The usual suspects are "string" type of properties. From your examples I see this: RucReceptor = '0990017514001'. Check the mapping for this column if it is Unicode but in the db is varchar this definitely will affect the performance negatively. Fix the types in the mapping to correspond to those in the DB and it should run fast.

Hope this helps.