0

I am writing a server backend for an android app which needs to access a database. My backend application and the SQL server are on the same machine so I don't have to worry about traffic slowing down my application but I don't know how to get user data from the database in the most efficient way.

When a new user connects to the server, a new thread is started for managing that connection. The client sends a few packets for identification but eventually all clients need some online data every t seconds. This t is the same for every client. The clients don't need to request this data every t seconds, instead the server sends it to them. The data itself is on a SQL server and is updated every few seconds.

Now I want to know which one is better (for CPU performance - both my application and the SQL server itself):

  1. Create a new connection to the SQL server for each thread and let the thread handle it.

  2. Create only one connection to the SQL server and instead have a list of clients that need online data. Get all the needed data form the SQL server every t seconds and then distribute that data between threads so that they can send it to the client.

There will be at most 300 clients.

EDIT: To clarify, I'm writing my application using C++ and using SQLAPI++ for database connections and I'm not sure if this library actually uses connection pooling.

Still, even if I leave the connection management to the library, the question is: Should I let each thread execute its own commands or have one thread execute them all at once in the form of one command? Does it have a significant impact on performance? (my application or the SQL server)

SMMB
  • 107
  • 6
  • 4
    Why are both of your options "keep (a/many) connections open for a long time"? Usually you should prefer to Open/Use/Close each connection as close to the point of use as possible. Let the connection pool work out how many actual connections are required behind the scenes. – Damien_The_Unbeliever Jan 26 '21 at 08:14
  • Does this answer your question? [C# Data Connections Best Practice?](https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice) – Charlieface Jan 26 '21 at 09:40
  • *DON'T* use global long-lived connections at all. Connections accumulate locks as long as they're open, which means long-lived connections end up blocking each other. Even in databases with multi-version concurrency, long-lived connections result in extra IO to maintain versions of data that are changed by other connections – Panagiotis Kanavos Jan 26 '21 at 15:25
  • All major databases and drivers support connection pooling - instead of having one global connection that needs to be shared by every thread, the program opens and closes connections as needed. Instead of actually closing the connections though, the driver resets them and puts them in a connection pool, ready to be reused the next time the application tries to open a connection. This means that even multi-threaded code (eg busy web sites) eventually use far fewer concurrent connections – Panagiotis Kanavos Jan 26 '21 at 15:28
  • This means that 300 clients could easily make do with 10 connections or even less, depending on what they actually do. As for threading, database calls are IO so they don't use client threads. If you use your library's async features you'll be able to talk to the database without blocking or wasting CPU cycles while spinwaiting – Panagiotis Kanavos Jan 26 '21 at 15:32

0 Answers0