Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Question Unanswered: full text search extrmelly slow

    I have a table with 10 million records
    (CREATE TABLE [dbo].[tblDoc](
    [docId] [int] IDENTITY(1,1) NOT NULL,
    [docSubject] [nvarchar](500) NOT NULL,
    [docDesc] [nvarchar](max) NOT NULL,
    [docNo] [int] NOT NULL))
    and full text indexes r on [docSubject],[docDesc].(change tracking:manual,unique index : docId and full-text have it's own file group ).indexes were repopulate every 2 hour incrementally and every night indexes were rebuild.
    select using fts is extremely slow ! my sample query is :
    select * from tblDoc where contains (docSubject,'"test"')
    I should say other searches (select) without using fts r very good.
    what is my problem ?

  2. #2
    Join Date
    Sep 2009
    Posts
    16
    I think , I found problem , by this query :


    SELECT top (20)
    substring(text,qs.statement_start_offset/2
    ,(CASE
    WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2 + 2)
    ,qs.plan_generation_num as recompiles
    ,qs.execution_count as execution_count
    ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
    ,qs.total_worker_time as cpu_time
    ,qs.total_logical_reads as reads
    ,qs.total_logical_writes as writes
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    LEFT JOIN sys.dm_exec_requests r
    ON qs.sql_handle = r.sql_handle
    ORDER BY cpu_time DESC


    I understand this query have a huge cpu time and may be cause the problem :
    select top 4000 column fulltextkey as k, column fulltextall from[dbo].[tblDocument] where column fulltextkey > @p1 and column fulltextkey <= @p2 order by column fulltextkey

    anyone can help ?

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    its you again with almost the same problem as before ... welcome back

  4. #4
    Join Date
    Sep 2009
    Posts
    16
    Quote Originally Posted by mishaalsy View Post
    its you again with almost the same problem as before ... welcome back
    Hi mishaalsy,
    I dont get right response yet !

Tags for this Thread

Posting Permissions

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