I've had that happen at 10,000 or so. Actually, it is more likely when using Cfqueryparam because the array allocated
for the bind may have a maximum value. If you pass in an actual string, then you are leaving it up to the database
server and will likely have better results (in this one case only I might add).
-Mark
-----Original Message-----
From: Ken Ferguson [mailto:[EMAIL PROTECTED]
Sent: Friday, July 30, 2004 3:47 PM
To: CF-Talk
Subject: RE: The search is killing the server. Please help!
I worked for a DOD lab for years and I disagree. It's not LIKELY, it's a
CERTAINTY. Some FOIA sites have to do this sort of thing regardless of
whether or not anyone will ever use it. I had to write a report for a
clean-up project that often returned several thousand rows of complex
chemical data knowing for a fact that nobody would ever look at about
98% of it, but it HAD TO BE THERE.
So what I did was this. I ran my query using "top" and then I wrote the
list of ID's into a session var so that the next time they ran the query
it would use "top" to limit the query and use the list of IDs for a "id
not in(list)" type of statement. So, to illustrate:
Param idlist defalut = ""
Select top(100) from table where id not in session.idlist... (or you
don't even have to put this in the session scope if you don't like, but
I was allowing people to leave the search and come back to it...)
Set idlist = listofidsfromquery
This way your always getting your next chunk of records without what
you've already seen. Also, you could even simplify this if the IDs are
in numerical order, you'd just have to get the top x number of rows
where ID > the last one you saw...
Let me know if I can clarify any of that. It seems to make sense, but we
just had a baby the other day so I'm running on just about ZERO sleep
for the last 48 hours or so and still having to work!
_____
From: G [mailto:[EMAIL PROTECTED]
Sent: Friday, July 30, 2004 11:34 AM
To: CF-Talk
Subject: Re: The search is killing the server. Please help!
To that end, when you work for the Government as I do, the ridiculous
quickly becomes the ordinary. A query returning 5000 records, and a
report to display all those records, is not only a possibility, its
LIKELY.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]