Posted by HC on 02/03/07 21:32

On Feb 2, 5:21 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> HC (hboo...@gte.net) writes:
> > Thank you for your reply. I ran this against the two DB's I use and
> > then re-ran the report but it didn't make any real difference (about
> > 10 seconds on a 4 minute 30 second job. Someone replied to the VB
> > thread and suggested re-building the DB from scratch in 2005 and
> > importing the data which is what I'm going to try next.
>
> It could help, but I would count on it.
>
> A better approach is to analyse the query plans in SQL 2000 and SQL 2005
> to understand where the differences are. You can do this from
> Mgmt Studio Express which you can run against both SQL 2000 and SQL 2005.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hey, Erland, thank you for your reply. I have done some more reading
and found this on MS site:

"If MSDE was installed as part of another application that used its
own install program, the SQL Server Express installation program will
not know about it. In this scenario, the only way to upgrade SQL
Server Express is to install SQL Server Express under another instance
name. Use the Named Instance option to specify a new instance name
when you run SQL Server Express Setup, and then detach the databases
from MSDE and attach them to SQL Server Express."

(from: http://msdn2.microsoft.com/en-us/library/ms143491.aspx)

So, it seems, at first reading, that it is acceptable to use the
SP_DETACH_DB and then SP_ATTACH_DB commands as I have to move the DB
from one version (MSDE) to another (SQL Express 2005).

I have installed the Management Express as you suggested and have
reviewed the query plan. I ran the same query from the Management
Express console, three times each, against the same database on both
systems (MSDE and SQL Express 2005). Against the MSDE the system runs
the query in Total Execution Time of average 135 milliseconds (I
believe the time displayed is in milliseconds). This is across a
crossover cable between the two systems. Against the SQL Express 2005
system, running on the same system so it should be a Shared Memory
connection, it runs an average Total Execution Time of 1546
milliseconds, over 10 times as long. I had SP_DETACH_DB'd the
databases from the MSDE, SP_ATTACH_DB'd them to SQL Express 2005, run
SP_UPDATESTATS against each of the databases on Express.

I am going over the query plan and the actual execution now. Any
thoughts?

Thank you for your help.

--HC

[Back to original message]