ADO处理百万级数据库...

[smlstar]

现有一个SQLserver数据库DB_OLD,有150万左右数据，现要求将DB_OLD中的数据做处理后，全部
导入DB_NEW(SQLserver)数据库。我现在用ado来做数据处理，使用记录集来取数据写数据.这样做速度很慢。平均一小时处理3万左右的数据。我处理的时候是一次将150万条纪录读入
记录集，所以处理起来非常慢。
请教大家，有没有好的建议给小弟，感谢。。送分！

[smlstar]

我认为不能用update语句来实现。

[cakecai]

updatebantch()

[freelove1]

先在Tab1 中 增加一个字段 NEWID，用来记录新的ID　，

update tab2 set ID= tb1.NewID from tab1 tb1,tab2 tb2

Where tb1.ID=tb2.ID

update tab3 set ID= tb1.NewID from tab1 tb1,tab3 tb3

Where tb1.ID=tb3.ID
...............

更新完相关联的表后，
对 TAB1 ，
udpate tab1 set ID=NewID

ALTER TABLE Tab1 DROP NewID

这样的话速度也会很快 1500000 大约只需 30 分钟 （ 可能不要那么久）

[freelove1]

ALTER TABLE Tab1
ADD NewID char(8)
我有过类似的问题 ， 可能我的机器比较快，但处理比你的要复杂些
大约4千9百万条记录只用了4 个小时，我是双CPU ，专业服务器

[smlstar]

感谢你的指点，但是ID-->NewID的转换过程好象只用update无法实现。
我还认为上述的update语句应该放在一个事物中，以保证数据完整。

[freelove1]

当然你必须 转化它们的类型 ID -->NewID 如:

set ID= right(replicate('0',8)+ltrim(str(tab1.NewID)),8)

[freelove1]

当然要用事务

[small_wei]

用SQL SERVER的存储过程做数据转换，然后在VC中调用，很快。

[smlstar]

To:freelove1

要把表中每条纪录的关键字改为从1到150万的连续号码，而且是char类型。
请问可不可以有SQL语句来实现？

[freelove1]

有一個辦法, 你可以先把新表中的ID 設為是自動增長型的整數, 然后從舊表中
插入記錄, 再把 id　的類型改為　char

[freelove1]

下面是整個過程
1. 先產生 一個自動增長的 NewID
a. 從系統中找 New_TAB 這張表 ,如果有 ,則刪除

Begin transaction AAAA
select name from sysobjects where name='New_TAB'
if (表存在) 則 Drop Table 'New_TAB'

啟動 Select Into 的批量快速 插入模式
sp_dboption 'Your DataBase Name', 'select into/bulkcopy', 'true'

將舊表中的記錄加入到新表中, 如果字段需要改名則用as ,增加 newid　字段
SELECT Col1, col2,col3 ,col4......
col5 AS ColNewName5,
IDENTITY(int, 1, 1) AS NewID,
INTO New_TAB
FROM Old_TAB

關掉 Select Into 的批量快速 插入模式

sp_dboption 'Your DataBase Name', 'select into/bulkcopy', 'false'

增加一個 char 型的 ID 命名為 NewID1
Alter Table New_Tab
Add COLUMN NewID1 char (8)

把 NewID 的值 轉為 字符型加入 NewID1 (如果是類似0000001 的話)
Update New_Tab set NewID1= right(replicate('0')+ltrim(str(NewID)),8)
否則更簡單
Update New_Tab set NewID1= ltrim(str(NewID))
更新相關聯的表
Update Tab2 set tb2.ID =tb1.NewID1 from tab2 tb2 ,New_TAB tab1 where
tb1.ID=tb2.ID
.........................
最后 更新 New_Tab 中的ID

Update New_Tab set ID=NewID1

Alter Table New_Tab Drop Column NewID, NewID1



commit transaction AAAA 或者 Rollback transaction AAAA

[smlstar]

right(replicate('0')+ltrim(str(NewID)),8)
中replicate('0')少一个参数。
NEWID是在“00000001”--〉“01500000”之间，那么
replicate('0'，？)中的参数可能是7-->1,请问怎么确定呢？

[freelove1]

right(replicate('0',8)+ltrim(str(NewID)),8)
中replicate('0')少一个参数。
NEWID是在“00000001”--〉“01500000”之间，那么
replicate('0'，8)中的参数可能是7-->1,请问怎么确定呢？


最大的字符個數

[freelove1]

replicate('0'，8) 最大字符個數

