Are there performance/other downsides in creating a new RJDBC connections to MS SQL database for each request?

Many Questions:

1) Reusing a connection is faster then establishing a new connection for every use. Depending on your code, this will speedup your application a little bit. But reusing connections is more complex. Thats the reason why many people use connection pools.

2) If your program has a short runtime you can work with one connection, e.g. in a global variable. If your application is a server application (long running), than you need to maintain your connection because the server can close the connection, if he thing that nobody use it because there runs no traffic over the connection. This could happen in the night times on server applications. The connection maintenance function is part of connection pools.

Summary.If your application a simple, not multi threaded, not server application, than reuse your single connection. Otherwise, use every time a new connection or use a connection pool.

It might help to consider what happens behind the scenes every time you establish a connection:

• A TCP/IP connection has to be established (including DNS lookup and contacting the SQL Server Browser to get the correct port number for a named instance)
• The user needs to be authenticated and verified to be authorized to connect
• Server side resources for the connection (private memory etc.) have to be reserved

Therefore it makes sense to limit the amount of connections used by your application.

If your application executes all transactions in sequence you should open the connection once and reuse it. Use a connection pool for a server-based multi-user application.