Hi All,
I am trying to find a way to get better performance from the database
options for the Pocket PC. The application I have built is in eVB 3.0.
Right now I use ADOCE 3.0 and the CEDB engine. I tried some different ways
and I found something strange. Here are the results:
ADOCE 3.0 & CEDB 3553 Inserts in 3:24
ADOCE 3.1 & CEDB 3553 Inserts in 6:56
ADOCE 3.1 & SQL Server 3553 Inserts 8:38
I used the same code and input data into an empty database for each test
run. I find it strange that the performance declines as I switch to the
newer tools. I have posted the code below. Any suggestions would be very
much appreciated.
Thanks, Steve
Private Sub Command1_Click()
Dim filFile As FILECTLCtl.File
Dim strSQL
Dim buffer
Dim arr_Product
Dim oProduct As ADOCE.Recordset
Dim tempTimeStart, tempTimeEnd
Dim recCount
Screen.MousePointer = 11
tempTimeStart = Now
Set filFile = CreateObject("FILECTL.File")
'Open the Product file for reading
filFile.Open "\Program Files\PocketSalesman\DWNLOAD\PROD.116",
fsModeInput, fsAccessRead, fsLockRead
If Err.Number = 0 Then
Set oProduct = CreateObject("ADOCE.Recordset.3.1")
goConnection.Execute "Delete tblProducts"
ADOCECommit
Label1.Caption = "Delete Complete." & tempTimeStart & " >>> " &
Now
oProduct.Open "tblProducts", goConnection, adOpenDynamic,
adLockOptimistic, adCmdTableDirect
Do Until filFile.EOF
buffer = filFile.LineInputString
arr_Product = Split(buffer, "|")
If arr_Product(1) <> "HEADER" And arr_Product(1) <>
"TRAILER" Then
oProduct.AddNew
oProduct.Fields("ItemNumber") = arr_Product(1)
oProduct.Fields("ItemDescription") = arr_Product(2)
oProduct.Fields("CaseUPC") = arr_Product(3)
oProduct.Fields("PackUPC") = arr_Product(4)
oProduct.Fields("UnitUPC") = arr_Product(5)
oProduct.Fields("SortFilter1") = arr_Product(6)
oProduct.Fields("SortFilter2") = arr_Product(7)
oProduct.Fields("SortFilter3") = arr_Product(8)
oProduct.Fields("SortFilter4") = arr_Product(9)
oProduct.Fields("SortFilter5") = arr_Product(10)
oProduct.Fields("SellByUnit") = arr_Product(11)
If arr_Product(12) = "Y" Then
oProduct.Fields("ReasonReturnFlag") = -1
Else
oProduct.Fields("ReasonReturnFlag") = 0
End If
'oProduct.Fields("ReasonReturnFlag") = arr_Product(12)
oProduct.Fields("DepositItemNumber") = arr_Product(13)
If arr_Product(14) <> "" Then
oProduct.Fields("CasePrice1") = arr_Product(14)
Else
oProduct.Fields("CasePrice1") = 0
End If
If arr_Product(15) <> "" Then
oProduct.Fields("CasePrice2") = arr_Product(15)
Else
oProduct.Fields("CasePrice2") = 0
End If
If arr_Product(16) <> "" Then
oProduct.Fields("CasePrice3") = arr_Product(16)
Else
oProduct.Fields("CasePrice3") = 0
End If
If arr_Product(17) <> "" Then
oProduct.Fields("CasePrice4") = arr_Product(17)
Else
oProduct.Fields("CasePrice4") = 0
End If
If arr_Product(18) <> "" Then
oProduct.Fields("CasePrice5") = arr_Product(18)
Else
oProduct.Fields("CasePrice5") = 0
End If
If arr_Product(19) <> "" Then
oProduct.Fields("CasePrice6") = arr_Product(19)
Else
oProduct.Fields("CasePrice6") = 0
End If
If arr_Product(20) <> "" Then
oProduct.Fields("CasePrice7") = arr_Product(20)
Else
oProduct.Fields("CasePrice7") = 0
End If
If arr_Product(21) <> "" Then
oProduct.Fields("CasePrice8") = arr_Product(21)
Else
oProduct.Fields("CasePrice8") = 0
End If
If arr_Product(22) <> "" Then
oProduct.Fields("CasePrice9") = arr_Product(22)
Else
oProduct.Fields("CasePrice9") = 0
End If
If arr_Product(23) <> "" Then
oProduct.Fields("CasePrice10") = arr_Product(23)
Else
oProduct.Fields("CasePrice10") = 0
End If
If arr_Product(24) <> "" Then
oProduct.Fields("UnitPrice1") = arr_Product(24)
Else
oProduct.Fields("UnitPrice1") = 0
End If
If arr_Product(25) <> "" Then
oProduct.Fields("UnitPrice2") = arr_Product(25)
Else
oProduct.Fields("UnitPrice2") = 0
End If
If arr_Product(26) <> "" Then
oProduct.Fields("UnitPrice3") = arr_Product(26)
Else
oProduct.Fields("UnitPrice3") = 0
End If
If arr_Product(27) <> "" Then
oProduct.Fields("UnitPrice4") = arr_Product(27)
Else
oProduct.Fields("UnitPrice4") = 0
End If
If arr_Product(28) <> "" Then
oProduct.Fields("UnitPrice5") = arr_Product(28)
Else
oProduct.Fields("UnitPrice5") = 0
End If
If arr_Product(29) <> "" Then
oProduct.Fields("UnitPrice6") = arr_Product(29)
Else
oProduct.Fields("UnitPrice6") = 0
End If
If arr_Product(30) <> "" Then
oProduct.Fields("UnitPrice7") = arr_Product(30)
Else
oProduct.Fields("UnitPrice7") = 0
End If
If arr_Product(31) <> "" Then
oProduct.Fields("UnitPrice8") = arr_Product(31)
Else
oProduct.Fields("UnitPrice8") = 0
End If
If arr_Product(32) <> "" Then
oProduct.Fields("UnitPrice9") = arr_Product(32)
Else
oProduct.Fields("UnitPrice9") = 0
End If
If arr_Product(33) <> "" Then
oProduct.Fields("UnitPrice10") = arr_Product(33)
Else
oProduct.Fields("UnitPrice10") = 0
End If
If arr_Product(34) <> "" Then
oProduct.Fields("PostOffCasePrice1") =
arr_Product(34)
Else
oProduct.Fields("PostOffCasePrice1") = 0
End If
If arr_Product(35) <> "" Then
oProduct.Fields("PostOffCasePrice2") =
arr_Product(35)
Else
oProduct.Fields("PostOffCasePrice2") = 0
End If
If arr_Product(36) <> "" Then
oProduct.Fields("PostOffCasePrice3") =
arr_Product(36)
Else
oProduct.Fields("PostOffCasePrice3") = 0
End If
If arr_Product(37) <> "" Then
oProduct.Fields("PostOffCasePrice4") =
arr_Product(37)
Else
oProduct.Fields("PostOffCasePrice4") = 0
End If
If arr_Product(38) <> "" Then
oProduct.Fields("PostOffCasePrice5") =
arr_Product(38)
Else
oProduct.Fields("PostOffCasePrice5") = 0
End If
If arr_Product(39) <> "" Then
oProduct.Fields("PostOffCasePrice6") =
arr_Product(39)
Else
oProduct.Fields("PostOffCasePrice6") = 0
End If
If arr_Product(40) <> "" Then
oProduct.Fields("PostOffCasePrice7") =
arr_Product(40)
Else
oProduct.Fields("PostOffCasePrice7") = 0
End If
If arr_Product(41) <> "" Then
oProduct.Fields("PostOffCasePrice8") =
arr_Product(41)
Else
oProduct.Fields("PostOffCasePrice8") = 0
End If
If arr_Product(42) <> "" Then
oProduct.Fields("PostOffCasePrice9") =
arr_Product(42)
Else
oProduct.Fields("PostOffCasePrice9") = 0
End If
If arr_Product(43) <> "" Then
oProduct.Fields("PostOffCasePrice10") =
arr_Product(43)
Else
oProduct.Fields("PostOffCasePrice10") = 0
End If
If arr_Product(44) <> "" Then
oProduct.Fields("PostOffUnitPrice1") =
arr_Product(44)
Else
oProduct.Fields("PostOffUnitPrice1") = 0
End If
If arr_Product(45) <> "" Then
oProduct.Fields("PostOffUnitPrice2") =
arr_Product(45)
Else
oProduct.Fields("PostOffUnitPrice2") = 0
End If
If arr_Product(46) <> "" Then
oProduct.Fields("PostOffUnitPrice3") =
arr_Product(46)
Else
oProduct.Fields("PostOffUnitPrice3") = 0
End If
If arr_Product(47) <> "" Then
oProduct.Fields("PostOffUnitPrice4") =
arr_Product(47)
Else
oProduct.Fields("PostOffUnitPrice4") = 0
End If
If arr_Product(48) <> "" Then
oProduct.Fields("PostOffUnitPrice5") =
arr_Product(48)
Else
oProduct.Fields("PostOffUnitPrice5") = 0
End If
If arr_Product(49) <> "" Then
oProduct.Fields("PostOffUnitPrice6") =
arr_Product(49)
Else
oProduct.Fields("PostOffUnitPrice6") = 0
End If
If arr_Product(50) <> "" Then
oProduct.Fields("PostOffUnitPrice7") =
arr_Product(50)
Else
oProduct.Fields("PostOffUnitPrice7") = 0
End If
If arr_Product(51) <> "" Then
oProduct.Fields("PostOffUnitPrice8") =
arr_Product(51)
Else
oProduct.Fields("PostOffUnitPrice8") = 0
End If
If arr_Product(52) <> "" Then
oProduct.Fields("PostOffUnitPrice9") =
arr_Product(52)
Else
oProduct.Fields("PostOffUnitPrice9") = 0
End If
If arr_Product(53) <> "" Then
oProduct.Fields("PostOffUnitPrice10") =
arr_Product(53)
Else
oProduct.Fields("PostOffUnitPrice10") = 0
End If
If arr_Product(54) <> 0 Then
oProduct.Fields("PostOffExp") = DateConvert(arr_Product(54))
'If gPSEdition = "RB" Then
oProduct.Fields("SupplierCode") = arr_Product(55)
If arr_Product(56) <> "" Then
oProduct.Fields("RetailPack") = arr_Product(56)
End If
oProduct.Fields("Draft") = arr_Product(57)
oProduct.Fields("PullDateType") = arr_Product(58)
oProduct.Fields("ShelfLife") = arr_Product(59)
oProduct.Fields("Volume") = arr_Product(60)
oProduct.Fields("CompetitiveItem") = arr_Product(61)
'allocated = arr_Product(62)
'special order = arr_Product(63)
'seasonal = arr_Product(64)
'closeout = arr_Product(65)
oProduct.Fields("BrandID") = arr_Product(66)
Else
If arr_Product(1) = "TRAILER" Then
Exit Do
End If
End If
Loop
oProduct.Update
oProduct.Close
Else
MsgBox "Failed"
End If
tempTimeEnd = Now
Label1.Caption = "Transmission Complete." & tempTimeStart & " >>> "
& tempTimeEnd
filFile.Close
Screen.MousePointer = 0
End Sub