Re: Full Text Search Performance

From: Ali Salem (AliSalem_at_discussions.microsoft.com)
Date: 10/24/04 

Date: Sun, 24 Oct 2004 01:33:02 -0700

Thanks a lot for you prompt answers. Below is the script result you
requested. The database is on drive E on the production, the fulll text
catalogs are on drive L, which is also on the SAN.

Please finde below my comments on your points:
1) The Arabic language columns, Yes, these columns contains arabic text.
However, we are using neutral for FT. Arabic word breaker can follow the same
rules as english one, and I am not sure this might cause i problem in
performance, I have some doubts about this. the only think that i think is
important here is that arabic uses unicode here, so text size is twice! Also,
I am not getting arabic seach slower than english in a reasonable way, I can
understand a loss of 5-10% in performance for such a thing but not to get a
system that is 50 times slower for the simplest query that does not have much
linguistic as this is not being apllie don arabic by sql FT. The query that
should perform in sub second, is taking 30 or 40 seconds this is too much to
the point that I cannot understand it.

2) The drive letter that is used by the database is E, The FT catalgo are on
another SAN Partition and that is frive E. I think this should make
performance even better.

3) Resource Usage, I can try to set to level 5, but how much difference this
will show, I mean can it be the reason for such a poor query performance! or
it should improve the performance by 10% or so?

Also, I have read something about configuring SQL to use a fixed memory on
the server and leave at max 512 MB for mssearch, does setting the resource
usage flag to 5 substitutes the fixed sql server memory idea? or it is
something that i need to do in adition to that?

Thanks a lot for you help and support. i really appreciate it.

thank you,
Ali Salem
name
                                                    db_size owner
                                                                              
                                       dbid created status
                                                                              
                                                                              
                                                                              
                                                                              
                                                                              
                                                                              
                                                                              
                                   compatibility_level
--------------------------------------------------------------------------------------------------------------------------------
-------------
--------------------------------------------------------------------------------------------------------------------------------
------ -----------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------
testdb
                                                    148399.88 MB
tstdmn\sysadmin
                                                 19 Oct 14 2004
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=BULK_LOGGED, Version=539, Collation=Arabic_CI_AS, SQLSortOrder=0,
IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics,
IsFullTextEnabled
                                                                              
                                                                              
                                                                              
                                                                          80

 
name
                                                    fileid filename
                                                                              
                                                                              
                                                                              
        filegroup
                                                           size
maxsize growth usage
--------------------------------------------------------------------------------------------------------------------------------
------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
------------------ ------------------ ------------------ ---------
testdbSYS
                                                   1
E:\E_DataBase\testdbSYS_Data.MDF
                                                                              
                                                                              
                          PRIMARY
                                                                             
10112 KB Unlimited 10% data only
testdb_Log
                                                   2
E:\E_DataBase\testdb_Log.LDF
                                                                              
                                                                              
                          NULL
                                                                             
51200000 KB Unlimited 10% log only
testdb1
                                                   3
E:\E_DataBase\testdb_INDEXES_Data.NDF
                                                                              
                                                                              
                          SECONDARY1
                                                                             
9216000 KB Unlimited 10% data only
testdb2
                                                   4
E:\E_DataBase\testdb_MAIN_Data.NDF
                                                                              
                                                                              
                          SECONDARY2
                                                                             
40960000 KB Unlimited 10% data only
testdb3
                                                   5
E:\E_DataBase\testdb_IMAGES_Data.NDF
                                                                              
                                                                              
                          SECONDARY3
                                                                             
39424000 KB Unlimited 10% data only
testdb4
                                                   6
E:\E_DataBase\testdb_ALL_Data.NDF
                                                                              
                                                                              
                          SECONDARY4
                                                                             
11151360 KB Unlimited 10% data only

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: complex filter and calculations in access
    ... switch to SQL view. ... query by switching to datasheet view, ... of your database using the from address in this post. ... pre-op infections yes/no ...
    (microsoft.public.access.queries)
  • Re: A little more meat this week
    ... implementation with a query processor capable of returning a result set ... then we can call it a result bag (SQL ... I'm looking at the data model and not database tools at this ... for a s/w developer and a s/w developer simplifies for the end user). ...
    (comp.databases.pick)
  • Re: complex filter and calculations in access
    ... when using a subquery on the same table as the main query, ... switch to SQL view. ... of your database using the from address in this post. ...
    (microsoft.public.access.queries)
  • Re: A little more meat this week
    ... said data includes lists." ... implementation with a query processor capable of returning a result set ... then we can call it a result bag (SQL ... I'm looking at the data model and not database tools at this ...
    (comp.databases.pick)