-1

The problem is that I'm getting a timeout error while querying the database in a for loop to fetch paginated set of records. Each loop is not expensive, but querying for more than 30 seconds using the same connection object gives the transaction/timeout error :

I've tried adding commandTimeout property for Dapper query, and Connection Timeout in app.config (both being above 30 seconds), but it doesn't affect anything. 

int pages = totalCount / pageSize;
for(int pageNumber = 1; pageNumber <= pages; pageNumber++){
  string sql = @"select * from MyTable order by 1 desc offset @pageSize * (@pageNumber-1) rows fetch next @pageSize rows only;";
 _connection.Query<MyEntity>(sql, param: new {pageSize, pageNumber}).ToList();
}

The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements

Dale K
  • 25,246
  • 15
  • 42
  • 71
afrose
  • 169
  • 1
  • 9
  • On a side note, why aren't you calculating the offset outside the query and pass that value directly as a parameter...this looks a bit unusual to me... – gkb Jul 30 '19 at 05:21
  • You say each query is not expensive, how are you going to prove that to us? can you show us the query plan – TheGeneral Jul 30 '19 at 06:10
  • @TheGeneral its because if I change my page size to a larger number say 10k, the number of times the loop will execute is less and hence no timeout, decreasing the page size leads to timeout – afrose Jul 30 '19 at 06:28
  • I would suggest you to put this query into a parameterised stored procedure and set the isolation level to read uncommitted, that in my opinion would give you a better picture of what's going on.. – gkb Jul 30 '19 at 08:14

1 Answers1

0

It turns out that there was a wrapper dll from an external library on top of the database layer that was causing this issue:

public static readonly TimeSpan DefaultTransactionTimeout = TimeSpan.FromSeconds(30);

Overriding this behaviour fixed the issue.

afrose
  • 169
  • 1
  • 9