Re: Fulltext search too slow

On 3 Aug., 10:51, "Daniel Crichton" <msn...@xxxxxxxxxxxxxxxx> wrote:
DC wrote on Thu, 02 Aug 2007 09:36:01 -0700:

expression: "bank*", hits: 177558, elapsedms:
20746
expression: "citibank*", hits: 20478, elapsedms: 296
That was uncached. Why did "bank*" take so much longer compared to
"citibank*"?

Because "bank*" returned 177558 results, whereas "citibank*" returned only
20478. The smaller the result set the quicker the query will run, and it
appears that once you hit a large number of results (actual number will
depend on various factors, but lets say 30,000 for sake of argument here)
then there is a huge performance penalty. Also try generating some execution
plans and see if maybe some of them are generating parallelism - if so, and
you have hyperthreading CPUs, you might want to consider adjusting the
MAXDOP to the number of physical CPUs, or even try just 1.

Hm. This one was fine too, and the expressions were not cached. What
was the problem with the initial "bank*"?

It was the very first search, and also returned a lot of results - maybe
it's also due to having had the initial connections to the FTS service and
catalog caching included in it's execution time.

I read something about the ft engine trying to connect to the internet
for it's wordbreaker, maybe that has to do with it? I will check.

That should only affect indexing itself when parsing the rows into words,
not the searching.

Dan

Thank you Dan, I watched the execution plan which looks innocent (98%
remote scan, 2% stream aggregate, 0% compute scalar). Is this the way
to detect parallelism or do I have to look somewhere else? I think
MAXDOP does not apply since we are using 2005 standart edition.

The extremely varying latencies of the fulltext searches do only in
part correlate with the state of caching and the number of returned
hits. Within 10 minutes the same expression that returns about 10.000
hits will vary in latency between 1 and 20 seconds (and it is not
getting faster with later tries, only the first search is quiet sure
quiet slow). So I think there are other limiting factors, and since it
is not CPU or disk queues I am having high hopes about changing the
memory configuration as Simon proposed.

Regards
DC

.

Relevant Pages

  • Re: Disobeying jet engines - why?
    ... This really isn't a particularly good example because on a lot of CPUs there is an almost 1 to 1 correspondence between each line of C here and an assembler instruction. ... I challenge the ASM will do everything you need crowd to write a QUINE in their favourite assembler language. ... The errata lists are surprisingly short considering how much clever stuff like register colouring and speculative execution is going on the background to keep the thing fully utilised at every clock cycle. ...
    (sci.electronics.design)
  • Re: CPU that will not execute data?
    ... Only CPUs in von Neumann architecture have those problems, ... what you're meaning is the idea to prevent the execution of ...
    (comp.security.misc)
  • Re: Possible instruction pipelining problem between HTs on the same die ? (fwd)
    ... > write ordering between physical cpus, not between logical HT cpus, ... independence from the fence instructions: ... execution of a memory-reading instruction is quite different from ... speculative prefetch into a cache. ...
    (freebsd-hackers)
  • Re: [BUGFIX]{PATCH] flush icache on ia64 take2
    ... CPUs of 1.3 GHz: ... Flushing a page of 64 Kbytes, with modified data in D-cache ... Assume a few % of this 1 Gbyte is the program execution, ...
    (Linux-Kernel)
  • Re: how fast a public webserver has to be?
    ... I have a server running on a ... Also what is your connection to the net or to ... Also the amount of memory you have if you expect a lot of hits. ...
    (alt.os.linux)