|
 |
activex_data_objects thread: need help on a code that deals with VB + ADOB.Connection + MessageDigest
Message #1 by "Leyla Yerlikaya" <leylay@h...> on Mon, 28 Jan 2002 19:49:59
|
|
Hi everybody,
First of all, I need to let you know that I am not a programmer, and I am
in trouble with a VB program that I had to write.
I have created a VB document using MSWord. A small part of the code is as
follows. This code basically, connects to a dbf file using
ADODB.Connection, and creates encrypted columns (using messagedigest). The
dataset I am working on has approximately 400,000 records. Although the
code runs correctly, but it takes 2 minutes to effect 20 records, which
means it will take app. 15 days to encrypt 400,000 records. I need to find
a way to reduce this execution time to one hour.
I will appreciate any kind information to solve my problem. I can send the
entire code if it would be helpful. The following is only a small portion
to provide you with some information.
I am looking forward to hearing from you.
Thanks,
leyla
*********
' Add a field named column1 to the table
If CheckBox1.Value = True Then
rs.Open "ALTER TABLE " & FileName & " ADD COLUMN " & Column1 & " CHAR
(16)", mcn
TextBox1.Text = TextBox1.Text & "...New column " & Column1 & " is
added..."
If CheckBox2.Value = True Then
MsgBox ("Added " & Column1 & " column")
End If
End If
' Add a field named column2 to the table
If CheckBox1.Value = True Then
rs.Open "ALTER TABLE " & FileName & " ADD COLUMN " & Column2 & " CHAR
(16)", mcn
TextBox1.Text = TextBox1.Text & "...New column " & Column2 & " is
added..."
If CheckBox2.Value = True Then
MsgBox ("Added " & Column2 & " column")
End If
End If
' Get the info from table (SELECT)
rs.Open "SELECT id_number, first_name, mid_name, last_name, post_name,
line1, line2, city, state, " _
& Column1 & ", " & Column2 & _
" FROM " & FileName & " ", mcn
TextBox1.Text = "SQL Query Select is executed..."
If CheckBox2.Value = True Then
MsgBox ("executed SQL Select")
End If
Report = TextBox1.Text
Do While loop_var
loop_var = Not rs.EOF
numberofrecs = numberofrecs + 1
' Calculate the first hash
rs.Fields(Column1).Value = encoder.CRC(UCase(Trim(rs.Fields
("first_name"))) & " " _
& UCase(Trim(rs.Fields("mid_name"))) & " " & UCase(Trim(rs.Fields
("last_name"))) _
& " " & UCase(Trim(rs.Fields("post_name"))))
' Calculate the second hash
rs.Fields(Column2).Value = encoder.CRC(UCase(Trim(rs.Fields
("line1"))) _
& " " & UCase(Trim(rs.Fields("line2"))) & " " & UCase(Trim
(rs.Fields("city"))) _
& " " & UCase(Trim(rs.Fields("state"))))
Message #2 by "David Adams" <davidadams00@h...> on Mon, 28 Jan 2002 15:05:36 -0500
|
|
Just a few questions:
1) I assume Encoder is a separate class or dll.
2) Is the ALTER Table a requirement? If so, is it a requirement that you do
it multiple times?
These are just two things that jumped out at me as possible areas to focus
attempts to improve performance. In either the case of the Class or DLL for
Encoder or the ALTER TABLE statement, your best case is to minimize the
iterations that you do similar operations (especially if the process is
accomplished over a non-local connection).
Hope this gives you a few ideas of where to start.
David Adams
----- Original Message -----
From: "Leyla Yerlikaya" <leylay@h...>
To: "ActiveX_Data_Objects" <activex_data_objects@p...>
Sent: Monday, January 28, 2002 7:49 PM
Subject: [activex_data_objects] need help on a code that deals with VB +
ADOB.Connection + MessageDigest
> Hi everybody,
>
> First of all, I need to let you know that I am not a programmer, and I am
> in trouble with a VB program that I had to write.
>
> I have created a VB document using MSWord. A small part of the code is as
> follows. This code basically, connects to a dbf file using
> ADODB.Connection, and creates encrypted columns (using messagedigest). The
> dataset I am working on has approximately 400,000 records. Although the
> code runs correctly, but it takes 2 minutes to effect 20 records, which
> means it will take app. 15 days to encrypt 400,000 records. I need to find
> a way to reduce this execution time to one hour.
>
> I will appreciate any kind information to solve my problem. I can send the
> entire code if it would be helpful. The following is only a small portion
> to provide you with some information.
>
> I am looking forward to hearing from you.
>
> Thanks,
>
> leyla
>
> *********
>
> ' Add a field named column1 to the table
> If CheckBox1.Value = True Then
> rs.Open "ALTER TABLE " & FileName & " ADD COLUMN " & Column1 & " CHAR
> (16)", mcn
> TextBox1.Text = TextBox1.Text & "...New column " & Column1 & " is
> added..."
> If CheckBox2.Value = True Then
> MsgBox ("Added " & Column1 & " column")
> End If
>
> End If
>
> ' Add a field named column2 to the table
> If CheckBox1.Value = True Then
> rs.Open "ALTER TABLE " & FileName & " ADD COLUMN " & Column2 & " CHAR
> (16)", mcn
> TextBox1.Text = TextBox1.Text & "...New column " & Column2 & " is
> added..."
> If CheckBox2.Value = True Then
> MsgBox ("Added " & Column2 & " column")
> End If
> End If
> ' Get the info from table (SELECT)
> rs.Open "SELECT id_number, first_name, mid_name, last_name, post_name,
> line1, line2, city, state, " _
> & Column1 & ", " & Column2 & _
> " FROM " & FileName & " ", mcn
>
> TextBox1.Text = "SQL Query Select is executed..."
> If CheckBox2.Value = True Then
> MsgBox ("executed SQL Select")
> End If
>
> Report = TextBox1.Text
>
> Do While loop_var
>
> loop_var = Not rs.EOF
> numberofrecs = numberofrecs + 1
>
> ' Calculate the first hash
> rs.Fields(Column1).Value = encoder.CRC(UCase(Trim(rs.Fields
> ("first_name"))) & " " _
> & UCase(Trim(rs.Fields("mid_name"))) & " " & UCase(Trim(rs.Fields
> ("last_name"))) _
> & " " & UCase(Trim(rs.Fields("post_name"))))
>
> ' Calculate the second hash
> rs.Fields(Column2).Value = encoder.CRC(UCase(Trim(rs.Fields
> ("line1"))) _
> & " " & UCase(Trim(rs.Fields("line2"))) & " " & UCase(Trim
> (rs.Fields("city"))) _
> & " " & UCase(Trim(rs.Fields("state"))))
>
>
$subst('Email.Unsub').
>
Message #3 by "Leyla Yerlikaya" <leylay@h...> on Mon, 28 Jan 2002 20:22:49
|
|
<html><div style='background-color:'><DIV>
<P>David,</P>
<P>I think the problem is starting after ALTER TABLE statement. The encoder is stamin.dll. If you don't mind I am sending the
code to you. I am not running a database, I am connecting to a database using ODBC driver.</P>
<P>I will appreciate any kind of information on this matter. </P>
<P>Thanks,</P>
<P>leyla</P>
<P>******************</P>
<P>Option Compare Database<BR>Option Explicit</P>
<P>Option Explicit<BR>Public mcn As New ADODB.Connection<BR>Public rs As New ADODB.Recordset<BR>Public
connstr As String</P>
<P>' Function to calculate the MD5<BR>Private Sub CreateKeys()</P>
<P>' ODBC Driver Name<BR>Dim Dir_File As String<BR>Dir_File = DirectoryBox.Text<BR>connstr =
"DRIVER=Microsoft Visual FoxPro
Driver;UID=;Deleted=Yes;Null=Yes;Collate=Machine;BackgroundFetch=Yes;Exclusive=yes;SourceType=DBF;SourceDB=" & Dir_File
& " "</P>
<P>mcn.Open connstr<BR>mcn.CommandTimeout = 0</P>
<P>Dim rs As New Recordset<BR>Dim encoder As New MessageDigest.Stamina<BR>Dim rec As Long<BR>Dim upd As
Long<BR>Dim numberofrecs As Long<BR>Dim FileName As String<BR>Dim Column1 As String<BR>Dim Column2 As
String<BR>Dim Report As String<BR>Dim loop_var As Boolean</P>
<P>'rs.CursorType = adOpenDynamic</P>
<P>loop_var = True</P>
<P>If BatchBox.Value = True Then<BR> mcn.CursorLocation = adUseClient<BR>
rs.LockType = adLockBatchOptimistic<BR> rs.CursorType =
adOpenStatic<BR>Else<BR> mcn.CursorLocation = adUseServer<BR> rs.LockType
= adLockOptimistic<BR> rs.CursorType = adOpenForwardOnly<BR>End
If<BR> <BR>' File name of the database file (DBASE)<BR>FileName =
FileNameBox.Text<BR>Column1 = Column1Box.Text<BR>Column2 = Column2Box.Text</P>
<P>numberofrecs = 0</P>
<P>' Initialize the text box<BR>TextBox1.Text = "Connection to database is established"</P>
<P><BR>' Add a field named column1 to the table<BR>If CheckBox1.Value = True Then<BR> rs.Open
"ALTER TABLE " & FileName & " ADD COLUMN " & Column1 & " CHAR(16)", mcn<BR>
TextBox1.Text = TextBox1.Text & "...New column " & Column1 & " is added..."<BR> If
CheckBox2.Value = True Then<BR> MsgBox ("Added " & Column1 & "
column")<BR> End If</P>
<P>End If</P>
<P>' Add a field named column2 to the table<BR>If CheckBox1.Value = True Then<BR> rs.Open "ALTER TABLE "
& FileName & " ADD COLUMN " & Column2 & " CHAR(16)", mcn<BR> TextBox1.Text =
TextBox1.Text & "...New column " & Column2 & " is added..."<BR> If CheckBox2.Value = True
Then<BR> MsgBox ("Added " & Column2 & " column")<BR> End
If<BR>End If<BR> <BR>' Get the info from table (SELECT)<BR> rs.Open "SELECT
id_number, first_name, mid_name, last_name, post_name, line1, line2, city, state, " _<BR> &
Column1 & ", " & Column2 & _<BR> " FROM " & FileName & " ",
mcn<BR> <BR> TextBox1.Text = "SQL Query Select is
executed..."<BR> If CheckBox2.Value = True Then<BR> MsgBox ("executed
SQL Select")<BR> End If</P>
<P> Report = TextBox1.Text<BR> <BR> Do While
loop_var<BR>
<BR> loop_var = Not
rs.EOF<BR> numberofrecs = numberofrecs +
1<BR>
<BR> ' Calculate the first
hash<BR> rs.Fields(Column1).Value =
encoder.CRC(UCase(Trim(rs.Fields("first_name"))) & " "
_<BR> &
UCase(Trim(rs.Fields("mid_name"))) & " " & UCase(Trim(rs.Fields("last_name")))
_<BR> & " " &
UCase(Trim(rs.Fields("post_name"))))<BR> &n
bsp; <BR> ' Calculate the second
hash<BR> rs.Fields(Column2).Value =
encoder.CRC(UCase(Trim(rs.Fields("line1")))
_<BR> & " " &
UCase(Trim(rs.Fields("line2"))) & " " & UCase(Trim(rs.Fields("city")))
_<BR> & " " &
UCase(Trim(rs.Fields("state"))))<BR>
<BR>
<BR> ' Display the current record being
processed<BR> TextBox1.Text = Report &
"..Number of records processed:" & numberofrecs & "
"<BR>
<BR> ' Advance to the next
record<BR>
rs.MoveNext<BR>
<BR> If CheckBox2.Value = True
Then<BR> If (numberofrecs
Mod TextBox2.Value) = 0
Then<BR> &nbs
p; MsgBox ("Updated record " &
numberofrecs)<BR> End
If<BR> End If</P>
<P> ' if batch then push the data in chunks to
the file...<BR> If BatchBox.Value = True
Then<BR> If
(numberofrecs Mod BatchTextBox.Value) = 0
Then<BR> &nbs
p;
rs.UpdateBatch<BR> &nbs
p; If CheckBox2.Value = True
Then<BR> &nbs
p; MsgBox ("pushed " & BatchTextBox.Value & " record to
database")<BR> &a
mp;nbsp; End
If<BR>
TextBox1.Text = TextBox1.Text & " " & BatchTextBox.Value & " records
pushed"<BR> End
If<BR> End If</P>
<P> If StopBox.Value = True And
StopTextBox.Value = numberofrecs
Then<BR>
loop_var = False<BR> End
If<BR> Loop</P>
<P>' Push the changes, commit...<BR>If BatchBox.Value = True Then<BR>
rs.UpdateBatch<BR>Else<BR> rs.Update<BR>End If</P>
<P>' Close the record set<BR>rs.Close</P>
<P>' Close the connection<BR>mcn.Close</P>
<P>MsgBox (numberofrecs & " rows in file " & FileName & " are affected...")</P>
<P>End Sub</P>
<P><BR>Private Sub BatchBox_Click()<BR> If BatchBox.Value = True
Then<BR> <BR>
BatchTextBox.Visible = True<BR> Label6.Visible =
True<BR> <BR>
Else<BR> BatchTextBox.Visible =
False<BR> Label6.Visible =
False<BR> <BR> End
If<BR>End Sub</P>
<P>Private Sub CheckBox1_Click()<BR> If CheckBox1.Value = True
Then<BR> Column1Box.Visible =
True<BR> Column2Box.Visible =
True<BR> Label3.Visible =
True<BR> Label4.Visible =
True<BR> <BR>
Else<BR> Column1Box.Visible =
False<BR> Column2Box.Visible =
False<BR> Label3.Visible =
False<BR> Label4.Visible =
False<BR> <BR> End If<BR>End Sub</P>
<P>Private Sub CheckBox2_Click()<BR> If CheckBox2.Value = True
Then<BR> Label1.Visible =
True<BR> TextBox2.Visible =
True<BR> Else<BR>
Label1.Visible = False<BR> TextBox2.Visible =
False<BR> <BR> End If<BR>End Sub</P>
<P>Private Sub CommandButton1_Click()<BR> ' In the event of button click call CreateKeys
function<BR> CreateKeys<BR>End Sub</P>
<P> </P>
<P>Private Sub Command0_Enter()</P>
<P>End Sub</P>
<P>Private Sub Form_Current()</P>
<P>End Sub<BR><BR></P></DIV></div><br clear=all><hr>Chat with friends online, try
MSN Messenger: <a href='http://go.msn.com/bql/hmtag1_etl_EN.asp'>Click Here</a><br></html>
Message #4 by "David Adams" <davidadams00@h...> on Mon, 28 Jan 2002 15:56:49 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_007C_01C1A814.65143540
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Can't say that this will hurt or help.
From just a process flow.
You might try to process your ALTER TABLE statement with a
Connection.Execute. Instead of settting the Connection property of a
Recordset and running your ALTER SQL against a Recordset.Open. Perform
it as a Connection.Execute. Also, it might be more valuable to handle
all of the ALTER TABLE commands as one Execute statement instead of
multiple ones.
Basically, use your If-Then-Else structures to create the ALTER TABLE
statements, then have only one Connection.Execute. That assumes that
you can do your ALTER statements as one process instead of more than
one. That might get you some speed.
As for the DLL, I wouldnt think that a local DLL would not cause that
much problem. However, you might want to do 1 transfer to the DLL
instead of multiple communications to it. If it is on a remote machine,
reducing the multiple trips might help performance as well.
David Adams
----- Original Message -----
From: Leyla Yerlikaya
To: ActiveX_Data_Objects
Sent: Monday, January 28, 2002 8:22 PM
Subject: [activex_data_objects] Re: need help on a code that deals
with VB + ADOB.Connection + MessageDigest
David,
I think the problem is starting after ALTER TABLE statement. The
encoder is stamin.dll. If you don't mind I am sending the code to you. I
am not running a database, I am connecting to a database using ODBC
driver.
I will appreciate any kind of information on this matter.
Thanks,
leyla
******************
Option Compare Database
Option Explicit
Option Explicit
Public mcn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public connstr As String
' Function to calculate the MD5
Private Sub CreateKeys()
' ODBC Driver Name
Dim Dir_File As String
Dir_File =3D DirectoryBox.Text
connstr =3D "DRIVER=3DMicrosoft Visual FoxPro
Driver;UID=3D;Deleted=3DYes;Null=3DYes;Collate=3DMachine;BackgroundFetch=3D
Yes;Exclusive=3Dyes;SourceType=3DDBF;SourceDB=3D" & Dir_File & " "
mcn.Open connstr
mcn.CommandTimeout =3D 0
Dim rs As New Recordset
Dim encoder As New MessageDigest.Stamina
Dim rec As Long
Dim upd As Long
Dim numberofrecs As Long
Dim FileName As String
Dim Column1 As String
Dim Column2 As String
Dim Report As String
Dim loop_var As Boolean
'rs.CursorType =3D adOpenDynamic
loop_var =3D True
If BatchBox.Value =3D True Then
mcn.CursorLocation =3D adUseClient
rs.LockType =3D adLockBatchOptimistic
rs.CursorType =3D adOpenStatic
Else
mcn.CursorLocation =3D adUseServer
rs.LockType =3D adLockOptimistic
rs.CursorType =3D adOpenForwardOnly
End If
' File name of the database file (DBASE)
FileName =3D FileNameBox.Text
Column1 =3D Column1Box.Text
Column2 =3D Column2Box.Text
numberofrecs =3D 0
' Initialize the text box
TextBox1.Text =3D "Connection to database is established"
' Add a field named column1 to the table
If CheckBox1.Value =3D True Then
rs.Open "ALTER TABLE " & FileName & " ADD COLUMN " & Column1 & "
CHAR(16)", mcn
TextBox1.Text =3D TextBox1.Text & "...New column " & Column1 & " is
added..."
If CheckBox2.Value =3D True Then
MsgBox ("Added " & Column1 & " column")
End If
End If
' Add a field named column2 to the table
If CheckBox1.Value =3D True Then
rs.Open "ALTER TABLE " & FileName & " ADD COLUMN " & Column2 & "
CHAR(16)", mcn
TextBox1.Text =3D TextBox1.Text & "...New column " & Column2 & " is
added..."
If CheckBox2.Value =3D True Then
MsgBox ("Added " & Column2 & " column")
End If
End If
' Get the info from table (SELECT)
rs.Open "SELECT id_number, first_name, mid_name, last_name,
post_name, line1, line2, city, state, " _
& Column1 & ", " & Column2 & _
" FROM " & FileName & " ", mcn
TextBox1.Text =3D "SQL Query Select is executed..."
If CheckBox2.Value =3D True Then
MsgBox ("executed SQL Select")
End If
Report =3D TextBox1.Text
Do While loop_var
loop_var =3D Not rs.EOF
numberofrecs =3D numberofrecs + 1
' Calculate the first hash
rs.Fields(Column1).Value =3D
encoder.CRC(UCase(Trim(rs.Fields("first_name"))) & " " _
& UCase(Trim(rs.Fields("mid_name"))) & " " &
UCase(Trim(rs.Fields("last_name"))) _
& " " & UCase(Trim(rs.Fields("post_name"))))
' Calculate the second hash
rs.Fields(Column2).Value =3D
encoder.CRC(UCase(Trim(rs.Fields("line1"))) _
& " " & UCase(Trim(rs.Fields("line2"))) & " " &
UCase(Trim(rs.Fields("city"))) _
& " " & UCase(Trim(rs.Fields("state"))))
' Display the current record being processed
TextBox1.Text =3D Report & "..Number of records processed:" &
numberofrecs & " "
' Advance to the next record
rs.MoveNext
If CheckBox2.Value =3D True Then
If (numberofrecs Mod TextBox2.Value) =3D 0 Then
MsgBox ("Updated record " & numberofrecs)
End If
End If
' if batch then push the data in chunks to the file...
If BatchBox.Value =3D True Then
If (numberofrecs Mod BatchTextBox.Value) =3D 0 Then
rs.UpdateBatch
If CheckBox2.Value =3D True Then
MsgBox ("pushed " & BatchTextBox.Value & " record to
database")
End If
TextBox1.Text =3D TextBox1.Text & " " & BatchTextBox.Value
& " records pushed"
End If
End If
If StopBox.Value =3D True And StopTextBox.Value =3D
numberofrecs Then
loop_var =3D False
End If
Loop
' Push the changes, commit...
If BatchBox.Value =3D True Then
rs.UpdateBatch
Else
rs.Update
End If
' Close the record set
rs.Close
' Close the connection
mcn.Close
MsgBox (numberofrecs & " rows in file " & FileName & " are
affected...")
End Sub
Private Sub BatchBox_Click()
If BatchBox.Value =3D True Then
BatchTextBox.Visible =3D True
Label6.Visible =3D True
Else
BatchTextBox.Visible =3D False
Label6.Visible =3D False
End If
End Sub
Private Sub CheckBox1_Click()
If CheckBox1.Value =3D True Then
Column1Box.Visible =3D True
Column2Box.Visible =3D True
Label3.Visible =3D True
Label4.Visible =3D True
Else
Column1Box.Visible =3D False
Column2Box.Visible =3D False
Label3.Visible =3D False
Label4.Visible =3D False
End If
End Sub
Private Sub CheckBox2_Click()
If CheckBox2.Value =3D True Then
Label1.Visible =3D True
TextBox2.Visible =3D True
Else
Label1.Visible =3D False
TextBox2.Visible =3D False
End If
End Sub
Private Sub CommandButton1_Click()
' In the event of button click call CreateKeys function
CreateKeys
End Sub
Private Sub Command0_Enter()
End Sub
Private Sub Form_Current()
End Sub
-------------------------------------------------------------------------
-----
Chat with friends online, try MSN Messenger: Click Here
$subst('Email.Unsub').
|
|
|
View Cart
