Need nice strategy for this search.

Hello!

We got a task where we are supposed to optimize a webapplication written in Java.

The application got a huge database, oracle, for eg 10 000 000 rows.
The table looks like (simplified)
id  long
name varchar50
desc varchar250

One usecase that now got bad performance is the search and searchdisplay. My guess is that the strategy looks bad.

Now it works like this:

1) Enter search criteria.
For eg name="screwdriver", desc~"*color of handel is *"

2) Execute SQL (through hibernate)

3) Get the search result, for eg 10 000 rows.

4) Display the search result, The result is devided into 50 rows on each page. The complete list of rows is looped through by java-code and the proper intervall 50 rows is fetched.

5) If user choose to browse the search result and enter page, for eg, 10 (rows 500-550) then the complete SQL is executed again. And the proper intervall of 50 rows is fetched this time.

Hope you get it.

To me it looks like this strategy is bad since the complete search result is fetched over and over again. One way of solving this might be store the search result in the session. But since the session also seems to be using a lot of RAM or disk through swapping, if there is a lot of users, perhaps this idea is bad as well.

My hope is that there is a way to execute the sql in a better way so it only fetch 50 rows each time in a efficient way.

Any comments are welcome!

Best regards
Fredrik