*
The moose likes JDBC and the fly likes MS Access performance issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MS Access performance issue" Watch "MS Access performance issue" New topic
Author

MS Access performance issue

Rajeev Ravindran
Ranch Hand

Joined: Aug 27, 2002
Posts: 455
hi all,

Im using MS-Access for my web-application (JSP, Servlets, JavaBean - i follow MVC architecture.)
I'm getting a Connection on userlogin and keep it in a session for his later data manipulation..my problem is that my application is taking too much time to execute after few processes..if i re-start my applicationeserver it will run fastly...wat cld be the problem ???
the issue arises even when only one user is using the application..i dont think its bcoz of MS-Access..It can support upto 10-15 users i think..

cld any1 tell me wat all things are very critical for better performace ??

. im using only one connection..i heard, creating new connection is the only time consuming process relating to database..
. shld i close all resultset ??
one thing i notices is that, my application is consuming lot of system memory..if i close the applicationserver the server memory usage shown by Win NT falls from abt 1550084K to 990024K..
why is it cosuming too much memory !!!
Plz guide me...

Thanks
raj..


SCJP, SCWCD, SCBCD, Oracle Certified Professional (SQL n PL/SQL)
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

For those having trouble reading:
wat = what
cld = could
bcoz = because
im = I'm
shld = should
abt = about
Plz = Please
(Please take the time to communicate your problem, contractions plus spelling and grammar errors plus people facing English as a second or third language hinders communication enough - Thanks, Dave)
The session is the worst place to keep a Connection. I'd think it would be better to open a new Connection from scratch each time you needed one. Yes, creating a Connection takes time, but you're better off finding a free Connection Pooling solution and plugging that in.
Dave
Rajeev Ravindran
Ranch Hand

Joined: Aug 27, 2002
Posts: 455
Sorry Dave, I did not mean to trouble anyone.
Could you tell me what is the issue behing huge memory consumption ?? will it be solved if I remove Connection object from the session and use connection pool instead ??
Thanks
Raj.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

That might have come out ruder than I meant and I appologise
(I wrote it at work and thought about it on the way home )
The most likely cause is that you have not written the session to release the Connection when it expires or otherwise finishes. Since these are actually database resources, the database doesn't get told they are finished with and it is OK to clean them up. Therefore the database keeps getting asked for new Connections and just keeps handing them out.
If you use Connection Pooling instead, you can limit it to about 5 Connections and just reuse the Connections. You may need more Connections than this, but this would be a matter of profiling. It may be better to occasionally have a thread wait a fraction of a second for a Connection than have too many Connections open.
Vedhas Pitkar
Ranch Hand

Joined: Jan 27, 2001
Posts: 445
Also you can try closing the resultset & conection in each page after you've finished with them.But about the memory problem,it seems to me that you are doinga LOT of processing on the front end.Try minimising it.
Rajeev Ravindran
Ranch Hand

Joined: Aug 27, 2002
Posts: 455
Thanks for the reply Dave, Vedhas.
I'm keeping the Connection object in a session and will close this connection when the user logs out, what I meant by doing like this is, by using a single connection object I can manage the user requirement.
Vedhas, could u tell me about the front end processing..I tried my application by keeping the application server in a different machine, this time also memory of the application server machine was getting consumed a lot..The memory is getting locked, i.e. the memory is not releasing even after the whole process ends..what could be done to release the memory ??
Thanks
Raj...
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Originally posted by r rajeev:
I'm keeping the Connection object in a session and will close this connection when the user logs out

This is still flawed though. What if the user doesn't log out, but just closes their browser? If the session just expires on the server side, it won't close the Connection. You need to start resorting to SessionListeners to manage the resources otherwise yu'll have the same problems.
I can't stress strongly enough the need for Connection Pooling. Even if you get the session creation/close working correctly using the session, the solution is not very scalable since resource usage will be proportional to the number of users. You cap the number of users you can support at about 10-15 people. A Pool with 10-15 Connections should be able to serve hundreds of people.
Dave
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MS Access performance issue
 
Similar Threads
any free type 4 driver ?
lock on MS Access 2000
regarding JDialog .
plz guide me on my career.
Tomcat 4.0 running slow !!!