Perfect design for winforms connecting to SQL Server.

I have a winforms application that does the following:

for each request:

  • Db connect
  • Db query
  • Disable Db

as we try not to open a db connection. This suffers from performance as we have to connect / disconnect every time.

What is the idealized model here to have the fastest performance but not having an open connection for too long. Is there a best practice here?

+1


source to share


3 answers


The most efficient method is pooling, and ADO.NET does this automatically for you until you close your connections.Just wrap the objects SqlConnection

in statements using

and you don't have to worry about it.



+4


source


There is very little overhead in connecting / disconnecting with connection pooling. Your bottleneck is most closely related to database queries, so leaving the global connection open won't help.



If your queries are too slow, try optimizing them. If that doesn't work, you'll be looking for caching, asynchronous fetching, or denormalization. There really isn't a generic use case I can think of to support DB join.

+1


source


Maybe try to think about caching your data? My own crude tests have shown that a query on memory data can be up to 20x faster than a query on the same data from a database. While this approach may not suit your schema or your data needs, it's always worth looking at caching frequently accessed data somewhere if you can.

0


source







All Articles