如果 NewID=1 則 ltrim(str(NewID))='1' ,Replicate('0',8)+'1'='0000000001' ,從右開始取8個 則為'000000001'
如果 NewID=1500000 ltrim(str(NewID))='1500000' ,Replicate('0'8) +'1500000'='000000001500000' 從右開始取8位 則為 '015000000'

[smlstar]

:).
freelove1,谢谢你的耐心讲解。
：）

[52001314]

同意small_wei,用存储过程，避免数据的来回传输。

[52001314]

标题：提高ADO性能的优秀经验


文章的关键字：
发布人：156ok 发布时间：2002-1-24 11:17:01
-----------------------------------------------------------------------


一、概述

　　“性能”这一术语有着几种不同的、差异微妙的含义。当人们谈到某个东西性能多少好时，他们想要表达的意思可能就是在一定的时间之内它完成了多少工作。例如，一个性能好的发动机运行起来更稳定，产生的动力更强大。对于开发小组，你同样也可能应用这个判断标准：一个性能好的开发小组工作时比较安静，而且能够生产出大量高质量的代码。对我来说，性能至少意味着两件事情——我的代码运行起来有多好，我的开发小组和我本人工作效率怎么样。无论哪一方面，本文介绍的技巧都将起到一定的帮助作用：帮助你更快地编写代码，帮助你编写更快的代码——安静地完成这一切，减少这样那样的错误。本文介绍的技巧主要面向ADO，特别是如何通过ADO访问SQL Server。但与此同时，我还将涉及一些适用范围更广的COM技巧，它们适用于你所编写的所有Visual Basic代码。

　　为了了解从哪些SQL Server数据访问代码编写技术、哪些体系、哪些开发习惯可以得到最好的性能，我已经花了不少时间。一些情况下，对于应用的整体性能来说，单一的技术意义很小，除非我们通过循环将性能的改善程度成倍放大。例如，在一个客户机/服务器应用中，当我们不是通过指定ODBC数据源（DSN）的方式连接数据库时，大约能够节省一到二秒的时间。对于应用整体的适用性或性能来说，这部分节省的时间所产生的影响很小。但是，如果我们在一个中间层组件上应用这种技术，这个组件每分钟（或每小时，每天）都要建立和关闭数据库连接数百（甚至数千）次，那么，这种技术将显著地影响系统的性能表现。因此，对于我在这里讨论的每一种技术，请务必考虑这个倍数因子——即，在一定的时间周期内，你的系统将执行同一段代码多少次。

　　当你开始寻求改进性能的方案时，请考虑一下你的应用（组件，或者是ASP代码）大部份的等待和处理时间花在什么地方。如果你发现应用程序把大量的时间花在等待Open或Execute方法执行完成，那么，你应该认真地检查一下服务器端的查询策略。包括ADO在内，所有的数据访问接口等待查询结果的时间都相同。例如，如果你有一个查询，SQL Server需要20秒才能完成它，不论用来执行该查询的是什么接口，没有一种接口能够比其他接口以更快的速度返回结果。虽然有些接口打开连接的速度比较快，有些接口处理结果集的速度比较快，但没有一种接口能够影响数据库引擎编译和执行查询的速度。因此，如果你的查询具有太高的“挑战性”——例如你没有对索引进行优化，你没有使用存储过程，服务器负载过重，或者你要求返回的记录数量太多——那么，世界上没有一种ADO技术能够帮助你提高性能。除非你解决了这些基本的查询问题，否则没有一种性能调整技术能够显著地改善整体性能。SQL Server的Query Analyzer是一个分析查询性能的优秀工具。它能够用图形的方式显示查询的执行过程，并对改进性能的方法提出建议。

　　如果你能够确信查询具有较高的效率，那么，你可以使用本文介绍的技术进一步调整ADO代码的性能。这里介绍的技巧将从各个方面帮助你简化和改进ADO编程，包括：建立和维护连接，构造和提交执行速度更快的查询，提高处理查询结果的效率，等等。

二、建立连接

　　在一个客户机/服务器应用中，我们可以用好几种方法把建立和初始化数据库连接所需要的时间隐藏起来，使得应用程序既能够打开连接，又不需要用户等待应用程序启动。首先，我们可以尝试异步连接。使用异步连接时，ADO启动连接操作之后，不等待连接完成就把控制权返回给应用程序——这样，应用程序就能够接着执行大部份初始化操作，以更快的速度完成form_load事件处理。如果关闭并重新建立连接的时间小于连接池释放连接的时间，那么这个连接实际上是即时的。但在许多情况下（特别是用户数量不多时），让连接保持打开状态更具有现实意义。在中间层组件或ASP页面内部，如果数据库查询多次重复出现，我建议你让Connection对象保持打开状态。

