zenmaster10665

Hi All,

I have a large number (65+) of VB clients that need to access an ODBC-Compliant DB, possibly simultaneously. My current solution has each client opening a connection directly to the database with VB ADO calls, however, I am fairly sure that this is not the best implementation in terms of perfomance as it has no centralised pooling of connections to the database.

In my mind, I am thinking that the most efficient way of accessing the database would be through a "pooler" of some sort that will hold/queue all requests to the database and return the results to the client machine. I am thinking this would require designing an adapter that will reside on a separate (query) server and that will accept requests from the client machines, subsequently passing the SQL query on to the database? (This seems a bit complex as it would require the design of a queuing mechanism as well as a registration mechanism for each client to register with the query server.)

Does anyone know of a simpler method of achieving this? It is entirely possible that I am making this issue more complex than it really is, however, I am not sure if there is an easier way! Would 65 simultaneous client connections to an ODBC database actually cause noticable performance issues??

Thanks very much in advance!

__________________
"Beware of the man who works hard to learn something, learns it, and finds himself no wiser than before.
He is full of murderous resentment of people who are ignorant without having come by their ignorance the hard way."
-Kurt Vonnegut

dunkelhelmut

I think you're overreacting here. At work, we have a database server that's being hit by many computers in the office all day, non-stop (for our product's automated test suite). If they're written well, ODBC drivers are designed to handle many simultaneous requests from different clients. I really don't think a spooler will be necessary. In fact, it may be more trouble than it's worth (it's another step in the process that could break, when the ODBC driver should already handle this for you)

Your main concerns here are the database type and the specs of the server. Of course, you'll want a good amount of speed, as well as plenty of RAM and disk space too.

If you're running 60+ cnxns with Access, then you're hosed. Access can barely handle 5-10. Heh. It will work, but don't expect any kind of speed from Access.

If you're expecting a lot of connections, and a lot of data, and a lot of throughput, then Oracle will be your best option. It's not real quick with a low amount of connections, and it's a huge disk space hog (my Oracle installation at work takes up 3GB), but it's designed to be reasonably quick and strong even with hundreds of connections.

SQL Server may not be a bad option either. It's easier to use than Oracle, and it's lightning fast with a lower number of connections.

There are also a number of free database solutions with ODBC support (MySQL and Postgres are the main players), so you can play with those as well.

Whew.

f00sion

ADO caches and pools connection automatically.. just think about any given database driven website, there would potentially be thousands of active connections at any given time.

__________________
Tell me what we’re fighting for— I don’t remember anymore, only temporary reprieve.
And the world might cease if we fail to tame the beast;
from the faith that you release comes an atheist peace.

zenmaster10665

Thanks for the responses!! I think that this should save me a bit of work!!

__________________
"Beware of the man who works hard to learn something, learns it, and finds himself no wiser than before.
He is full of murderous resentment of people who are ignorant without having come by their ignorance the hard way."
-Kurt Vonnegut