0

Can anyone please suggest possible reasons for a sql query timeout while debugging in Visual Studio, considering that the same query always runs as expected in SSMS, the same in VS without debugging as well as while running installed desktop application.

The sql query is quite long so giving a simplified example.

    SELECT t1.field1, t2.field1, t3.field1, t4.field1 FROM 
    table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id INNER JOIN 
    table3 t3 on t1.id = t3.id INNER JOIN table4 t4 ON t1.id = t4.id 
    WHERE t3.char = ‘N’ AND t4.id2 = 1

The query only pulls about 500 records so the usual execution time is under 1s. I had the sql string excecuted from a variable, also tried to adjust the program to run it from a stored procedure and a table function, all three options result in the same timeout in VS while using debugging mode only.

When the problem occurs, the sql execution queue is blocked and unless I stop the debugging mode and sometimes even having to restart VS, no sql commands can complete in SSMS.

The C# code is

   using (IDbConnection db = new SqlConnection(myconnection))
        {
            return new ObservableCollection<MY_INFO>(db.Query<MY_INFO>
           ("exec sp_mysp @MY_ID", new { MY_ID = myid }, commandTimeout: 60).ToList());
        }
RWo
  • 56
  • 5
  • What do you use to get the data ( ORM , ADO etc ) ? – Eugene Oct 05 '18 at 13:00
  • Thanks, it’s Dapper, will update the question with some more info – RWo Oct 05 '18 at 13:06
  • So it always fails in DEBUG mode, even without any breakpoints? – stuartd Oct 05 '18 at 13:21
  • I would say it mostly it fails in debugging mode, so even more puzzling is that it occasionally executes. It always executes in live app version, without debugging mode or in SSMS. There are no breakpoints. – RWo Oct 05 '18 at 13:31

1 Answers1

0

The following thread has helped me resolving the issue Query times out from web app but runs fine from management studio.

I changed the following settings to match sql server logins that differ from front end user logins and settings required by the front end application.

From:

set arithabort off
set language us_english  
set dateformat mdy  
set datefirst 7

To:

set arithabort on
set language uk_english  
set dateformat dmy  
set datefirst 1

This resolved the issue for most part, still having occasional delays. In the end I decided to switch from VMWare Win 10 VM onto Mac Pro Docker using this guide, all runs now as expected.

Hope this helps someone.

RWo
  • 56
  • 5