1

I am using the following LINQ query using EF Core and getting the following error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Not sure what I am doing wrong here.

ECUserId is a varchar column and there is no index set on this table for this column. Also checked the collation and it is set to 'SQL_Latin1_General_CP1_CI_AS' which is case sensitive.

string ECUserId = "TN504060"
var userStandby = await _context.UserStandby
                                .Where(standBy => ECUserId.Equals(standBy.ECUserId, 
                                       StringComparison.OrdinalIgnoreCase))
                                .FirstOrDefaultAsync();

The userStandby table has got around 126580 records.

Can anyone help me fix the above timeout error?

Created an index on this column but still getting the same timeout error:

GO
CREATE NONCLUSTERED INDEX [IX_UserStandby_ECUserId]
    ON [dbo].[UserStandby]([ECUserId] ASC);

The LINQ query is not generating the SQL query with the where condition. What am I doing wrong here please?

Thanks.

d219
  • 2,707
  • 5
  • 31
  • 36
Mukil Deepthi
  • 6,072
  • 13
  • 71
  • 156
  • 1
    Analyze SQL and execution plan. Maybe SQL is ineffective or table has no index on `ECUserId` – Svyatoslav Danyliv Jun 10 '22 at 09:42
  • 1
    Why are you comparing two *ID*s by upper casing them? The generated SQL won't be able to use any indexes. If you have a large table the server will have to check every single row. To fix this remove `ECUserId.Equals` and use `standBy.ECUserId = thatID`. Make sure the column's collation is case insensitive *or* ensure the strings are always converted to uppercase when saved. – Panagiotis Kanavos Jun 10 '22 at 09:46
  • Is `ECUserId` the key of `UserStandby`? In that case you could use `.Find(thatId)` and load the object directly – Panagiotis Kanavos Jun 10 '22 at 09:49
  • @PanagiotisKanavos i have updated my question. please have a look. ECUserId is not the primary key. it is one of the column in that table and there is no index set so far – Mukil Deepthi Jun 10 '22 at 09:53
  • Add an index then. Otherwise your query will have to read and check all 100K rows. That's disk IO for 100K rows and CPU to compare 100K rows. *And* a lock on the entire table – Panagiotis Kanavos Jun 10 '22 at 09:54
  • A full table scan will have to lock the entire table for reading. To do that it will have to wait for all update (exclusive) locks to be released – Panagiotis Kanavos Jun 10 '22 at 09:57
  • @PanagiotisKanavos thanks. so adding an index will solve my issue? is that right. I am new to indexing :). what kind of index i need to add? Thanks – Mukil Deepthi Jun 10 '22 at 10:13
  • @PanagiotisKanavos the above query in query plan not showing the where condition. not sure why? – Mukil Deepthi Jun 10 '22 at 10:49
  • Which EF Core version are you using? EF Core 3 and later would throw an exception if the couldn't convert an expression to SQL. Older (unsupported) ones would load the results in memory and *then* apply the expressions. Your code may be loading the entire table into memory so it can compare the string field with the argument – Panagiotis Kanavos Jun 10 '22 at 10:59
  • You need to add an index *in the database table **and** remove `Equals` – Panagiotis Kanavos Jun 10 '22 at 10:59
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/245485/discussion-between-mukil-deepthi-and-panagiotis-kanavos). – Mukil Deepthi Jun 10 '22 at 11:00
  • Please try given exp: `string ECUserId = "TN504060"; var userStandby = await _context.UserStandby .Where(standBy => standBy.ECUserId.Equals( ECUserId , StringComparison.OrdinalIgnoreCase)) .FirstOrDefaultAsync();` – Hiren Patel Jun 10 '22 at 11:15
  • @HirenPatel that's what caused the problem in the first place and no different than the OP's code. Changing the argument order won't remove the need to either emit `UPPER` or `COLLATE` in the query, preventing the use of indexes. – Panagiotis Kanavos Jun 10 '22 at 13:00
  • You say "the LINQ query is not generating the SQL query with the `where` condition" - what SQL is it generating? – d219 Jul 25 '23 at 12:33

1 Answers1

0

You can set a longer timeout when setting up your DbContext:

        services.AddDbContext<MyDbContext>(config =>
          config.UseSqlServer(
             "MyConnectionString",
             providerOptions =>
             {
                 providerOptions.CommandTimeout(180); <-- Timeout in seconds
             })
        );
ShanieMoonlight
  • 1,623
  • 3
  • 17
  • 28