　　另外一个改进连接性能的办法是，避免使用带有DSN的ODBC。在Microsoft，ODBC已经转入了Quick Fix Engineering（QFE，快速修理工程）状态，它意味着：除非发现重大BUG，该公司将不再在ODBC或它的驱动程序上花时间。另外，考虑性能和部署问题时，ODBC DSN也是一个必须关注的问题。DSN必须安装到客户系统上，要求进行注册表查找，与OLE DB连接相比，它建立连接所需要的时间更长——特别是当你用直接编码的方式指定ConnectionString时，这一点尤其突出。从实际效果来看，避免使用DSN降低的系统开销很有限：如果完全取消连接建立过程，对于每个连接，你也许能够剩下二到五秒时间（假设数据库连接池中已经没有连接）。然而，如果你的应用程序需要频繁地建立连接，节省的时间累计起来就很可观了。

　　建立数据库连接的时候，你要选择一个数据提供者。Microsoft建议我们使用OLE DB提供者替代默认的ODBC提供者。对比最新的OLE DB本地提供者和功能类似但较早的ODBC提供者，我感到前者令人不愉快的意外之事较少。但无论是哪种情况，你都应该在决定使用某个新的提供者之前对应用进行完整地测试——代码的性能、支持的功能、行为方式都有可能发生变化。

　　在中间层和ASP中，在保持连接打开的情况下，我们不能（从实践来看）创建出可伸缩的组件——至少在多次调用之间是这样的。一般地，当IIS引用和释放组件、ASP页面的实例时，组件和ASP页面被频繁地装入、丢弃。由于基于ADO的代码每次执行时都必须建立、使用、释放数据库连接，最小化连接复杂程度的策略对性能的提高程度达到了可明显测量的程度。在这些情形下，对于我们连接数据库的速度来说，连接/会话池有着重要的意义。如果你为Command对象的ConnectionString属性指定合适的值（即，每次使用同样的服务器、初始目录、登录ID和其他参数），那么，连接已经打开且处于可用状态的机会很大。如果连接池中能够找到匹配的连接，连接（或重新连接）的时间将接近0（通常小于250 ms）。

　　然而，如果ADO（或VB）代码不释放Connection对象，或者，我们在不同的实例之间改换了ConnectionString，OLE DB必须每次建立一个新的连接。如果出现了这种情况，我们将很快耗尽连接池内可用连接的数量。要确保连接被释放，我们必须在关闭连接之后把Connection对象设置为Nothing。另外，不要在Recordset Open方法中使用ConnectionString，而是以独立的方式打开Connection对象；这样，当我们要关闭Connection对象以及要把它设置成Nothing的时候，引用它就很方便了。

[52001314]

三、构造和提交查询

　　在构造查询的时候，要搞清楚为什么必须这么做、为什么不能那么做是一个很复杂的问题。然而，一些基本的指导方针能够让构造高效查询的过程更加流畅、轻松。一般地，你不应该让查询浪费服务器时间。下面几个技巧能够帮助你构造出更好、更高效的查询。

　　不要强制SQL Server每次执行查询的时候重新编译和构造查询执行计划。避免这种重复操作的一种简单方法是使用带有参数的存储过程。注意尽量不要使用ADO Command对象的Prepare属性——有时它不能正确工作。如果使用存储过程，你还可以通过消除不必要的“受影响行数”返回值进一步提高ADO性能——只需在存储过程中加入SET NOCOUNT ON就可以了。

　　尽量减少与服务器的通信次数。如果你有几个相关的操作要执行，请把它们合并为一个存储过程，或者是一个可以在服务器上作为脚本执行的复合查询。避免使用方法（比如Refresh）和不适当的Parameters集合引用，它们会强制ADO增加额外的服务器通信过程。

　　在客户机/服务器应用中，只构造Command对象一次，而不是每次使用Command对象的时候重新构造。你可以重新设置Command的参数值，然后在需要时执行它。

　　当查询返回的不是一个记录集时，确保使用了adExecuteNoRecords选项，告诉ADO越过所有那些用来接收和构造记录集（Recordset格式）的代码。你可以把adExecuteNoRecords选项传递给Execute方法，或把它作为Command的选项。

