SQL从MSDE 2000迁移到SQL Express [英] SQL Slow from MSDE 2000 to SQL Express

问题描述

我在其中一个VB论坛上发布了这个，但我开始认为它/

可能更适合在这里使用它，因为它真的好像是

a SQL server（MSDE / Express 2005）问题：


嘿，全部，我对SQL服务器实例的查询有问题

我只是发现并使我适应。我希望有人可以指出

我

正确的方向。 TIA。


基本上，我有一台Vista OS机器来测试我的VB6应用程序，因为我的客户将在未来几个月内转换一些b $ b 。我在

周的早期工作时间去了当地的Circuit City

a我在新的的每台5台PC上安装了我的应用程序/>
Vista

OS（星期二，它发布时）。我读过MSDE 2000，这通常用作我的数据库的
在Vista上不支持所以我已经下载了

并使用了SQL Express 2005.每个系统都有至少1.9 GHz

双核处理器和1 GB RAM。我的程序中的一个进程在一个表中找到了与另一个表中的记录不匹配的
记录，然后

然后报告那些未匹配的条目。在我的开发机器上

（带1 GB RAM的笔记本电脑，XP专业版SP2，MSDE 2000（当前SP），2 GHz

迅驰（IIRC））这个过程花费更少一直持续30秒。

在Circuit City的5个系统中，每个系统花费5分钟

（每台3台HP机器，a1700n，a1720n，a1730n，以及两个网关系统各有11分钟

（其中我忘记的型号为

）。这些计算机中的每一台都应该比我的

笔记本电脑，有些有两倍的RAM，而且都有SATA或SATA II

驱动器

而不是我的5400笔记本电脑驱动器，我原以为他们会$
都快，但速度非常慢。

所以，看到时间上的巨大差异，并试图保持这个

短而甜，我开了另一台电脑，运行XP SP2，512 MB RAM，AMD Athlon 2300+上
。首先我加载了MSDE 2000和我的

申请并运行流程。 <在多个

运行中每个30秒。其次，我卸载了MSDE 2000并安装了SQL Express 2005

和

将数据库移动到它（sp_attach_db），这导致了一些升级

（消息在OSQL中报告有关更新/升级的信息）。当它完成后我确认，重新启动了
，然后再次运行程序和进程。

在相同的数据上，在同一台计算机上，每次运行7-9分钟

。这使得

应用程序的这一部分无法使用，甚至可以从数据库中抓取一个

单个

记录（可能是5列）总共不超过500字节）

在SQL Express 2005上明显比在MSDE 2000上慢。

所以，问题似乎与我与DB的交互有关。在VB 6（SP 6）中使用ADO 2.8，我是
。我使用带有

连接字符串的无DSN连接，例如：Driver = {SQL Server}; server =（local）

\ caredata; database = caredata; Uid = SA; Pwd =< password>

我使用RecordsSet对象打开类似这样的数据：

oRS.Open

strSQL $，oCN ，adOpenKeyset

在oCN对象设置了连接字符串并打开了对象

之后。

考虑到同一台计算机，相同的数据，与

相同的程序，运行时间大约需要14倍（或更长），那么它必须是SQL Express 2005缓慢或者那个我的程序是

以不正确的方式与它进行交互。

有人能指出我正确的方向吗？

谢谢。

--HC

所以，问题不是Vista

解决方案

< blockquote>，oCN，adOpenKeyset

在oCN对象设置了连接字符串并打开了对象

之后。

考虑到相同计算机，对于相同的数据，与

相同的程序，需要大约14运行的时间（或更长），然后它必须是SQL Express 2005缓慢或者我的程序是以不正确的方式与它进行交互的b $ b。

有人能指出我正确的方向吗？

谢谢。

--HC

所以，问题不是Vista

hi HC，

HC写道：

....

考虑到相同的计算机，使用相同的数据，与

相同的程序，需要大约14倍（或更多） ）运行时间更长，那么它必须是SQL Express 2005速度慢或者我的程序是以不正确的方式与它进行交互的b $ b。

http：// msdn2。 microsoft.com/en-us/library/ms190775.aspx ...

" ...

重要提示：

为确保升级数据库的最佳性能，请对SQL

Server 2005服务器上的已升级数据库运行
sp_updatestats（update statistics）。

....

你呢？


甚至更好， http://msdn2.microsoft.com/en-us/library/ms187348.aspx

UPDATE STATISTICS对象与FULLSCAN;

-

Andrea Montanari（微软MVP - SQL Server）
http://www.asql.biz http://italy.mvps.org

DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and进一步的SQL工具

---------删除DMO回复

2月2日凌晨3:32， Andrea Montanari < andrea.sql ... @ virgilio.it>

写道：

hi HC，


HC写道：

....

考虑到相同数据的同一台计算机与

相同的程序，运行时间大约需要14倍（或更多），然后它就是b $ b必须是SQL Express 2005运行缓慢或我的程序是

以错误的方式与它进行交互。

http： //msdn2.microsoft.com/en-us/library/ms190775.aspx ...

" ...

重要：

为了确保升级数据库的最佳性能，请对SQL

Server 2005服务器上的已升级数据库运行
sp_updatestats（update statistics）。


...>

你呢？


甚至更好， http://msdn2.microsoft.com/en-us/library/ms187348.aspx

UPDATE STATISTICS对象与FULLSCAN;

-

Andrea Montanari（Microsoft MVP - SQL Server） http://www.asql.biz http://italy.mvps.org

DbaMgr2k ver 0.21.0 - DbaMgr ver 0.6 5.0和更多SQL工具

---------删除DMO以回复

感谢您的回复。我针对我使用的两个DB运行了这个并且

然后重新运行报告但它没有任何真正的区别（关于

10秒on一个4分30秒的工作。有人回复了VB

线程并建议在2005年从头开始重新构建数据库，并且

导入数据，这就是我所说的我将接下来试试。


再次感谢您的回复。


--HC

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

解决方案

, 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" <[e-mail]>
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

这篇关于SQL从MSDE 2000迁移到SQL Express的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！