I posted this in one of the VB forums but I'm starting to think it
might be more appropriate to have it here, since it really seems to be
a SQL server (MSDE/Express 2005) problem:

Hey, all, I have a problem with queries against a SQL server instance
that I just found and is causing me fits. I hope someone can point
me
in the right direction, please. TIA.

Basically, I got a Vista OS machine to test my VB6 app on it as some
of my clients will be switching over in the coming months. I went to
a local Circuit City during early business hours in the middle of the
week and I installed my application on each of 5 PC's on the new
Vista
OS (Tuesday, when it was released). I had read that MSDE 2000, which
I normally use as my DB is not supported on Vista so I had downloaded
and was using SQL Express 2005. Each system had at least a 1.9 GHz
dual core processor and 1 GB of RAM. One process in my program finds
records in one table that do not match records in another table and
then reports those un-matched entries. On my development machine
(laptop with 1 GB of RAM, XP Pro SP2, MSDE 2000 (current SP), 2 GHz
Centrino (IIRC)) the process takes less than 30 seconds consistently.
On each of those 5 systems at Circuit City the process took 5 minutes
(on each of 3 HP machines, a1700n, a1720n, a1730n, and 11 minutes on
each of two Gateway systems (the model numbers of which I forget at
the moment). Each of these computers should be much faster than my
laptop, and some had twice the RAM, and all had SATA or SATA II
drives
instead of my piddly 5400 laptop drive, I would have thought they'd
all be faster but were abysmally slow.


So, seeing a huge difference in the time, and to try to keep this
short and sweet, I fired up another computer I have, running XP SP2,
on 512 MB RAM, AMD Athlon 2300+. First I loaded MSDE 2000 and my
application and ran the process. < 30 seconds on each of multiple
runs. Second, I unloaded MSDE 2000 and installed SQL Express 2005
and
moved the DB to it (sp_attach_db) which caused some upgrading
(messages reported in OSQL about update/upgrade). When it was done I
rebooted, to be sure, and the ran the program and the process again.
On the same data, on the same computer, the process took 7-9 minutes
consistently on each of several runs. This makes this part of the
application unusable, and even the simple stuff like grabbing a
single
record from the DB (maybe 5 columns of no more than 500 bytes total)
is noticeably slower on the SQL Express 2005 than on MSDE 2000.


So, the problem seems to be with my interaction with the DB. I am
using ADO 2.8 in VB 6 (SP 6). I use DSN-less connections with a
connection string like: Driver={SQL Server};server=(local)
\caredata;database=caredata;Uid=sa; Pwd=<password>


I use the RecordsSet Object to open the data similar to this:
oRS.Open
strSQL$, oCN, adOpenKeyset
after the oCN object has had the connection string set and the object
is opened.


Considering that the same computer, against the same data, with the
same program, takes about 14 times (or more) longer to run, then it
has to be either that SQL Express 2005 is slow OR that my program is
interacting with it in an incorrect manner.


Can someone point me in the right direction, please?


Thank you.


--HC


So, the problem isn't Vista

hi HC,
HC wrote:
> ....
> Considering that the same computer, against the same data, with the
> same program, takes about 14 times (or more) longer to run, then it
> has to be either that SQL Express 2005 is slow OR that my program is
> interacting with it in an incorrect manner.

http://msdn2.microsoft.com/en-us/library/ms190775.aspx ...
"...
Important:
To ensure optimal performance of an upgraded database, run
sp_updatestats (update statistics) against the upgraded database on the SQL
Server 2005 server.

...."
did you?

even better, http://msdn2.microsoft.com/en-us/library/ms187348.aspx
UPDATE STATISTICS object WITH FULLSCAN;
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
--------- remove DMO to reply

On Feb 2, 3:32 am, "Andrea Montanari" <andrea.sql...@virgilio.it>
wrote:
> hi HC,
>
> HC wrote:
> > ....
> > Considering that the same computer, against the same data, with the
> > same program, takes about 14 times (or more) longer to run, then it
> > has to be either that SQL Express 2005 is slow OR that my program is
> > interacting with it in an incorrect manner.
>
> http://msdn2.microsoft.com/en-us/library/ms190775.aspx...
> "...
> Important:
> To ensure optimal performance of an upgraded database, run
> sp_updatestats (update statistics) against the upgraded database on the SQL
> Server 2005 server.
>
> ..."
> did you?
>
> even better,http://msdn2.microsoft.com/en-us/library/ms187348.aspx
> UPDATE STATISTICS object WITH FULLSCAN;
> --
> Andrea Montanari (Microsoft MVP - SQL Server)http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> --------- remove DMO to reply

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.

Thanks again for your reply.

--HC

HC (hboothe@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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

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

HC (hboothe@gte.net) writes:
>> 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?

Did you copy the MDF files to the SQL 2005 instance? That is, it can be
a good idea to have the database both on SQL 2000 and SQL 2005 so that
you can scrutinize the differences in the query plans.

And don't forget to run UPDATE STATISTICS WITH FULLSCAN on the table
when you have attached on SQL 2005; the statistics from SQL 2000 is
invalidated.

One thing that I have noticed with SQL 2005 that can be problematic, is
that if it cannot get all memory it would like to have, it can be
very slow. (I have not verified whether this can happen with the
Express edition as well.) If there are other applications running on
the machine - and this should be common with SQL Express - one way to
avoid this problem is to set the configuration parameter "max server
memory" to some amount of memory that is likely to be available. My gut
feeling, though, is that this is a query-plan issue.

> 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.

Funny. Does tnis happen on SQL 2000 or SQL 2005?

I have no idea what this could be.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Feb 3, 5:14 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> HC (hboo...@gte.net) writes:
> >> 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?
>
> Did you copy the MDF files to the SQL 2005 instance? That is, it can be
> a good idea to have the database both on SQL 2000 and SQL 2005 so that
> you can scrutinize the differences in the query plans.
>
> And don't forget to run UPDATE STATISTICS WITH FULLSCAN on the table
> when you have attached on SQL 2005; the statistics from SQL 2000 is
> invalidated.
>
> One thing that I have noticed with SQL 2005 that can be problematic, is
> that if it cannot get all memory it would like to have, it can be
> very slow. (I have not verified whether this can happen with the
> Express edition as well.) If there are other applications running on
> the machine - and this should be common with SQL Express - one way to
> avoid this problem is to set the configuration parameter "max server
> memory" to some amount of memory that is likely to be available. My gut
> feeling, though, is that this is a query-plan issue.
>
> > 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.
>
> Funny. Does tnis happen on SQL 2000 or SQL 2005?
>
> I have no idea what this could be.
>
> --
> 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- Hide quoted text -
>
> - Show quoted text -

Erland, I copied all the DB files from the MSDE 2000 installation to
the SQL Express 2005 system. The EXE (my program) is exactly the same
on both systems, the databases are identical (I did a fresh copy over
this morning in preparation for more testing so I'm quite certain they
are the same). The files I copied are the MDF and the LDF files and I
referenced them both in the sp_attach_db statement (sp_attach_db
'dbname', 'c:\dbdatafile.mdf', 'c:\dblogfile.ldf').

I'm sorry to be ignorant but I do not recognize the parameters you
mention about the update stats, I will check BOL for this. I ran this
on each db (use <db>, go, SP_UPDATESTATS, go).

The only thing I can think of about the open connection from VB would
be that the open database connection would keep SQL Server Express "on
the line", so to speak, and might make it faster for other database
connections from my app to locate/call the DB. That may be a lame
guess but it's the best I have now.

Thank you for your help and input. I'm disappointed in myself for not
knowing more about this stuff, particularly since I do make a living
using this DB as my data storage; I sincerely appreciate the help.

I have tried a few different things that did not work so I'm about to
re-do the query plan and review them.

--HC

On Feb 3, 8:32 pm, "HC" <hboo...@gte.net> wrote:
> On Feb 3, 5:14 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
>
>
>
>
> > HC (hboo...@gte.net) writes:
> > >> 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?
>
> > Did you copy the MDF files to the SQL 2005 instance? That is, it can be
> > a good idea to have the database both on SQL 2000 and SQL 2005 so that
> > you can scrutinize the differences in the query plans.
>
> > And don't forget to run UPDATE STATISTICS WITH FULLSCAN on the table
> > when you have attached on SQL 2005; the statistics from SQL 2000 is
> > invalidated.
>
> > One thing that I have noticed with SQL 2005 that can be problematic, is
> > that if it cannot get all memory it would like to have, it can be
> > very slow. (I have not verified whether this can happen with the
> > Express edition as well.) If there are other applications running on
> > the machine - and this should be common with SQL Express - one way to
> > avoid this problem is to set the configuration parameter "max server
> > memory" to some amount of memory that is likely to be available. My gut
> > feeling, though, is that this is a query-plan issue.
>
> > > 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.
>
> > Funny. Does tnis happen on SQL 2000 or SQL 2005?
>
> > I have no idea what this could be.
>
> > --
> > 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-Hide quoted text -
>
> > - Show quoted text -
>
> Erland, I copied all the DB files from the MSDE 2000 installation to
> the SQL Express 2005 system. The EXE (my program) is exactly the same
> on both systems, the databases are identical (I did a fresh copy over
> this morning in preparation for more testing so I'm quite certain they
> are the same). The files I copied are the MDF and the LDF files and I
> referenced them both in the sp_attach_db statement (sp_attach_db
> 'dbname', 'c:\dbdatafile.mdf', 'c:\dblogfile.ldf').
>
> I'm sorry to be ignorant but I do not recognize the parameters you
> mention about the update stats, I will check BOL for this. I ran this
> on each db (use <db>, go, SP_UPDATESTATS, go).
>
> The only thing I can think of about the open connection from VB would
> be that the open database connection would keep SQL Server Express "on
> the line", so to speak, and might make it faster for other database
> connections from my app to locate/call the DB. That may be a lame
> guess but it's the best I have now.
>
> Thank you for your help and input. I'm disappointed in myself for not
> knowing more about this stuff, particularly since I do make a living
> using this DB as my data storage; I sincerely appreciate the help.
>
> I have tried a few different things that did not work so I'm about to
> re-do the query plan and review them.
>
> --HC- Hide quoted text -
>
> - Show quoted text -

After reviewing the query plans and the actual execution of them I
think I have an idea of where the next place to look is. Since it
does not involve VB or programming at all, I'm going to start a new
thread to try to keep things neat and tight.

--HC

HC (hboothe@gte.net) writes:
> Erland, I copied all the DB files from the MSDE 2000 installation to
> the SQL Express 2005 system. The EXE (my program) is exactly the same
> on both systems, the databases are identical (I did a fresh copy over
> this morning in preparation for more testing so I'm quite certain they
> are the same). The files I copied are the MDF and the LDF files and I
> referenced them both in the sp_attach_db statement (sp_attach_db
> 'dbname', 'c:\dbdatafile.mdf', 'c:\dblogfile.ldf').

Great. I just want to make sure that you had not lost the SQL 2000
databases, so that you still have those to compare with.

> I'm sorry to be ignorant but I do not recognize the parameters you
> mention about the update stats, I will check BOL for this. I ran this
> on each db (use <db>, go, SP_UPDATESTATS, go).

You run UPDATE STATISTICS tbl WITH FULLSCAN for each table. sp_updatestats
runs UPDATE STATISTICS for table, but without FULLSCAN, which means that
it only samples data. For the small sets of data you mention, FULLSCAN or
not may be be a big deal.

Another way is to reindex all tables. When you reindex a table, statistics
are updated as with fullscan, as SQL Server has to read all rows anyway.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx