Posted by HC on 02/03/07 22:39

On Feb 3, 3:32 pm, "HC" <hboo...@gte.net> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

An interesting thing I just noticed:

In my VB6 app using ADO 2.8 when I run the long query process for one
report it takes, on a test box I've set up for this purpose with XP
SP2, 512MB RAM, bunch o' GB free disk space, Athlon 2400+ and Express
2005 SP1, it is taking about 4 minutes consistently.

HOWEVER, in one portion of my program I open a database connection
when the user opens a window and I keep that connection alive and open
as long as that window is open. When that window is open (so, the
connection is open, too) then the same long-process query consistently
takes about 40 seconds. I'm not sure what to make of that yet.

--HC

[Back to original message]