Execute a view/query is slower in SQL Server 2005 than 2000

290 pts.
Tags:
Microsoft SQL Server 2000
SQL Server 2005
SQL Server Query
VARCHAR
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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:

————————-

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Db23
    I am using SP 3, could it be the number of processors??????
    290 pointsBadges:
    report
  • Denny Cherry
    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.
    66,185 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following