Accessing an MS-SQL database via ADO.Net in a WPF C# application

[WojtexDeveloper]

Hello.
I've created a large WPF application in C#, where I work with an MS-SQL database using ADO.Net controls.
I'm using the System.Data.SqlClient library. I read and write to the database by calling stored procedures. These procedures are large and heavy, containing several subqueries, temporary tables, variables, and always returning data via SELECT.
For some time now, I've been having connection stability issues. I have a large form that's primarily used by the user with a large amount of data.
As long as the application has frequent user interaction (the user frequently clicks on individual options to call stored procedures from the database), everything runs smoothly. However, if the user stops working in the application, a significant slowdown in procedure execution is noticeable after a few seconds. When there's continuous work, the execution time of the heaviest procedure is less than 1 second. After this slowdown, it's a few seconds. This happens with all called procedures. The second time, the called procedure responds quickly. I have the impression that when there's no user interaction, .Net puts the connection to sleep. Is this possible?
It looks like a "change start" because the application then behaves as if it were starting. The first call to the stored procedure is then slower than the next.
I run all my queries on a single shared connection, which I open at application startup.
Has anyone else had a similar problem?
Where can I find a solution to ensure that the procedures always run quickly, regardless of whether there's any activity in the application or not?

 

[Skydiver]

Not directly answering your question: ADO.NET is expecting to be used where you open and close connections all the time. Whenever possible, ADO.NET will do connection pooling for you. It's not like the old database connections when you were programming in C/C++ where it's your job to keep the connection open through the lifetime of application. In C#, the expectation is to open the connection, do your unit of work, and then close the connection.

To partially address your question:
It's quite possible that the SQL server is configured to timeout a connection on its side when it sees inactivity. Very likely, ADO.NET has some retry logic when you try to use an already open connection, but the server on the other end has timed out the connection. Also look closely at your connection string parameters because you also have some control with regards to timeouts. You might need to talk to your DBAs to see how the server is configured.

Some of the speed you are seeing where you perform a query and it takes a long time, and then is fast when run very soon afterwards is SQL server caching and preserving results. As I recall, the connection string has some control over this as well. Again, ask your DBA regarding any optimizations they may have enabled on their end.

 

[Skydiver]

Not directly related to your question, but have you considered doing you database operations on another thread, or asynchronously? That way the WPF UI is not frozen whenever there is a long query that is happening.