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

Last post 09-18-2008 3:16 AM by satya. 3 replies.
Page 1 of 1 (4 items)
Active Topics My Discussions Unanswered Sort Posts: Previous Next
  • 09-17-2008 3:17 AM

    • Trevi
    • Not Ranked
    • Joined on 09-17-2008
    • Posts 2

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

    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 using conditional CONTAINS ().
    The execution of queries in development environment with SQL Server 2005 takes about 100-200ms for each row, while in production environment, the same query executed on the same data with SQL Server 2008 takes about 7 seconds (!!!)
    Is there a solution?
  • 09-17-2008 11:58 AM In reply to

    • satya
    • Top 10 Contributor
    • Joined on 11-05-2002
    • United Kingdom
    • Posts 22,515
    • Microsoft MVP
      Moderator

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

    Welcome to the forums.

    When was the last time you have populated the indexes on the table/database?

    -Satya S K J

    SQL Server MVP



    Knowledge is Power, you will gain by sharing it. SSQA.net - Invisible contributions to the users & visible success in SQL Community.
  • 09-18-2008 2:31 AM In reply to

    • Trevi
    • Not Ranked
    • Joined on 09-17-2008
    • Posts 2

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

    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)
                )
               
            )

     

  • 09-18-2008 3:16 AM In reply to

    • satya
    • Top 10 Contributor
    • Joined on 11-05-2002
    • United Kingdom
    • Posts 22,515
    • Microsoft MVP
      Moderator

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

    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.

    -Satya S K J

    SQL Server MVP



    Knowledge is Power, you will gain by sharing it. SSQA.net - Invisible contributions to the users & visible success in SQL Community.
Page 1 of 1 (4 items)
Active Topics   My Discussions    Unanswered Posts


© 2000 - 2007 vDerivatives Limited All Rights Reserved.