single or multiple connection objects???

[greenoe]

i'm writing an app that will allow users to retrieve 1-3 different types of data from a DB. if the user chooses to retrieve all 3 data types is it faster to open one connection and pipe the data through one connection or to open three concurrent connections? what is the performance trade off b/t one and three connections?

[honeybee]

Can you give some more info? If you are using a single database, there is no need to have more than one connection, usually.

.

[greenoe]

i am pulling the information from only one DB. the question i'm struggling with is whether it is faster to have one connection and pipe the data through that or to have three connections. if you have three connections does that mean each pipe is one-third the size?

when you said "usually" what circumstances warrant more than one connection?

[DerFarm]

You should only use 1 connection per database. There should be no degradation of speed because the limiting factor is the network data exchange rate.

I think....

Test it out. Open 3 connections and time the response
then try it with only 1 connection. Make sure you pick the times so that network traffic doesn't interfere.

[honeybee]

Originally posted by greenoe
i am pulling the information from only one DB. the question i'm struggling with is whether it is faster to have one connection and pipe the data through that or to have three connections. if you have three connections does that mean each pipe is one-third the size?

when you said "usually" what circumstances warrant more than one connection?

The most notable is the use of transactions. If you want to manage transactions yourself, and if there is a chance that more than one transaction may be required to be open at the same time, you have to use one connection per transaction.

If you are unfamiliar with the term Transaction in the database perspective, it's some sort of a batch update where changes made to the database are buffered, and the buffer is then either written to the database, i.e. committed, or flushed out just like that, i.e. rolled back. Transactions are a great tool of ensuring data integrity. For e.g. if you were to undertake a complex database updation involving more than one table, you would want to make sure that all the changes were successful. Also if any of the change failed, the entire database updation operation should fail. You can use transactions effectively in such scenarios. And if you are in a situation where more than one transaction needs to be open, for e.g. in a multi-user environment, you will need to open more than one connection, ideally one per user, although the database may be the same.

.

[greenoe]

thanks guys. i think this will have me going in the right direction.