Execute a view/query is slower in SQL Server 2005 than 2000
Hi,
I have migrated a database from 2000 to 2005, updated the statistic and rebuild the index.
When I run the following query
"select top 500 * from myview order by name", it is extremely slower in 2005 than 2000. The myview simply does an inner join with 2 table. The name is varchar(50). Strangely when I remove the top 500, the time to run the query on 2000 and 2005 quite similar.
Does anyone have any idea why this is occurring, or any input, this is really puzzling me???
Thanks
Software/Hardware used:
Software/Hardware used:
ASKED:
April 29, 2009 9:15 AM
UPDATED:
May 2, 2009 8:46 PM
Answer Wiki:
This could be because 2005 chooses a different execution plan than 2000. Have you had a look at the execution plans ?
What is the service pack level of the 2005 server ?
There were some situations in which 2005 used less optimal execution plans than 2000, but those bugs have been fixed in service packs or hotfixes, so I would recommend to install the latest service pack and cumulative updates available if you have not done so yet.
Some reference links:
<a href="http://support.microsoft.com/kb/921896">A list of the bugs that are fixed in SQL Server 2005 Service Pack 2</a>
<a href="http://support.microsoft.com/kb/937137/LN/">The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released</a>
-------------------------
To see all answers submitted to the Answer Wiki: View Answer History.
I am using SP 3, could it be the number of processors??????
The number of processors would only effect things if you were using more than one CPU to perform the query.
The first place to start will be the execution plan of the query.