Open and Close a DB every access i do or only open one time ?

[BOA]

Aloha,

I wrote a server - client app. Ca. 100 clients are connected with the server app.

Every 30 Secs the clients sends some datas to the server. The server has to store the datas in a Access DB per OBDC.

This is my way at the moment :

I open the DB, when i start the server.

m_db.OpenEx(...)

Then everytime i have to write in a table i open the relevant table, write the datas and close the table.

Earlier I used the way, that for every table access I opened and closed the DB.

Now i let my DB open all the time.

Can this have side effects ?

What way is the better way ?

Thanks and Greetings

BOA

[ovidiucucu]

Now i let my DB open all the time.

Can this have side effects ?
Yes. Having many simultaneous open connections decreases the performance.
What way is the better way ?
First one, in this case. If you have many clients that use that database it's better to open then close the connection each time.

[BOA]

Yes. Having many simultaneous open connections decreases the performance.

First one, in this case. If you have many clients that use that database it's better to open then close the connection each time.


Aloha,

sorry for my english, I forgot something. Only the server app writes in the DB.

The Client sends a message to the server, the server analyses the message and wrote the result in the DB.

this happens up to 300 times each minute.

Greetings

BOA

[GCDEF]

I wouldn't close the table either. Opening the table has more overhead than opening the database.

[purpleflash]

Open the conection, get/send the data you need to, then close the connection.

[BOA]

Open the conection, get/send the data you need to, then close the connection.

Hi,

I need the Con, because sometimes the Server sends some datas ( Shutdown message ) to the Client.....

Greetings

[BOA]

I wouldn't close the table either. Opening the table has more overhead than opening the database.

Hi,

thx for the idea.
But sometimes i must write to a second table. Is it possible to open two or more tables at the same time ?

Greetings

BOA

[usman999_1]

Well, I think keeping a connection open is better then creating, using and closing it. In J2EE the App server keeps a Pool of opened database connections and the applications running within the App Server can request for a connection use it and return it back , that Connection goes back into the Pool to be used later. But as they say, too much of everything is bad, so opening TOO MANY unnecessary connections is not good either. And it also depends upon the senario and how your application is run.

Hope this helps,
Regards,
Usman.

[TheCPUWizard]

Connections to the database should (typically) be held open for the shortest possible time. Two primary reasons...

1) Scalability. Every open connection puts a load on the DB

2) Reliability. If you write your code to open once, and then count on it being open in all the other places, you need a much more sophesticated error handling mechanism for dealing with connections that are expected to already be open.

[ovidiucucu]

Just for...
2) Reliability. If you write your code to open once, and then count on it being open in all the other places, you need a much more sophesticated error handling mechanism for dealing with connections that are expected to already be open.
...hmmm! That's the situation in the open source land?

[BOA]

Connections to the database should (typically) be held open for the shortest possible time. Two primary reasons...

1) Scalability. Every open connection puts a load on the DB

2) Reliability. If you write your code to open once, and then count on it being open in all the other places, you need a much more sophesticated error handling mechanism for dealing with connections that are expected to already be open.

Hi,

only the server app holds the con to the DB !

But there are up to 100 Cons from the Clients to the server app !!!

Greetings

BOA

[GCDEF]

Hi,

thx for the idea.
But sometimes i must write to a second table. Is it possible to open two or more tables at the same time ?

Greetings

BOA

Of course. Looks like you're using ODBC. Just use two CRecordset objects.

[ovidiucucu]

only the server app holds the con to the DB !

But there are up to 100 Cons from the Clients to the server app !!!
Is that a quiz?

[VictorN]

...
But sometimes i must write to a second table. Is it possible to open two or more tables at the same time ?Yes.

[TheCPUWizard]

only the server app holds the con to the DB !


But what happens if/when the server needs to become multi-threaded to handle all of the client connections, and wants to do multiple updates to the database from multiple threads.... :eek:

I will maintain that there are very very few situations where the "open, use, close" paradigm is not appropriate....

[GCDEF]

Let's regroup here people. The guy's continuously writing to an Access database. Continuously opening and closing the database is going to add a large amount of unnecessary overhead. There's nothing inherently wrong with one application holding a DB open for a long time.

[TheCPUWizard]

I missed "Access via ODBC" part.... :blush:

In that case, I would say it may be OK to hold it open longer. Keeping it oopen the entire time, I would still hesitate to do....

[ovidiucucu]

[ Redirected thread ]

[usman999_1]

But what happens if/when the server needs to become multi-threaded to handle all of the client connections, and wants to do multiple updates to the database from multiple threads.... :eek:

I will maintain that there are very very few situations where the "open, use, close" paradigm is not appropriate....
Well, I have written an IOCP based SSL Server that keeps a Pool of Database Connection (somewhat similar approach to what I wrote earlier). And I don't have any problem with performance. Since the updates and database reads happen quite often, I see this as the only logical way forward instead of creating/closing the connection for every request. And the problem you mentioned with keeping a connection open for too long and checking if its still valid, for that atleast the MySQL C API provids an option option that if set, an automatic reconnect is tried incase of connection loss transparently to the client.

Regards,
Usman.