Connection Object
-
I have something I want to clarify. I did a .NET project some time ago and I'm not sure I remember this correctly, but I don't think I called conn.Open() whenever I want to fill a dataset. I think I read somewhere that calling dataAdapter.Fill() will open the connection and close it right away after it fills the dataset. If for example I have 100 datasets to fill, would it make a difference if I call conn.Open() before I tried to fill the datasets and then call conn.Close(), or should I leave it to the dataAdapter.Fill() to open and close the connection? Which one is more efficient? Does it depend on the database server (connection pooling, etc)? Thanks, Edbert P.
-
I have something I want to clarify. I did a .NET project some time ago and I'm not sure I remember this correctly, but I don't think I called conn.Open() whenever I want to fill a dataset. I think I read somewhere that calling dataAdapter.Fill() will open the connection and close it right away after it fills the dataset. If for example I have 100 datasets to fill, would it make a difference if I call conn.Open() before I tried to fill the datasets and then call conn.Close(), or should I leave it to the dataAdapter.Fill() to open and close the connection? Which one is more efficient? Does it depend on the database server (connection pooling, etc)? Thanks, Edbert P.
EdbertP wrote: Which one is more efficient? While I haven't done any testing with this to determine a precise answer, from experience the first connection.Open() has a noticable delay, while subsequent Open() operations have no notiable delay. This is because the connection gets pooled and .NET is pulling the connection out of the pool rather than completely recreating it. I would say that if timing is absolutely critical that you should Open first, then do all your Fill() operations then Close() because getting and returning a connection to the pool will still take a small amount of time.
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
-
EdbertP wrote: Which one is more efficient? While I haven't done any testing with this to determine a precise answer, from experience the first connection.Open() has a noticable delay, while subsequent Open() operations have no notiable delay. This is because the connection gets pooled and .NET is pulling the connection out of the pool rather than completely recreating it. I would say that if timing is absolutely critical that you should Open first, then do all your Fill() operations then Close() because getting and returning a connection to the pool will still take a small amount of time.
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
-
So I suppose that also depends on the database server you're connecting to then? I'm assuming Access won't have such a luxury... I'll try to implement Open before doing a whole bunch of sql operations then. Thanks Colin! :)
EdbertP wrote: I'm assuming Access won't have such a luxury What luxury? If you are talking about connection pooling it is done on the .NET side - so it works for Access too. EdbertP wrote: I'll try to implement Open before doing a whole bunch of sql operations then. As I said, this is if performance is absolutely critical. And if that is the case then you should also be looking at structuring everything into as few (a single, if possible) database calls. You will get a bigger performance increase over doing a single open and single close if you do that as there is less communication toing and froing between your application and the database.
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
-
EdbertP wrote: I'm assuming Access won't have such a luxury What luxury? If you are talking about connection pooling it is done on the .NET side - so it works for Access too. EdbertP wrote: I'll try to implement Open before doing a whole bunch of sql operations then. As I said, this is if performance is absolutely critical. And if that is the case then you should also be looking at structuring everything into as few (a single, if possible) database calls. You will get a bigger performance increase over doing a single open and single close if you do that as there is less communication toing and froing between your application and the database.
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.