　　执行返回简单记录集的存储过程时，不要使用Command对象。所有的存储过程（以及Command对象）可以作为Connection对象的COM方法出现。让存储过程作为Connection对象的方法出现有着显著的性能优势，同时它也简化了代码。尽管这种技术对于那些有Return Status值或Output参数的存储过程没有什么帮助，但对于动作查询（INSERT、DELETE等）以及那些返回一个或多个记录的查询来说，这种技术很有用。把存储过程作为Connection的方法之后，你可以用方法参数的形式传入存储过程的输入参数；如果调用存储过程返回了一个记录集，你可以通过方法调用中最后一个参数引用该Recordset。例如，下面的ADO语句执行一个名为“Fred”的存储过程，Fred存储过程有两个输入参数，返回一个Recordset：

　 　
MyConnection.Fred "InputArg1", 2, myRecordset



　　编写代码的时候，不要寄希望于VB的自动完成功能会把存储过程或Command对象名字视为合法的Connection对象的方法。在正式运行之前，COM不会解析这类名字。

　　除非绝对必要，否则不要返回记录集。当正在执行的查询返回记录时，ADO就会构造一个Recordset对象。构造Recordset对象的开销很大，因此你应该尽量避免使用Recordset对象。注意有时候执行查询虽然返回结果，但不是返回记录。例如，你可以通过Return Status参数返回整数值。另外，你可以返回Output参数来替代需要构造Recordset对象的记录集，SQL Server允许返回的Output参数多达1000个。

　　只要有可能，请用动作查询（INSERT，UPDATE，DELETE和执行这些操作的存储过程）替代可更新的Recordset游标。此时，你应该使用Execute方法和它的adExecuteNoRecords选项，确保ADO能够知道查询不需要构造Recordset对象。

　　除非必要，否则不要请求服务器进行排序。大多数情况下，对于一个适度大小的Recordset对象，当它被发送到客户端之后，排序速度将更快。另外，如果让ADO客户程序排序Recordset中的记录，则客户应用程序能够按照用户选择的次序排序，从而提高了灵活性。

　　在编写查询之前了解索引的结构。创建合适的索引，调整查询的语法以利用这些索引，你将能够提高记录提取的速度。Query Analyzer能够帮助你决定是否有必要添加更多的索引。

　　不要一次性返回太多的记录。很多时候，容量太大的记录集会严重地影响应用程序的性能。只返回那些当前你需要的记录，如果客户程序需要更多的记录，则以后随时提取。通过带有参数的WHERE子句，或者灵活地运用TOP N查询，限制查询的范围。

　　不要返回太多的列。避免使用SELECT *。SELECT *语句告诉SQL Server返回所有的列，不管实际存在的列有多少。只选择那些你需要的列，这样，当有人为表增加了更多的列时，你不会得到大得出奇的结果集。

　　避免使用游标。如果你必须使用游标，那么不要使用那些所需资源数量超过必要的游标类型。如果没有必要，不要要求游标提供滚动、更新和数据缓冲能力。

　　详细地告诉ADO你想要它做些什么。打开Recordset或者构造Command对象时，不要忘了设置CommandType选项。它避免了ADO“猜测”你的意图，你将能够减少与服务器的通信，而且使得代码更加稳定。

　　另外，学习使用诊断工具，测定运行在服务器上的代码和应用程序的代码占用了多少时间——以及这些时间花在哪里。在这方面，SQL Server Profiler是一个宝贵的工具。它能够阐明你的代码在要求服务器做些什么，能够在草率构造的查询中或对于错误选择的命令属性突出显示。另外，Query Analyzer还能够用图示的方式显示出SQL Server将如何执行查询，提出改进查询的建议，帮助你调整查询。Query Analyzer甚至还能够执行它提出的建议（例如，添加或者删除索引），你只需点击一下按钮就可以完成。

四、处理查询结果

　　查询结果记录发送到客户端之后，客户端应用程序可能需要相当可观的时间去处理结果集。每一种体系（客户机/服务器，多层体系中的中间层，以及ASP）都为优化这个阶段的代码提供了相应的技术。下面是几个能够显著改善性能的技巧。

　　我在代码中看到最多的错误之一是：在引用Recordset Field.Value的时候，使用延迟绑定（Late Binding）。由于代码需要频繁地引用Value属性，而且通常要引用的Field对象有很多，本文前面提到的倍数因子将起到重要影响——因此，所有这里介绍的技巧能够显著地改善性能。一些开发者使用延迟绑定技术的原因在于，他们想要明确地标识出SELECT语句选择了哪些行。为了这个目标，许多人使用了用引号包围字符串的做法。例如，为了引用记录集RS字段集合中的“Cows”字段，你可能使用：

　 　
RS("Cows") 或者： RS.Fields("Cows").Value



