On Thu, 2005-03-03 at 11:46 -0600, John A Meinel wrote:
> Jeremiah Jahn wrote:
>
> >I have about 5M names stored on my DB. Currently the searches are very
> >quick unless, they are on a very common last name ie. SMITH. The Index
> >is always used, but I still hit 10-20 seconds on a SMITH or Jones
> >search, and I average about 6 searches a second and max out at about
> >30/s. Any suggestions on how I could arrange things to make this search
> >quicker? I have 4gb of mem on a raid 5 w/ 3 drives. I'm hoping that I
> >can increase this speed w/o a HW upgrade.
> >
> >thanx,
> >-jj-
> >
> >
> >
> >
> >
> It sounds like the problem is just that you have a lot of rows that need
> to be returned. Can you just put a limit on the query? And then change
> the client app to recognize when the limit is reached, and either give a
> link to more results, or refine query, or something like that.
Not really, about 2% of the returned rows are thrown away for security
reasons based on the current user, security groups they belong to and
different flags in the data itself. So the count for this is generated
on the fly needed for pagination in the app which expresses the total
number of finds, but only displays 40 of them. If any one knows a way to
determine the total number of matches without needing to iterate through
them using jdbc, I'm all ears as this would save me huge amounts of time
and limit/offset would become an option.
>
> John
> =:->
>
--
"A power so great, it can only be used for Good or Evil!"
-- Firesign Theatre, "The Giant Rat of Summatra"