using a next/prev style interface. Since there were a lot of filters to
narrow down the returned data, I just ended up presenting the top 20 items
to the user and basically forced them to use the filter to narrow their
search.
The criteria was basically: "If the user enters no data to filter by,
present only the top 20 items ordered by a specific field(s) per the
business rules." If the user did enter some filter data, then all the
results were returned and they could page through them. This way there was
no caching involved. The data changes quite a bit as there are various
people working in the system all day, so we opted to implement the listing
in this way.
Other parts of the site do use query caching, but the way the data is being
presented/used dictates that method in those cases. Some even use a
combination of both where necessary.
Andy
-----Original Message-----
From: cf coder [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 29, 2004 6:25 AM
To: CF-Talk
Subject: The search is killing the server. Please help!
Hello everybody, I'm working on a logging system. The site search as you can
imagine is very complicated and a lot depends on it.
I have written a stored procedure for the search to make the search quicker
with a lot of inner and outer joins (the usual). The seach is very quick
most of the times, but if the search returns say for ex 5000 + records, it
takes a while for it to display the results in the browser. I have
impletemented "paging", the search displays 100 records per page and then
you have the prev and next links to choose from. I'll give you an example to
hopefully give you a better understanding.
If I do a search that returns say 5000 records, the search takes on an
average 15-20 seconds, displays the first 100 records. Now if the user
clicks on the next button, coldfusion runs the entire query once again, but
this time displays 101 - 200 records. So each time you hit on the 'prev' or
'next' buttons, the seach is re-run. I was wondering if there was a way
round this.
We already know that the search has returned 5000 records and the first page
is displaying the first 100 records.
When the user hits on the 'next' button, I want to simply ask for the next
100 records and not run the entire query. Is this making any sense? I guess
if sql has a 'between' clause or a 'startrow' and 'endrow' clause, this woud
be very easy to do?
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