　　后面这种方法显式地引用了记录集内Fields集合中指定成员的Value属性。这种方法要稍微快一点，而且当你把这些代码向Visual Basic.NET迁移时，它的向上兼容性也要好一些。上述方法的一种变化是使用感叹号（!）操作符：

　 　
RS!Cows



　　与先行绑定（Early-Binding）相比，采用上述方法时COM进行解析的时间要长得多，这是因为它们强制COM在运行时（而不是在编译时）解析对Value属性的引用，每一次对该对象的引用都要求有一系列类似的、后台进行的查找过程。

　　然而，使用延迟绑定时，不存在代码引用的是哪一个列这类问题。如果你完全按照下列方式编写代码：

　 　
RS(0) '' 指向第一个列（Fields集合的成员）



　　这时，COM能够在编译时解析Value属性地址，代码的运行速度将加快。但是，只有那些了解查询所返回的列以及返回次序的人能够理解这行代码到底指向了哪一个列。如果开发者不具备控制查询数据源的权限（这是很常见的情况），这种方法可能带来问题。为了确定RS(0)引用了哪一个SELECT列，你必须找出生成该Recordset的是哪一个SELECT语句、搞清楚SELECT语句所返回的各个列。

　　然而，有几种技术允许你既能够实现快速地运行时引用，同时保证代码的可读性。其中一种方法的要求如下：开发者必须创建一个查询所返回列的枚举列表。如果查询被改变，比如返回更多的列，或者列的次序发生变化，开发者必须修改和重新部署枚举列表。遗憾的是，要保持枚举列表与查询的匹配，对于管理者来说是一个有些困难的任务。例如，为了快速、明白地标识出ADO代码引用的是哪一个列，你可以结合下面的SELECT语句和枚举列表：

　 　
SELECT CatName, CatType, CatSize from Cats Where... Enum enuCatsQuery CatName CatType CatSize End Enum



　　注意，SELECT语句返回的列与枚举列表中声明的列完全匹配。此后，当你需要引用Recordset的Fields集合时，可以使用下面的代码：

　 　
StrMyName = Rs(enuCatsQuery.CatName)



　　按照这种方法，代码不仅具有较好的可读性，而且它仍旧是编译时绑定，代码的运行速度明显加快。

　　然而，要避免延迟绑定，你还可以使用另外一种方法。[e-mail]列表服务上一场长时间的讨论得出了一种我称之为预先绑定（Prebinding）的方法，它结合了两种技术。当你只需引用Field对象一次时，这种技术没有什么帮助；但在客户机/服务器应用中，预先绑定方法非常理想。使用这种方法时，你要创建多个独立的、命名的Field对象，并把这些对象设置为Recordset对象Fields集合中的成员。编写代码的时候，你首先要为每一个想要使用的字段创建一个命名的Field对象，例如：

　 　
Dim fldName as ADODB.Field Dim fldType as ADODB.Field Dim fldSize as ADODB.Field



　　创建这些Field对象需要一定的开销。然而，你应该估量一下，这是一次性的开销，但它却能够戏剧性地改善性能。

　　打开Recordset之后，你只需一次性地把这些命名的Field对象设置为SELECT查询选择出来的列：

　 　
If fldName is Nothing then Set fldName = RS!CatName Set fldType = RS!CatType Set fldSize = RS!CatSize End if



　　你可以在这里用引号包围字符串的方法引用列，甚至也可以使用感叹号操作符。由于这里的代码只运行一次，不论使用什么方法，性能的差异都不大。接下来，当你需要引用Field对象（查询之后）时，只需使用预先绑定的变量即可：

　 　
strName = fldName strType = fldType strSize = fldSize



　　这种预先绑定方法的性能甚至比序数引用方式（例如RS(0)）都要好。

五、客户机/服务器、中间层和ASP策略

　　在编写代码

[freelove1]

根本就用不著用存储过程那么痳煩 , 因為這個 過程又不是經常用到,只用一次就
行了, 而且 使用 动作查询 根本就沒有 "数据的来回传输"


最好你可以在 Query Analyzer 里面寫語句 ,這樣什麼都vc ,ado 都不用理了

[Richuen22]

试用那个“多行存取”技术，Bulk RFX函数以，如RFX_Text_Bulk