Session storage on an intranet app.

[sde]

I'm building this web app which will be used by 25 people max at a time.

I have a very detailed 'advanced customer search'. I want it to work like when you search for flights on orbitz, expedia, or most other big sites like that.

- execute the search
- view an animated gif that says 'searching'
- change the page to display the results.

since there are about a million records, the ability to use 'like' in the query, and possible joins happening depending on the search criteria, this may take some time.

the only way i can think to do this is by saving the result set to a session variable.

you think that is how the airline sites are doing it? my only concern would be hogging server resources, but the advanced search wouldn't really even get used that much.

any thoughts on this?

[Belisarius]

Are you asking whether or not saving the result to to a session variable is a good idea or not?

[sde]

Originally posted by Belisarius
Are you asking whether or not saving the result to to a session variable is a good idea or not?

exactly, with the idea that it's only going to be used by a few users at a time.

[Belisarius]

If this is in Java, there might be a problem with the ResultSet should you attempt to use the connection, as the ResultSet is dependant on the connection. Should you use the connection to do something else or close the connection (or should the connection be closed for you), it'll screw up the ResultSet.

[sde]

i suppose an array would work fine too .. just haven't ever stored that much information in a session. i guess it will be fine. i can always check the server

[Belisarius]

Well, for what it's worth I'm storing about 10000 items in an array and it hasn't killed the server yet.

I will admit, I'm not sure of the best way to handle large sets of data.

[pipeten]

Good question.. what method did you opt for in the end and how did it go? (What language and OS too )

[sde]

for this project i'm using java. i've opted to store an array of search results ( my customer object )in a session variable.

it makes for very fast sorting and paging. there are a couple factors that influenced my decision.

1. i'm working with a db2 database on an as/400, and 'LIMIT' is not recognized by the db. this means i have to query all results anyway. ( and not just the first 20-50 that i display on the page )

2. queries take 500-2000 ms. when coupled with other queries can compromise performance of my web app.

i'm pretty happy with the results now.

[idx]

What about the FETCH FIRST $n ROWS ONLY statement? I remember it being used in the ADODB code.

Looks like OPTIMIZE FOR $n ROWS might also be an option.

-r

[sde]

i had to think to re-remember why that didn't work. yes, you are right, however how would you select the second set of 10 records?

select * from table limit 10,20

i think that was the problem. you can't fetch anything but the first set of 10.

[idx]

You would think there has to be some sql-specific way around that.. Looking at ADO a bit more it seems they use some internal chopping functions to return only the second set of 10. (although the first 20 are pulled from the db)

damn db2.

-r

[sde]

i'm not using ado though, i need to use a native jdbc driver to db2 via the javatoolbox for ibm as/400 midrange servers. .. ado is just microsoft right?