I have an web application which does differnt types of searches based on queries. In the application there is hyperlink based queries and custom search queries form also. The search query is running on a view. This view build from two tables. The main table haviing around 5lakh records and other table contains around 8lakhs records. So once the user query excecuted then the search query results is stored Arraylist object and depending on the page count respective result are getting displayed to the user.
To display the results it takes around 15 secs. But the real problem comes when user clicks on other links simultaneously which all run the multiple search queries and it degrades performance to the maximum.
1. It doesn't matter how many records you have in both the tables. If you use the correct indexes/primary keys you can reduce the results. However it seems this is not really issue for.
2. You need to analyze how much time the queries are taking. Generally the problem won't stem from DB. Looking at your message that you store the objects in a ArrayList it's better you profile you code to see if the objects were being released.
3. To handle user clicks on multiple links you can try to check when the request comes to see if there is any other objects are cached from previous execution.
In short finding the root cause for performance issue is the only thing you can do now. Is it Query performance? or memory management?.
Joined: Jan 24, 2006
Thank Purushothaman Thambu for quick reply.
I will once again go through the tables whether somemore indexes can be created on tables.
I would like to know for ArrayList profiler can ran to see if the objects were being released. If you can help some links it will be helpful.
To handle user clicks on multiple clicks if we check that any request comes to see if there is any other objects are cached from previous execution. But still it has give the response for latest request right.
Joined: May 24, 2003
It's better to use Java profiling tool to see the memory consumption and related activity (for development you can try NetBean or other open source profiling tools or you can go for commerical ones like JProfiler).
For handling multiple request you can impose some restriction but it depends on your business case. Here is what you can do.
- First time user fires a request. Before processing the request you can set the marker or user-request count and initiate the task. - Before the result is displayed on the client if user clicks on the click you can check the session to see if there is uncompleted job or check the allowed counts. Based on this you can decide to process the request or return back a message to user. - If step 2 happens user will have to start back the entire process and he will loose the response of the first request as well.
The catch is previous request from user must be completed. You need to decide on what you plan to do if user cancels the request (assuming it takes long time or the previous request ends in deadlock). One solution will be to include the timestamp of last request in the session.
Joined: Jan 24, 2006
Thank you very much for the reply.
subject: Performance problem in database application