Entity Framework 6 can't work with large data

Question

I'm having a trouble about working large data with entity framework 6. When I try to get data from database it gives timeout error. I'm just including 2 extra tables to my query. I mean I'm joining 3 tables and one of them is empty for now. (ORDERS, ORDER_DETAILS, PEOPLE)

ORDERS table has 385K records and PEOPLE has 400K of records. I'm also not getting all records. I'm taking just 25 records for one page. (I'm using paging.)

Here is my sample code. After 15 seconds it gives timeout error and when I get the tsql query from Sql Profiler it works but that takes 37 seconds.

// skipCount is integer and it changes with page number.
// pageSize is fixed integer number which is 25.
// I also set timeout about 180 seconds
var list = _uow.Repository<ORDERS>()
        .Query(x => x.ACTIVE == true)
        .Include(x => x.ORDER_DETAILS)
        .Include(x => x.PEOPLE)
        .OrderBy(sorting)
        .Skip(skipCount)
        .Take(pageSize)
        .ToList();

Also if I use LinqToSql query that works very fast.

var list = (from o in _context.ORDERS
           join od in _context.ORDER_DETAILS on o.ID equals od.DETAIL_ID
           join p in _context.PEOPLE on o.CUSTOMER_ID equals p.ID
           select ab)
        .OrderBy(sorting)
        .Skip(skipCount)
        .Take(pageSize)
        .ToList();

How can I solve this performance issue? Or where is my mistake?

Answer 1

I use this sql code to rebuild all indexes and my problem is fixed now. Thanks for help.

USE [DATABASE_NAME]
EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"