| Author |
 Topic  |
|
|
AnonJr
Moderator
United States
5768 Posts |
 Posted - 13 April 2006 : 10:21:14
|
I've been trying to figure out how to squeeze more out of Access - mostly because that's all I have... I figure that if I optimize the snot out of it now, it should scream if I ever get a hold of SQL Server - anyway, I came across a section on connection pooling. Not having done anything with it, I humbly ask the following questions of those more experienced than I:
Is it worth the time and effort to set up connection pooling with Access?
Will I see a difference?
Just how much extra work am I looking at?
I ran a profiler program on a handful of my pages and noticed that the majority of the time was spent opening and closing connections to the database... and the biggest time sink was the connection set up in config.asp!
Am I on the right track or do I just need to add this to my "Big List of Reasons Access Sucks for What I Need to do My Job" and keep begging? (not that I'm going to stop begging for SQL Server  ) |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 13 April 2006 : 11:40:34
|
| I'm not sure what you mean by setting up connection pooling. That is done automatically by your server. What you need to do is to release your connection as soon as possible, so that it can be used again. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 13 April 2006 : 12:06:35
|
Hmmm... I was under the impression that it was not by default. Am I just a little behind on my reading?
What I have read so far (under the term "Connection Pooling") was that it was something along the lines of:quote:
Connection pooling enables an ODBC application to reuse a connection from a pool of connections. Once a connection has been created and placed in the pool, an ODBC application can reuse the same driver and the connection within the same shared environment (henv) without performing the complete connection process. However, a connection can never be reused between different henv or different drivers.
I do try to open late and close early wherever possible. |
|
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 13 April 2006 : 12:29:37
|
Cool. Got some reading to do. 
What sort of trade-off comes with caching certain pieces of information? |
|
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 13 April 2006 : 14:20:05
|
quote:
Originally posted by Podge
Caching is a huge topic.
Yes, I'm definitely learning about that!
I've got some interesting decisions coming up... I'll be here picking the brains of everybody I can when I get past the simpler parts of this intermediary system.
The biggest thing that keeps nagging me is how in God's green earth I'm going to accomplish what needs to be done using Access if IS doesn't relent and let me use SQL Server (or MySQL or FireBird or any server-based database). |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 13 April 2006 : 17:46:39
|
| I saw one interesting approach to caching recently where the Access DB was put in a RAM Disk, then persisted every 5 minutes to disk, seemed to work pretty well from the demo they showed me. |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 14 April 2006 : 09:48:49
|
quote:
Originally posted by ruirib
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp
Cool. That helps. 
Gremlin, as much as I would love to put a RAM disk in, I can't even get the IS department to let me install components, let alone a database server or even additional hardware.  Hence the situation I'm in. I suspect part of the problem is that I just haven't explained this problem to the right person (almost by definition since I don't have what I need...).
Having said that, it would be fun to try.  |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 15 April 2006 : 01:28:48
|
| I think putting the DB in a RAM Disk was kinda overkill anyway, I think it was more concept than a production stable site and like you say fun to try which is what I think the guys showing me were doing, they were experimenting with RAM Drives and the Gigabyte RAM Module thingy at the same time http://www.anandtech.com/storage/showdoc.aspx?i=2480 |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 15 April 2006 : 17:46:28
|
I saw a review of that in the May issue of MaximumPC. I wonder if that wouldn't be a great place to use as temporary storage for my home-made PVR...
I'm still reading through the other articles. My boss wants me to start sending nasty-grams to all the people who haven't updated their content on the site. I'm really not looking forward to trying to process all the updates and work on the new system (which I'm trying to build in such a way that I don't have to process all the updates... technically that's possible now but the accountability factor still needs to be addressed). |
|
|
| |
Topic |
|
Topic Locked
