Post reply

Fulltext performance issue.

  • September 29, 2008 at 3:45 am

    #191546

    Hi I have developed a search engine table, which is a flat table for my users searching records. I have following table strucuture.

    CREATE TABLE [dbo].[SearchEngine](

    [RecordID] [bigint] IDENTITY(1,1) NOT NULL,

    [Col] [int] NULL,

    [Col] [int] NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [int] NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [int] NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [int] NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [int] NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [int] NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [int] NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [bit] NULL,

    [Col] [datetime] NULL,

    [Col] [int] NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [int] NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    CONSTRAINT [PK_SearchEngine] PRIMARY KEY CLUSTERED

    (

    [RecordID] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    In above structure "Col" means columns. Now I have enabled full text search on all the columns whicha are text based. I then enabled full text search on this table with default catalog on different drive i.e. Database on one drive and catalog on another.

    I have also setup for incremental population, and on daily basis one time a day at night.

    there are some queries that come quick upto satisfactory levels but most of the times it taks 3,4 minutes to get data. Please help me out in optmizing this issue.

  • September 29, 2008 at 4:21 am

    #878315

    Are you able to post the procedure that you use to perform the full-text search?

  • October 8, 2008 at 3:55 am

    #882158

    Steve,

    I have given my user options in front end, according to options my query checks which flag is true and which flag is false, my generalized query for all options is as following:

    select RecordID

    from SearchEngine

    WHERE

    Contains (TapeNumber, @TapeNumberString)

    and

    Contains (ReportSlug, @ReporterSlugString)

    and

    Contains (KeyType, @KeyTypeString)

    and

    Contains (ProposedSlug, @ProposedSlugString)

    I first search for ID's (numeric primary keys). After I get all data I page records ranging from 25 to 100 records per page.

    Please help me in this regard.

    Thanks

  • October 8, 2008 at 7:31 am

    #882252

    Please help me steve

  • October 8, 2008 at 2:57 pm

    #882602

    Perhaps more code samples would help

    what code takes 3~4 minutes to run?

    Take a look into CONTAINSTABLE or FREETEXTTABLE as well if you need to

    Otherwise Full-Text should be very efficient on text searching

    But it is also a black box that nobody knows exactly how it works nor how to tweak it

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

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

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