Saving Query as application variable (performanceQuestion)

[Johnny Derk]

Is there a limit to the size of query you can or should save as an application
varialble, I have a real estate app with about 6,000 listings and was wondering
if I should call the database each time there is a search or save a query as an
application variable and search the query upon request. Any advice would be
much Appreciated.

Thank You

[Dan Bracuk]

Better approaches would be to either cache the query or save as a session
variable. However, you are now dealing with snapshots, rather than real time
data.

Just out of curiousity, under what circumstances would you select all 6000
listings?

[Johnny Derk]

My plan was to have a scheduled upload of a csv file into the database nightly
and upon completion run the query and set the variable.

I don't need real time data because I only get the feed nightly so as long as
I can recache upon upload completion on a nightly basis, I would have the most
upto date data possible.

I think Caching the query sounds like the answer, do still agree after my
reply.

Thanks for you help

[Johnny Derk]

Dan,

Is your question referring to when a user would ask to see all listings.
If so the only time I would need that is if somebody either went to the
results page without entering search criteria (by default all listings show).

I also wanted to have all listings available so when there was a search done I
could search the saved query and take the load off of my DB. I was under the
impression that this would improve the performance of the site.

Thank You For You Help

[Dan Bracuk]

Searching the saved query is a good idea. Especially if someone really wants to see all 6000. You'll want to display them over more than one page or you may overwhelm the user's browser.

[Johnny Derk]

Thank You Dan,

I am planning on using recordset navigation with 10 results displayed per page. I will plan on saving the query.

Thanks again