Results 1 to 12 of 12
  1. #1
    Join Date
    May 2004
    Posts
    351

    microsoft sql server text search?

    Hello,
    I am thinking about writing a script that will utilize a Microsoft SQL Server database. One of the fields in the table will be a field containing text only. I have never done a search query on a text field before so I had a few questions that I would really appreciate answers to:

    1. What is the performance hit of doing a search for a word on a text field?

    2. Is this what the Full Text search feature in MSSQL is for or can this be done using some other query method?

    3. What kind of query would I use? For example, if I wanted to search for a record containing the words "john" and "doe" in its text field, what kind of query would I use?


    Thanks in advance

  2. #2
    Join Date
    May 2002
    Location
    UK
    Posts
    2,997
    Are you familiar with SQL? For the most part MS SQL works in the same way as other SQL compliant databases, sure they all have their quirks but the core is the same.

    It all really depends on how well designed your database is for what type of performance hit you get. If you have a well designed database with decent indexing then a keyword search really won't be a problem.

    Yes MS SQL has full text search, however it also has the very cool, natural language querying.

  3. #3
    Join Date
    May 2004
    Posts
    351
    I am familiar with SQL. Though mostly I have used mysql, I have made use of mssql before. However, I have never really done keyword searches on the databases because it was either not needed, or the pages didnt change so often that a regular search engine would have a problem.

    Now I am working on a database that will be changing constantly in a shared environment so I am apprehensive to test my queries on it before I can estimate its performance impact.

    Also, what would indexing a text field accomplish?

    Thanks again

  4. #4
    SELECT Field, SomeField From Table Where SomeField LIKE '%$string1%' OR LIKE '%$string2%'


    Text searches aren't as fast as primary key id fields. For example if you are doing a left join of sorts it would take quite a bit longer if you were matching similar text than if you were just matching Table1.ID and Table2.ID.

    For the love of God, use Oracle, MySQL, or PostgreSQL... don't support the evil empire

  5. #5
    Join Date
    May 2004
    Posts
    351
    heh

    I was also investingating the LIKE statements and I guess I will do some microbenchmarking on LIKE vs. indexing using full text search.

    The empire is evil, but once it gets some competition, it gets a bit better.

    I dont know oracle, never used it, dont have access to it therefore Oracle is a non factor.

    Mysql is fine, but is still lacking featurewise. It is also slower in many instances (though you could say the same for any DB).

  6. #6
    You can use Oracle for free for development. Its just a massive download from their site is all. Then if you decide to put it on a production server you get to pay through the nose like for anything Microsoft.

  7. #7
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,104
    Along the same lines, you can get IBM's DB2 for testing and development for free also -- although I wouldn't hold by breath while downloading it -- its a 2 gig+ download.

  8. #8
    Join Date
    May 2004
    Posts
    351
    jeebus. I still dont understand why they are so huge when mysql is like 20 megs. Do they have 200 times the features?

    ps: oracle will be a more interesting options once microsoft finishes their plans for the oracle .net provider which they have announced.

  9. #9
    Join Date
    May 2002
    Location
    UK
    Posts
    2,997
    Actually LIKE statements are MUCH slower if you use % at both ends because of the way that indexing works.

    Microsoft's natural language querying system in MS SQL server doesn't have any other competition with the other products, so if that is important to you, MS SQL is your only option.

    MySQL does not support the huge extra features that the big database do. Although MySQL are gradually adding all the groovy stuff in, many of the top level features still aren't there.

  10. #10
    Join Date
    May 2004
    Posts
    351
    I wonder how mysql forums do so well in search times when they mostly use LIKE statements in the sql? (I belive)

    Anyone have some insight into this?

  11. #11
    Join Date
    May 2002
    Location
    UK
    Posts
    2,997
    Well vBulletin generates it's own index format so the like statement (if it even uses one) so it can be much faster than running a LIKE search across the body of the entire message.

  12. #12
    Join Date
    May 2004
    Posts
    351
    I just looked at phpbb and it also uses a similar self generated index. I think I may take a look at this method since full text indexing seems overkill (I dont need inflection or many of the other features). Just for kicks though, I think I will experiment with the search indexing service. I just read the whitepaper and it has a few nifty tracking self update features.

    Thanks again for all the info

Posting Permissions

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