November 4, 2008 at 3:01 pm
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.
November 5, 2008 at 6:45 am
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.
November 5, 2008 at 7:09 am
What is the view? Please post the DDL for the view as well.
November 5, 2008 at 7:37 am
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.
November 5, 2008 at 9:21 am
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.
November 10, 2008 at 12:04 pm
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
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply