none
Like vs Fulltext index

    Question

  • SQL Server Version : 2000 Standard Edition, sp4

     

    I have a requirement in one of my application which needs to search on a table which contains billons of rows.  This table have two columns FirstName and LastName.  The input parameter of one of the sp is nvachar(4000) datatype. The value is  passed as

     

    FirstName1,LastName1/FirstName2,LastName2/Firstname2,LastName3/FirstName4/LastName4  …..

    The end result should be something like this

    Select  ID from  Emp where FirstName like '%firstname1%' or LastName Like '%LastName1%'

    Union

    Select  ID from  Emp where FirstName like '%firstname2%' or LastName Like '%LastName2%'

    Union

    Select  ID from  Emp where FirstName like '%firstname3%' or LastName Like '%LastName3%'

    Union

    Select  ID from  Emp where FirstName like '%firstname4%' or LastName Like '%LastName4%'

     

    Is this scenario right candidate for Fulltext index? Please note that , I neither  want   rank value feature which is available in FTI nor I want Linguistic search. I just need to do a patern search but the parameter contains multiple values.

    What is the best solution for this? Mind that this is 2000 and as far as Performance in FTI is concerned there were few issues in 2000 if am correct. The management of FTI in 2000 is also an issue like it is not completely integrated to the db engine.

    any help is highly appreciated.

     

    thanks

     

    Madhu

    Wednesday, December 03, 2008 9:29 AM

All replies

  • Madhu,

     

    Given the "billion of rows" you may face some extremely steep challenges in SQL Server 2000 FT.

     

    This is what I would do: Design a custom index Keyword/Results table including frequency column. Populate the table as the search requests coming in. If the custom index for a keyword is up-to-date to 5 billion rows, you only have to search the tail (like 100M) for recent hits, update the custom index table and return results.

     

    You may find that the searches are quite biased, 100 or so very frequent and the rest downhill slide to a single search only.

     

    Let us know how it works out. This project is quite exciting.

     

     

    Wednesday, December 03, 2008 11:18 AM
  •  SQLUSA wrote:

    This project is quite exciting.

     

    What to do... at times you will have no choice other than saying Yes.  Smile .  Let me see if anybody else have any other options/suggestions.

     

    Kalman... thanks for the input.

     

    Madhu

    Wednesday, December 03, 2008 4:45 PM
  • Yes there are issues with SQL Server 2000 Full Text because it was in Microsoft Search Service not installed by default.  But I think it will be better than LIKE predicate in a UNION operation but if you can change to UNION ALL or JOIN then I would say run some tests.


    Asp.net MVP, MCPD Web C#, MCTS TFS, MCITP BI and DBA
    Sunday, December 14, 2008 11:15 PM
  • Madhu,

    Although I am not an expert on FullText, What I have been bitten by in my first effort (POC) is that leading wild character searches doesn't fetch you the right data. The following post talks about the wild card search in general though. I had to drop the idea after learning that.

     
    http://sqlblogcasts.com/blogs/simons/archive/2005/11/07/Wildcard-search-in-SQL-Server-Full-Text.aspx
    Sankar Reddy | http://sankarreddy.spaces.live.com/
    Monday, December 15, 2008 5:28 PM