Local DB Connection quit after Windows and VS Update

Question

Current version: Microsoft Visual Studio Professional 2019 Version 16.5.4

I have a local desktop inventory app, I wrote in C# a few months ago. Everything has been just fine, but today, Windows update and Visual Studio 2019 update popped up so I went ahead. Now I "RANDOMLY" get errors connecting to the local DB. Meaning some times it works, launch the app again and it fails to connect to the DB.

Error:

Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=16063; handshake=8123;

Connection String (I auto replace the |DataDirectory| with the path of the application):

Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\MYDB.mdf;Integrated Security=True

I have to add ";Connect Timeout=30" to the end of the connection string, then the app takes almost 20 seconds to connect to the DB. The DB only has 3 tables, with no more than 40 records in each table. Anyone else have this issue before? I'm not going to accept the application taking 20 seconds to connect to the DB, which is local to my windows 10 machine.

EDIT: I know there will be people asking how am I connecting. It's in a larger method created for accessing any kind of MS DB.

//set connection
SqlConnection connection = new SqlConnection(ConnectionString);
//set command
SqlCommand command = new SqlCommand()
{
    Connection = connection,
    //even though it's set to procedure, the Microsoft.Security 
    //will still have warnings, because the proc name is passed 
    //in, instead of a constant.
    CommandText = storedProcedure,
    CommandTimeout = CommandTimeout
};

//if the proc has parameters, pass them.
if (param != null)
{
    command.CommandType = CommandType.StoredProcedure;
    for (int i = 0; i < param.Length; i++)
    {
        param[i].ParameterName = Clean(param[i].ParameterName, CleanType.UnquotedString);
        if(param[i].DbType == DbType.String)
            param[i].Value = Clean(param[i].Value.ToString(), CleanType.UnquotedString);

        command.Parameters.Add(param[i]);
    }
}
else
    command.CommandType = CommandType.Text;

//open connection
connection.Open();  //<---- This is what Times Out

Run Process Monitor and see if there's anything going on during the delay? — Ben Voigt, Apr 16 '20 at 22:30
The delay is every time it connects for the first time. After it connects, there is no issue. First launch. That's never been a problem and code hasn't changed.. — Chizl, Apr 16 '20 at 22:35
Did you try searching the error message `The timeout period elapsed while...` ? Lots of links come up, e.g: https://stackoverflow.com/questions/15488922/connection-to-sql-server-works-sometimes — Loathing, Apr 17 '20 at 11:34

score -1 · Accepted Answer · answered Apr 16 '20 at 22:36

-1

It sounds like LocalDB is the wrong tool for your job. From the docs:

When connecting, the necessary SQL Server infrastructure is automatically created and started

That sounds really expensive and slow.

For your small dataset you probably want an in-process db engine such as SQLite. Microsoft's in-process database (whose support lifecycle is almost ended) is named "SQL Server Compact" and is not the same thing as "SQL Server Express LocalDB".

answered Apr 16 '20 at 22:36

Ben Voigt

277,958
43
419
720

It's during the connection, how does data have anything to do with it? It hasn't attempted to retrieve the data when it timesout. – Chizl Apr 16 '20 at 22:54
@Switch: "I can put this same information in SQL Server remotely and it's not a problem". If the remote instance of SQL Server wasn't started in advance yes it would be a problem. "I'm using the standard DB that comes with Visual Studio, why would I want to install some other DB for that" Visual Studio is a resource hog. Just because it uses LocalDB for its own Intellisense operations doesn't make that a good choice for you. – Ben Voigt Apr 16 '20 at 22:54
I changed up my response. This is during the connection, so DATA has nothing to do with it. Plus this just started today, with no code changes, after a VS Upgrade. – Chizl Apr 16 '20 at 22:56
"how does data have anything to do with it?" Lightweight database engines will solve your startup time problem. But if you had 15 TB of data I wouldn't recommend one. – Ben Voigt Apr 16 '20 at 22:56
The size of the data is only relevant to the relative performance hit you take loading a database engine. Loading 400 MB of MSSQL engine code is a lot of overhead for a 0.5 MB database. For a 15TB database, it's the same overhead, but a much smaller fraction. – Ben Voigt Apr 16 '20 at 22:58
I get that, but why would that start having a problem today? – Chizl Apr 16 '20 at 23:00
This is a 8MB MDF sitting on a Win10 SSD M2 HardDrive. Speed should not be a problem connecting to this file. I can load and parse XML faster. – Chizl Apr 16 '20 at 23:02
Well, this DB thing has pissed me off enough to pull in EPPlus and as it pulls from the DB, it saves off to Excel. Each table is a new tab. I can save, read an Excel Document faster than connecting and pulling from an MDF. Something seriously wrong with this world and how things are changing. – Chizl Apr 18 '20 at 18:08
"Speed should not be a problem connecting to this file" That's kind of my point. With an in-process DB engine, you only pay to access the database file. With an out-of-process DB engine like SQL Server Express, you pay for the time to startup the SQL Server service processes. Now I have no idea why that's taking tens of seconds so that the default timeout isn't enough, but even if you fix whatever Windows update did you're only making it less slow, not fast. From 5000 times longer than necessary to only 150 times is a big improvement, but still 150 times slower than necessary. – Ben Voigt Apr 19 '20 at 19:32
I ended up changing it again, just because, reading and writing from Excel, that just seems like a lot of overhead. Ended up writing my a JsonData class, that I can read and write through DataSets which creates tables and columns through it, On load, reads the JSON into memory and runs off of DataSets. Very fast and easier to use that even SQL Connections. Run create table, delete table, selects, inserts, updates, deletes, sort, and all. I'm pretty happy with myself. Might even open source it for others to use/edit as needed. – Chizl Apr 25 '20 at 21:20
@Switch: "Might even open source it for others to use/edit" Congratulations, you've reinvented what I recommended you switch to in the first place (a lightweight in-process database) of which many open source examples exist (I listed SQLite) which are much better tested and debugged than yours, as well as more efficient in use of disk space and memory, and have many more features implemented. Before sharing your version with anyone else, you should at least be aware of what projects it's competing with. – Ben Voigt Apr 27 '20 at 01:40

Local DB Connection quit after Windows and VS Update

1 Answers1