Post reply

TEXT COLUMN SEARCH

  • June 17, 2004 at 10:03 am

    #108516

    I using a table with 3 million records which has got text column which holds xml data.A simple search in the text column takes more than 12000 milliseconds.Full text index option reduces the insert efficiency.

    Can anyone gimme a suggestion to reduce the time taken to retrieve data.

  • June 21, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • June 22, 2004 at 7:35 am

    #511423

    What response time do you expect on such a table without using indexing???

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 25, 2004 at 2:41 pm

    #512044

    Could you provide some additonal information? Specificly, what version of SQL Server (2000 or 7.0) and on what OS Platform it is installed via -- SELECT @@version -- and sp_help <table_name>

    Using either T-SQL LIKE (assuming a '%search_string%') as well as Full-text Search (FTS) will be slow with a table with 3 million rows without the proper indexes as well as server and database configurations. Additionally, SQL Server 2000 can properly FT Index and FT Search XML data better if the XML is stored in a column defined as IMAGE and you can then use Microsoft's or 3rd Party XML IFilter.

    Assuming that you're using SQL Server 2000, you should review the BOL title "Full-text Search Recomendations" and ensure that your FT Catalogs are on a separate disk drive array (RAID10 and not RAID5) from your database files. You should also use CONTAINSTABLE or FREETEXTTABLE with the Top_N_Rank parameter in order to restrict the amount of data to the top "N" by RANK coming from the FT Catalogs. See KB article 240833 (Q240833) "FIX: Full-Text Search Performance Improved via Support for TOP" at:

    http://support.microsoft.com//default.aspx?scid=kb;EN-US;240833 for more info.

    Again, assuming you're using SQL Server 2000, are you using "Change Tracking" with "Update Index in Background"? As normally, this would be the only thing from a FTS prespective that would affect the INSERTing performance efficiency. If so, and you have a lot of reguarly scheduled inserts or updates in batches, you can turn off "Update Index in Background" and then run a scheduled Incremental Population during less busy times.

    Regard,

    John Kane


    John T. Kane

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply