SQL Server Performance

Single full text query takes about 7 seconds on a simple small table...

Discussion in 'SQL Server 2008 Questions [Archive Only - Closed]' started by Trevi, Sep 17, 2008.

  1. Trevi New Member

    Hello,I have a simple query in a stored procedure that matches on a table of about 12,000 rows and 20 columns containing strings. The search is performed usingconditional CONTAINS ().
    The execution of queries in developmentenvironment with SQL Server 2005 takes about 100-200ms for each row,while in production environment, the same query executed on the samedata with SQL Server 2008 takes about 7 seconds (!!!)
    Is there a solution?
  2. satya Moderator

    Welcome to the forums.
    When was the last time you have populated the indexes on the table/database?
  3. Trevi New Member

    Thank you for answer.
    It was when I filled the table with data... I've also tried to delete and rebuild the full text catalog, but without improvements.
    The DB is composed by only one table, and the query string is saved into a stored procedure.
    This is the query string:
    SELECT * FROM Annuario
    WHERE(
    (
    (Cap = @CAP)
    AND
    (
    (
    CONTAINS(Telefono,@TEL) OR
    CONTAINS(Fax,@FAX) OR
    CONTAINS(Mail,@EMAIL)
    )
    OR
    (
    CONTAINS(RagioneSociale,@RAGSOC) OR
    CONTAINS(Nome,@RAGSOC)
    )
    AND
    (
    CONTAINS(Indirizzo,@INDI) OR
    CONTAINS(Citta,@LOC)
    )

    )
    )
    OR
    (
    (
    CONTAINS(RagioneSociale,@RAGSOC) OR
    CONTAINS(Nome,@RAGSOC)
    )
    AND
    (
    CONTAINS(Telefono,@TEL) OR
    CONTAINS(Fax,@FAX) OR
    CONTAINS(Mail,@EMAIL)
    )
    )
    OR
    (
    CONTAINS(Indirizzo,@INDI) AND
    CONTAINS(Citta,@LOC)
    )

    )
  4. satya Moderator

    What kind of difference in configuration of development & production environment?
    As you say Development is 2005 and Production is 2008, which is something I see as a show stopper in performance in terms of system configuration on hardware.
    Also in the query try to count only one field and do not use *. Try to use the SQL below where field_id is an index in your table. Use change tracking with the "update index in background" option & limit activity when population is running.

Share This Page