Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Posts
    9

    Unanswered: Free text search for 2 million records

    Hi

    I have a new client with an existing system that has just over 2 million business listings in one table. Each business listing is associated with one business category.

    * Company Table (around 20 fields):

    companyID
    companyName
    categoryID
    state
    postCode
    etc.

    * Category Table (5 fields)

    categoryID
    categoryName
    etc.

    We are using MSSQL 2005 Express Edition with Advanced Services

    A free text search needs to be performed on the companyName and categoryName limited by region (state and or postcode).

    1) What kind of response times should I expect for the free text search (I have not used the free text search before)

    2) How should I index the companyName and categoryName so they are both used in a joined query? i.e. Do I just configure the free text search index on each field separately and it should work?

    Any suggestions appreciated.

    Best Regards

    Kevan
    Last edited by kevan; 04-23-07 at 18:50.

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    1) Impossible to tell. It highly depends on the memory, disk solution, allocated memory to SQL Server, processor, operating system configurations and more.

    2) Indexing the keys used in a join (that is: used in the on clause of the join) may improve performance.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Sep 2005
    Posts
    9
    Thanks for that.

    1)

    Yes, that makes sense. I was really after a very general idea of other peoples experience when dealing with free text search on a couple of million records. What sort of average response times have other people achieved with simple free text searches on this number of records. We would most likely be using a low level hardware solution on a hosted server.

    2)

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •