Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2002
    Posts
    87

    Unanswered: SQL 2005 Full-Text performance on large results

    Hello everybody,
    I've got a little problem wich i'm trying to solve since 1-2 years and i hoped it would go away with SQL 2005 - but that wasn't the case .

    Situation:
    I've just bought a new Server containing:
    SQL 2005
    64 Bit Enviroment
    4 GB RAM
    2x AMD Opteron 2 GHz Prozeccors (Dual Core)
    2x RAID Controllers (RAID 1) containing
    1.1 System
    1.2 Data
    2.1 Transaction Logs

    I've created a full-text table containing all the search terms i need to search.
    Table build:
    RecID - int - Primary Key
    SrcID - varchar(30)
    ArticleID - int - referring to an original table
    SearchField - varchar(150) - Containing the search terms
    timestamp - timestamp field

    Fulltext index:
    RecID as Primary Key
    SearchField as indexed field - Wordbreaker: Neutral (containing several languages), Accent sensitivity off

    Now i've got different tables imported in here resulting in a table size of ~ 13 million rows.

    There is no problem with the performance on this catalog if i search a term wich isn't contained in more than 200-300 recordsets - but if i search for a term wich could occur in 200'000 upwards it gets extremely slow.

    On the slow query the first records get in after no time, but until the query finished up to 60 seconds pass.
    The problem is that i have to sort by a ranking value wich is stored externally - so i need all results to sort them...

    current (debugging) query:
    SELECT ArticleID FROM fullTextTable AS ft INNER JOIN CONTAINSTABLE(FullTextCatalog,SearchField,'"term*" ') AS ftRes ON ftRes.[KEY]=ft.idEntry

    Now if i check in the performance monitor:
    As soon as i run the query the 'Avg. Disk Read Queue Length' counter on disk D (SQL Data Files) jumps to the top, until the query has finished.
    Almost no read/write activity on C: where the Fulltext is stored...

    If i rerun the query, after it finished once successfully - it takes place below 1-2 seconds, would be nice to get that result in first place .

    Does anybody know a workaround to this problem?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It runs slowly the first time because it is RETURNING AND SORTING 200,000 RECORDS OUT OF 13 MILLION RECORDS.

    I'm no expert on full-text search, but I don't see how you can expect that to be fast.

    It probably runs fast the second time because the data is in cache, or the engine may save the results of common searches.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2002
    Posts
    87
    Hello Blindman,
    I'm aware of the data quantities, but if i take other samples on the same system inner joining tables with similar counts of recordsets its much faster (e.g. counting the fulltext-table entries of each article id - selecting top 200 ordered by count descending - takes ~5 seconds) ...
    It seems to be a problem of joining the fulltext result to the fulltext-table.

    Is it possible to find out on wich database the avg. read counter on disk D: is done?
    I would wonder if it reads that much on the full-text database or tempdb...

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I wish I was more knowledgeable of full-text queries and could provide better assistance. We do not get many questions about full-text searches on this forum.

    Are you telling me that you have other scenarios that return hundreds of thousands of records from a table containing more than ten million records and sort the result set by an external ranking value joined on what are probably non-indexed columns...and they run fast?

    I can tell you that when you run standard SQL statements through query analyzer, sorting the results can easily occupy 30-50% of the processing time.

    What does the execution plan for this look like in Query Analyzer?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2002
    Posts
    87
    Hello Blindman...
    Im getting far better performance on inner joins (for sure on indexed values) with the same kind of data.

    The query i've called in the upper message (counting) is on the same tables used within the fulltext query...

    The simples full-text query without ordering or grouping takes very long...

    e.g.
    SELECT ArticleID FROM FullTextCatalog WHERE CONTAINS (*,' "pet*" ')
    >> Select all words wich begin with pet*

    Execution Plan:
    SELECT - Cost 0%
    Merge (Inner Join) - Cost 44% (Join on Full-Text service Key)
    Index Scan - Cost 44% - Output rowID, ArticleID (???)
    Sort - Cost 8% (Key ascending)
    Remote Scan - Cost 4%

    now the strange thing is, that i've worked around those queries for the whole day now - and the last queries ran extremely fast (dont ask me why) - even inner joining and grouping ... ...

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by coroner
    now the strange thing is, that i've worked around those queries for the whole day now - and the last queries ran extremely fast (dont ask me why) - even inner joining and grouping ... ...
    Because the query plan and much of the data was cached. You can't count on getting that performance every time the statement is executed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2002
    Posts
    87
    Jup, that's allright as long as i can change the search-term and the query time stays

    Do you know any technique how do find out in wich database there are read/write activities (I/O)?
    I could not find any appropriate..

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SQL Profiler.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jul 2002
    Posts
    87
    Quote Originally Posted by blindman
    SQL Profiler.
    Hello again blindman,
    Sorry for asking this - but i was not able to find any harddisk i/o counters in SQL Profiler, can you tell me in wich event i can find the database specific i/o workload?

    Thanks!

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This command will display IO statistics for every statement run. Use it in query analyzer:

    SET STATISTICS IO { ON | OFF }
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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