各位VB、SQL Server先進您好:
我使用VB6.0的adodb連SQL 7.0(SP2),我要做大批資料異動但顯然我的程式有問題,不然就是我太笨,輸入檔有近四百萬筆、二百多MB,合併轉入SQL資料庫後近四萬筆、近三百MB，PIII-600、512MBram郤run有夠久，我用NT工作管理員檢視vb及sql server(裝在同一台)的記憶體使用量、分頁錯誤、虛擬記憶體使用量，從一開始的幾十MB成長到幾百MB以上,愈來愈慢，不是說可以處理到GB以上的資料庫嗎?
以下是我的笨程式，真是獻醜了：
Option Explicit
Private objConn As ADODB.Connection
Private objRec As ADODB.Recordset
Private objCmd As ADODB.Command
Dim strHTML As String
Dim bswitch As Boolean
Dim ncount As Integer
Dim cprodno As String
Dim cpassid As String
Dim cemails As String
Dim cprodnm As String
Dim cdeptno As String
Dim ccodens As String
Dim nline As Integer
Private Sub Command1_Click(Index As Integer)
Dim replyval
Dim runstr As String
Dim checkstr As String
Dim strConnectionString As String
Dim strFileName As String
'須先設定引用項目microsoft ActiveX DATA object 2.1 library(選取過一次即可)
'要換台，須改下列連接字串之電腦名稱
'strConnectionString = "Provider=;Integrated Security=SSPI;" _
& "Persist Security Info=False;Initial Catalog=payroll;Data Source=sqserver"
strConnectionString = "Provider=SQLOLEDB.1" & ";Password=quser" _
& ";Persist Security Info=True;User ID=qsysopr" _
& ";Initial Catalog=dbsroll" & ";Data Source=sqserver"
Select Case Index
case 0
'略
Case 1
'執行刪除去年同月份資料表
Set objConn = New ADODB.Connection
objConn.Open strConnectionString
Set objCmd = New ADODB.Command
Set objCmd.ActiveConnection = objConn
runstr = "drop table dbsroll.dbo.qm07"
objCmd.CommandText = runstr
On Error Resume Next
Set objRec = objCmd.Execute
'重建新資料表
Set objCmd = New ADODB.Command
Set objCmd.ActiveConnection = objConn
runstr = "CREATE TABLE dbsroll.dbo.qm07 (" & _
" [prodno] [char] (6) NOT NULL primary key," & _
"[deptno] [char] (4) NOT NULL ," & _
"[passid] [char] (10) NULL ," & _
"[prodnm] [char] (14) NULL ," & _
"[codens] [char] (16) NULL ," & _
"[emails] [varchar] (50) NULL ," & _
"[htmltx] [text] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"
objCmd.CommandText = runstr
Set objRec = objCmd.Execute
ncount = 0
nline = 0
bswitch = False
'開啟ADODB 物件。
Set objConn = New ADODB.Connection
objConn.Open strConnectionString
Set objRec = New ADODB.Recordset
objConn.CursorLocation = adUseClient
objRec.Open "qm07", objConn, adOpenKeyset, adLockPessimistic
'開啟文字檔。
Open App.Path & "\" & "original.htm" For Input As #1
'將文字檔資料轉入資料庫。
Do While Not EOF(1)
PutToData1 strFileName, objRec
'關閉文字檔。
Loop
Close #1
' 關閉ADODB 物件。
objRec.Close
objConn.Close
Set objConn = Nothing
Set objRec = Nothing
Set objCmd = Nothing
'顯示結果
Set objConn = New ADODB.Connection
objConn.Open strConnectionString
Set objCmd = New ADODB.Command
Set objCmd.ActiveConnection = objConn
runstr = "select count(*) from dbsroll.dbo.qm07"
objCmd.CommandText = runstr
On Error Resume Next
Set objRec = objCmd.Execute
Text1.Text = "從original.htm轉入SQL資料庫，共" & objRec.Fields(0).Value & "筆"
Case 2
(略）
Case 3
（略)
End Select
End Sub
Private Sub PutToData1(ByVal strFileName As String, objRec As ADODB.Recordset)
Dim strX As String
Dim strY As String
'從一個已開啟的循序檔中讀入一行資料，並將它指定給一個String變數.。
Line Input #1, strX
If Left(strX, 2) = "**" Then
'不是第一筆
If bswitch Then
With objRec
.AddNew
!deptno = cdeptno
!prodno = cprodno
!emails = cemails
!prodnm = cprodnm
!passid = cpassid
!codens = ccodens
!htmltx = strHTML
.Update
End With
Else
bswitch = True
End If
cdeptno = Mid(strX, 3, 4)
cprodno = Mid(strX, 7, 6)
cpassid = Mid(strX, 13, 10)
strY = Trim(Mid(strX, 23, 28))
If Len(strY) ＞ 10 Then
cemails = "＜" & strY & "＞"
Else
'找不到帳號者
cemails = " "
End If
cprodnm = Mid(strX, 51, 6)
ccodens = Mid(strX, 57, 7)
strHTML = ""
Else
strHTML = strHTML + Trim(strX) + Chr(13) + Chr(10)
End If
End Sub