0

My code

var numberOfStudents = _context.Student.Count();

In the database , about 4.000.000 records. I tried

var numberOfStudents = _context.Student.Count();

but "Timeout expired" error still occur. I setted command timout to 5 mins. But this error still occur.

Cuong_BG
  • 11
  • 3

1 Answers1

0

Please run this query and tell me how long does it take to execute

Select Count(*) from Students

Also, please post your table structure, including all the Indices. I assume adding a suitable index on the table will solve the problem, but I need more information. Most probably, adding any NonClustered Index will make this fast. Please see SQL count(*) performance.

Community
  • 1
  • 1
Alireza
  • 5,421
  • 5
  • 34
  • 67
  • Thanks for your helping . I runned [Select Count(*) from Students] but It taked a lot of time. I tried add NonClustered Index for all colume but it still error. Also , I resolved problem of count command but. This time error when I call [_context.Students.Skip(Offset).Take(Limit);] – Cuong_BG Mar 31 '16 at 06:22
  • How did you resolve the problem of Count? How many rows are you trying to Skip() and Take()? What is the order of students? Do you have a useful index for this query? – Alireza Mar 31 '16 at 09:49
  • I used http://stackoverflow.com/questions/11130448/sql-count-performance that you suggested above to resolve Count command. Skip() and Take() any value , error still occur. Before skip and take , i runned _context.Students..OrderBy(birthday); . What is the "a useful index for this query" . NonClustered Index ? – Cuong_BG Apr 01 '16 at 08:15
  • As you `OrderBy(birthday)`, an index on Birthday is expected to be there, so that SqlServer will not need to search all `student`s. Please read [a tutorial on indexing](http://www.sqlservercentral.com/articles/Indexing/68439/) – Alireza Apr 01 '16 at 16:22
  • Ok. When I run the command SELECT [Extent1].[StudentId] AS [StudentId] FROM [dbo].[Students] AS [Extent1] LEFT OUTER JOIN [dbo].[StudentEvents] AS [Extent2] ON [Extent1].[FirstEventId] = [Extent2].[EventId] ORDER BY [Extent2].[EventDate] ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY , timeout exprired will occur. Run it on SQLserver taked a lot of time,too. Clustered index of Students table is StudentName. Nonclustered index is FirstEventId. So , do you have any way to fix this problem? – Cuong_BG Apr 04 '16 at 09:04
  • The indices you mentioned are a bit unexpected, and unless you know exactly why you need them, you better have the primary key of the table I.E. `StudentId` as the Clustered Index. Also, you need an index on `EventDate` (as sqlserver needs to sort on that column) and most probably a non-clustered index on `FirstEventId` (which you already have and will be used to find students by their FirstEventId) please create the indices and tell me if that improves the query speed. – Alireza Apr 04 '16 at 15:47
  • As I told you, you need to study more about Indices. No one can tell you what indices you need without proper knowledge of your exact requirements, and without testing different approaches on actual data. – Alireza Apr 04 '16 at 15:50
  • Thanks for your helpping . I created index for EventDate by :CREATE INDEX DEMO ON StudentEvents(EventDate). But query speed still slow. It seem so difficult. – Cuong_BG Apr 05 '16 at 02:14
  • Sory. StudentId is the Clustered Index – Cuong_BG Apr 05 '16 at 02:22
  • Please post the script of Table and index definitions, along with the query and its [execution plan](http://sqlmag.com/t-sql/understanding-query-plans) – Alireza Apr 05 '16 at 16:06