Keep Sql Connection open for iterating many requests? Or close each step?

Question

Hey there -- this is general to any operation calling an SQL server, or anything requiring an open connection at that.

Say I have anywhere from 20 to 1000 Select calls to make for each item in data being looped. For each step, I'll select from sql, store data locally in a struct, then proceed. This is not a very expensive call, so should I keep the connection open for the entire loop? Or should I open and close every step?

How expensive in run time is opening a connection? I would think it'd be better to keep the connection open, but would like to get the correct response for this.

Thanks.

Answer 1

How expensive in run time is opening a connection

This only considers CPU speed and doesn't consider bandwidth.

Keeping an open connection saves on CPU but it blocks other requests from being able to use that connection. So its a trade off. Its tough to say what the "correct response" is without knowing a lot more, but in either case it seems like one is tinkering with tolerances instead of nailing the nominals

That said I typically start by keeping a connection open for the duration of a unit of SQL work and then close it.

Although one thing that does seem a little sketchy is this line

20 to 1000 Select calls to make for each item in data being looped.

Try and do more set based operations instead.