Geek asks and answers

Entity model.net requests 1 million records from MySQL performance issues

I use ADO .Net Entity Modelto query a MySQL database. I was very pleased with its implementation and use. I decided to see what happens if I request 1 million records and you have serious performance problems, and I don’t understand why.

The system sometimes freezes, and then I get either

  • Blocking exception
  • MySQL exception

My code is as follows:

      try
        {
            // works very fast
            var data = from employees in dataContext.employee_table
                            .Include("employee_type")
                            .Include("employee_status")
                       orderby employees.EMPLOYEE_ID descending                            
                       select employees; 

            // This hangs the system and causes some deadlock exception
            IList<employee_table> result = data.ToList<employee_table>(); 

            return result;
       }
       catch (Exception ex)
       {
            throw new MyException("Error in fetching all employees", ex);
       }

My question is: why does ToList () take such a long time?

Also, how can I avoid this exception and what is the ideal way to request a million records?

+3
source share
1 answer

- IQueryable<T>, , , . , .

, , , MySQL , EMPLOYEE_ID, , . , , . MySQL, , -.

, var data , , , . ToList(), SQL SQL. , Lazy Loading.

:

        var data = from employees in dataContext.employee_table
                        .Include("employee_type")
                        .Include("employee_status")
                   orderby employees.EMPLOYEE_ID descending                            
                   select employees;

, - ,

data.Where(/* your filter expression */).ToList()

, 10.

var employee = data.Where(e => e.ID == 10).ToList();

, , S ( , , ).

var employees = data.Where(e => e.LastName.StartsWith("s")).ToList();

, 100

var employees = data.Skip(page * 100).Take(100).ToList();

, ToList() , . , , , , A

 var salaries = data.Where(s => s.LastName.StartsWith("A"))

 foreach(var employee in salaries)
 {
     salaryTotal += employee.Salary;
 }

, :

Select Salary From EmployeeTable Where ID = @ID

, , , , .

- . , , , , , , .

, , .

int ChunkSize = 100; //for example purposes
HashSet<Employee> Employees - new HashSet<Employee>;

//Assuming it exactly 1 Million records

int RecordsToGet = 1000000;

for(record = 0; record <= RecordsToGet; record += ChunkSize)
{
    dataContext.EmployeeTable.Skip(record).Take(ChunkSize).ForEach(e => HashSet.Add(e));
}

HashSet<T>, , , 1 000 000 .

+11

More articles:


All Articles