Link to home
Start Free TrialLog in
Avatar of letsbedecent
letsbedecent

asked on

Too many tuples from the database and my application crashes.

Hello all,

        When i am retrieving data from the database depending on a criteria issued by user, i am storing the results in request scope and displaying them for the user a list. When this list gets too large my application crashes !! What should i do ?  How can i get around this problem ??

Thank You,
Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

You could stop reading and close the result set perhaps?
code snippets would help
You might also consider some sort of shared cache
Avatar of letsbedecent
letsbedecent

ASKER

Can u guys be a bit more detailed... any supporting code or sites can be of great help.
>>You could stop reading and close the result set perhaps?

Then how will i show all the results...  ??  Suppose his query gets back 1000 rows from the database, then is there no way to show him those ??
You should be closing your resources in your finally block, that way it will get run regardless of what happens

try
{
   // do your db stuff in here
}
catch (Exception ex)
{
   // log any errors and do any error handling required
}
finally
{
   // close all database resources
}
Well it's you who needs to be more detailed, then we can be more specific ;-)
> Then how will i show all the results...  ??

Depends on the error, if its a db error theres not much you can do
Otherwise you may be able to catch the error (see example above) and handle it, and still display the data to them.
>>you should be closing your resources in your finally block

Please confirm that you are closing. My comments are made assuming no errors of this kind
Suppose his query gets back 1000 rows from the database, then is there no way to show him those ??

This is the problem.... isnt that clear ?
I meant You should post Your code snippets :)
This is the error its displaying

ServletException in:/errorPage.jsp] null' java.lang.NullPointerException
What is the reason for crashing? If it's OutOfMemoryError then it's possible to allow for VM to use more RAM.
But this error is raised only when the query asks for data over the past year or so... if the data is required for only past month, this error is not generated !!
That error is occurring on your error page, theres probably a different error also occurring
check your logs
ok i will definetely do that.,

But, while posting these question, i got a doubt. Suppose if we go to monster.com and type in "Java" in the keywords section, (probably there are many more users doing the same search), the amount of results we get would be very Huuuuuuuuuuge....and is it a good idea to store them in session scope /  any other scope, because memory is limited right., so what do they do normally to handle these kind of huge simultaneous requests ??
tyou may limit the query to only return 100 rows.
so, no matter how many types u hit the same database, you cannot see the records from 101 ??
eg.

select top 100 * from table
>>types

its times
>>so what do they do normally to handle these kind of huge simultaneous requests ??

Have huge resources for one thing. Without knowing what you're doing and what queries are being made against what, it's not easy to advise about caching etc.
and you also offer them a link to the the next (and previous) rows.
ASKER CERTIFIED SOLUTION
Avatar of Mick Barry
Mick Barry
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
so when we hit the next button, we actually hit the database again to get the next 100 rows ?? How can we specify that in a query ?
CEHJ,

In my case, i have a page where the user can select the dates startDate and endDate. I have to return all those rows which were created between these dates. I have a column of type DateTime which contains the value of the time this particular row was created. So, the query is simple, but if he specifies a date range of more than a year (which is probably returning a little more than 2000 tuples) the above error is displayed. I am checking the logs now actually.
Well that sounds like a potential candidate for caching. You could cache n 'pages' of results from x to y perhaps and, provided your data are not frequently updated, you could just hit the appropriate 'page' and return the results. Multiple requests could read the same cache
>  How can we specify that in a query ?

use:

select * from mytable LIMIT <offset>, <no records>
> Well that sounds like a potential candidate for caching.

You don't want to be caching as it could be huge.
>>select * from mytable LIMIT <offset>, <no records>

offset is 101
and
no records is 100

is that correct ??
no, offset is 100
(first row is 0)
For the first 100 rows:
select * from myTable LIMIT 100, 0

For the next 100
select * from myTable LIMIT 100, 101(this is the first row to be retrieved now)

Is this correct ?
For the first 100 rows:
select * from myTable LIMIT 0, 100
For the next 100
select * from myTable LIMIT 100, 100

>>You don't want to be caching as it could be huge.

There's no 'could' about it. The point about caching is that you *know* what resources you're using, so if they're within limits then there's no problem.

If you're going to limit the results returned, then you'll need to implement paging. Don't try to do that yourself - there are already libraries that implement this, e.g.

http://jsptags.com/tags/navigation/pager/index.jsp
CEHJ,

Please read previous comments so as to avoid duplicating posts.
OK - sorry i missed that link