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

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 loo开发者_运维知识库ped. 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.

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.

Not just for this loop you should keep connection open for entire request. It is good practice to open connection only once at the beginning and close once (at the very end)

You should change your queries (probably join in the other tables in this case) to include the other tables. Try and get all of your data in one database call.

It's the 1000s of queries that will take a lot of time.

Connections in MSSQL using ADO.NET are cached so it's not like when you close a connection the actual TCP/IP pipe to the database is actually closed. So closing and opening won't take time. But no matter what it is you're doing, when you do it 1000s of times, it all adds up.

Get all of your data in one database call, close the connection and then do what you need to do on your C# code. What I try and follow is one database call per Request-Response cycle.

You should use PDO. It creates a data object for each DB connection and you can reference multiple databases on one page without opening and closing over and over.

http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

I also have some public code on Github to jump start you. Search for Wrenbjor on github and look for the PDO repo. I would link it but I'm still new to SO so I can only have one hyper link in a post.