Query in 2000 returns in 1 second, in 2005 it runs for more than six minutes

  • I don't remember, and I'm too lazy to look back, did you ever update statistics with a full scan on the SQL Server 2005 database?

    The query engine and optimizer are both different between versions of SQL 2000 and SQL 2005.

  • yes, statistics were updated, I went ahead and did it again. It didn't make a difference.

    I created a copy of the remote data locally on the 2005 box and adjusted the view to reference it as the source. Query completes in less than a second. So that would lead me to believe that something in the engine has changed in how it processes remote queries.

    Here's another interesting item:

    If I remove the where clause and leave the order by clause in, it executes in 1 second.

    If I remove the order by clause and leave the where clause in, it executes in 1 second.

    If I remove both clauses, it executes in 1 second.

    If I leave them both in, it executes in 6+ minutes.

    still playing around with the query, but please feel free to post any suggestions or explanations why this is occurring. I'm not necessarily looking for a new query, I'm more looking for an understanding of what changed and why this occurring.

    **edit

    Found that if I move the "where c.comm_name is not null" into the join syntax (on c.parent_id=c_1.comm_id and c.comm_name is not null) the query executes in 1 second, under a completely different execution plan.

  • What is the view? Please post the DDL for the view as well.

  • once in a while we have weird problems like this and most times it's in a database that has been around since SQL 7 and continually upgraded across different SQL versions.

    what we do is drop all the statistics on a table and then rebuild the indexes to recreate the statistics. works every time.

  • Lynn Pettis (11/5/2008)


    What is the view? Please post the DDL for the view as well.

    create view COMMITTEE

    as

    select COMM_ID,

    COMM_NAME,

    STATE,

    TYPE_FK,

    SUB_FLAG,

    PARENT_ID,

    ADDRESS1,

    ADDRESS2,

    CITY,

    ADR_STATE,

    ZIP_CODE,

    PHONE,

    FAX,

    EMAIL,

    WEB

    from server2.centralDB.dbo.Committee

    And about dropping the indexes/stats, the table is in SQL 2000, and has never existed anywhere else, in any other version. The view in 2005 is just a standard view querying the linked server/table.

  • SQL Noob (11/5/2008)


    once in a while we have weird problems like this and most times it's in a database that has been around since SQL 7 and continually upgraded across different SQL versions.

    what we do is drop all the statistics on a table and then rebuild the indexes to recreate the statistics. works every time.

    SQL Noobs's suggestion makes sense;

    for fun i made a quickie procedure to drop and recreate all the statistics;

    any input on this? It's recreating all the stats, especially all the_WA_Sys_ stats, which i thought were the problem children.

    [font="Courier New"]

    CREATE PROCEDURE sp_DropAndRebuildStats

    AS

    BEGIN

    SET NOCOUNT ON

      --we need a @table of all to drop and recreate statistics

      DECLARE @AllMyStats TABLE(statistics_name VARCHAR(255),statistics_keys VARCHAR(500))

      DECLARE @Tableid    INT,

              @TableName  VARCHAR(255),

              @StatName VARCHAR(1024),

              @StatKeys VARCHAR(1024),

              @SQLString  VARCHAR(8000)

      --now an nice ugly cursor to do the dirty work:

      DECLARE c1 CURSOR FOR SELECT id,name FROM sysobjects WHERE xtype='U' ORDER BY name

      OPEN c1

      FETCH NEXT FROM c1 INTO @Tableid,@TableName

    PRINT '--Processing ' + @TableName

      WHILE @@fetch_status <> -1

       BEGIN

         DELETE FROM @AllMyStats

          INSERT INTO @AllMyStats

            EXEC sp_helpstats @objname = @TableName

          IF EXISTS(SELECT

                      statistics_name,

                      COUNT(statistics_name)

                   FROM @AllMyStats

                   GROUP BY statistics_name

                   HAVING COUNT(statistics_name)>0)

            BEGIN

              PRINT '-- ' + @TableName + ' stats exist'

              DECLARE StatisticsCursor CURSOR FOR SELECT statistics_name,statistics_keys FROM @AllMyStats

              OPEN StatisticsCursor

              FETCH NEXT FROM StatisticsCursor INTO @StatName,@StatKeys

              WHILE @@FETCH_STATUS = 0

                BEGIN

                  SET @SQLString = 'DROP STATISTICS ' + @TableName + '.' + @StatName + ''

                  PRINT @SQLString

                  EXEC (@SQLString)

                  SET @SQLString = 'CREATE STATISTICS ' + @StatName + ' ON ' + + @TableName + ' (' + @StatKeys + ') WITH SAMPLE 50 PERCENT '

                  PRINT @SQLString

                  EXEC (@SQLString)

                  FETCH NEXT FROM StatisticsCursor INTO @StatName,@StatKeys

                END

              CLOSE StatisticsCursor

              DEALLOCATE StatisticsCursor

            END --IF STATISTICS EXIST

          ELSE

            PRINT '-- ' + @TableName + ' no stats '

          DELETE FROM @AllMyStats

       FETCH NEXT FROM c1 INTO @Tableid,@TableName

       END

      CLOSE c1

      DEALLOCATE c1

    END

    GO[/font]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 16 through 21 (of 21 total)

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