code takes a Long time to run
Admittedly I have made many errors in the way of I have designed my SQL database. The below vb6 code takes very long to execute. Are there any things that you notice that might help me speed up the process?
Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
If rec.State = adStateOpen Then
rec.Close
End If
If conn.State = adStateClosed Then
conn.Open "Provider=sqloledb;Data Source=" & ConnectionIP & ",1455;Network Library=DBMSSOCN;Initial Catalog= CAPRegistration; User ID=sa;Password=xxxx"
End If
Dim rsClient As New ADODB.Recordset
Dim ServerTime As Variant
esql = "select GetDate()"
rec.Open (esql), conn, adOpenStatic, adLockReadOnly
ServerTime = rec.Fields(0)
rec.Close
'----This section checks to see if there are ANY STUDENTS-------------------
esql = " select count(*) From tblRegistration where Fiscal = '" & Fiscal & "' And Combo11 = '" & Combo2 & "' "
rec.CursorType = adOpenDynamic
rec.CursorLocation = adUseClient
rec.LockType = adLockOptimistic
rec.Open esql, conn, , , adCmdText
If rec.Fields(0) = 0 Then
Splash4.Hide
MsgBox "You have no Individuals in the database to create this Attendance List", vbOKOnly, "No List Found"
Splash4.Hide
Exit Sub
End If
If rec.State = adStateOpen Then
rec.Close
End If
'This section selects the people from Registration that will be put into Attendance------------
esql = " select * From tblRegistration where Fiscal = '" & Fiscal & "' And Combo11 = '" & Combo2 & "' "
rec.CursorType = adOpenDynamic
rec.CursorLocation = adUseClient
rec.LockType = adLockOptimistic
rec.Open esql, conn, , , adCmdText
Do Until rec.EOF
'-----This section will check for duplicates--------------------------------------
If Combo4.Visible = False Then
If Combo1 = "Core" Then
Set rsClient = conn.Execute("Select ID from AttendanceGrid Where Fiscal = '" & Fiscal & "' And [ID] = '" & rec!ID & "' And [Last Name] = '" & rec!Text4 & "' And [First Name] = '" & rec!Text5 & "' And Committee = '" & rec!Combo11 & "' And Month = '" & Combo3.Text & "' And Activity = '" & cboActivity.Text & "'")
End If
End If
'-------This section will insert the data into AttendanceGrid-----------------------------
If rsClient.BOF = True Then 'no duplicate found.. proceed to insert record
If Combo4.Visible = False Then
If Combo1 = "Core" Then
conn.Execute ("insert into AttendanceGrid (ID,Nu,[Client ID],[Last Name],[First Name],Committee,BirthDate,Month,Activity,ProgType,[CSW/TitleXX],Fiscal,EntryTime,[From],[To],ActivityLength) VALUES ('" & rec("ID").Value & "','" & rec("Text22").Value & "','" & rec("Text1").Value & "','" & rec("Text4").Value & "','" & rec("Text5").Value & "','" & rec("Combo11").Value & "','" & rec("Text7").Value & "','" & Combo3.Text & "','" & cboActivity.Text & "','Core','" & rec![CSW/TitleXX] & "','" & Fiscal & "','" & ServerTime & "','" & Combo8 & "','" & Combo9 & "','" & Label15 & "')")
End If
End If
End If
rec.MoveNext
Loop
Set rsClient = Nothing
If rec.State = adStateOpen Then
rec.Close
End If
'----------Update the Attendance with Terminated-------------
If Not Combo2 = "IVPA-Advancing Youth Development" Then
Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
If conn.State = adStateClosed Then
conn.Open "Provider=sqloledb;Data Source=" & ConnectionIP & ",1455;Network Library=DBMSSOCN;Initial Catalog= CAPRegistration; User ID=sa;Password=xxxxxxx"
End If
Dim sStringtoEx As String
Dim sStringtoEx2 As String
sStringtoEx = "Update AttendanceGrid Set AttendanceGrid.[CSW/TitleXX] = tblRegistration.[CSW/TitleXX] FROM AttendanceGrid INNER JOIN tblRegistration on AttendanceGrid.id = tblRegistration.id"
Set rec = conn.Execute(sStringtoEx)
sStringtoEx2 = "Update AttendanceGrid Set X = case when tblRegistration.Overage = 3 THEN 'X' else '' end FROM tblRegistration INNER JOIN AttendanceGrid on tblRegistration.id=AttendanceGrid.id "
Set rec = conn.Execute(sStringtoEx2)
End If
'------------------------------------------------------------
Splash4.Hide
MsgBox "You have updated your list for this month", vbOKOnly, "Records Updated"
Call Command1_Click
Splash4.Hide
BAKADY
did you hear about MERGE???
ASKER
No. Give me an example of how I might use it please.
ASKER
How can I simplify this section here
Do Until rec.EOF
'-----This section will check for duplicates--------------------------------------
If Combo4.Visible = False Then
If Combo1 = "Core" Then
Set rsClient = conn.Execute("Select ID from AttendanceGrid Where Fiscal = '" & Fiscal & "' And [ID] = '" & rec!ID & "' And [Last Name] = '" & rec!Text4 & "' And [First Name] = '" & rec!Text5 & "' And Committee = '" & rec!Combo11 & "' And Month = '" & Combo3.Text & "' And Activity = '" & cboActivity.Text & "'")
End If
End If
'-------This section will insert the data into AttendanceGrid-----------------------------
If rsClient.BOF = True Then 'no duplicate found.. proceed to insert record
If Combo4.Visible = False Then
If Combo1 = "Core" Then
conn.Execute ("insert into AttendanceGrid (ID,Nu,[Client ID],[Last Name],[First Name],Committee,BirthDate,Month,Activity,ProgType,[CSW/TitleXX],Fiscal,EntryTime,[From],[To],ActivityLength) VALUES ('" & rec("ID").Value & "','" & rec("Text22").Value & "','" & rec("Text1").Value & "','" & rec("Text4").Value & "','" & rec("Text5").Value & "','" & rec("Combo11").Value & "','" & rec("Text7").Value & "','" & Combo3.Text & "','" & cboActivity.Text & "','Core','" & rec![CSW/TitleXX] & "','" & Fiscal & "','" & ServerTime & "','" & Combo8 & "','" & Combo9 & "','" & Label15 & "')")
End If
End If
End If
rec.MoveNext
Loop
if i really understood your code, you try to INSERT and/or UPDATE data from tblRegistration into AttendanceGrid... right???
and this in VB6... -- don't worry, i made this mistake too -- !!!!
ok... if it's working, perfect you got that!!!!
now you have to take care about performance,... oh yeah... i forgot "code takes a Long time to run"...
i can said "here is something about MERGE, have a nice day"... ;)
but no, i will help you
MERGE or sometimes called UPSERT insert or update record if the conditions are matched or not, but this is not your problem number one...
and this in VB6... -- don't worry, i made this mistake too -- !!!!
ok... if it's working, perfect you got that!!!!
now you have to take care about performance,... oh yeah... i forgot "code takes a Long time to run"...
i can said "here is something about MERGE, have a nice day"... ;)
but no, i will help you
MERGE or sometimes called UPSERT insert or update record if the conditions are matched or not, but this is not your problem number one...
PROBLEM #1:
your variables: Fiscal, Combo2, Combo4, ... etc.
i know, you can use objects, it makes easy, you don't need to declare variables...
use datatypes and real names, like example:
Dim strFiscal as String
strFiscal = Fiscal.Text
str for String, and Fiscal as identifier
this help you and other programmers to understand your code... in 2 or 3 years you will not remember what you are thinking today...!!!
but seems that you understand your code and you can do this alone,
then we handle the problem number 2
your variables: Fiscal, Combo2, Combo4, ... etc.
i know, you can use objects, it makes easy, you don't need to declare variables...
use datatypes and real names, like example:
Dim strFiscal as String
strFiscal = Fiscal.Text
str for String, and Fiscal as identifier
this help you and other programmers to understand your code... in 2 or 3 years you will not remember what you are thinking today...!!!
but seems that you understand your code and you can do this alone,
then we handle the problem number 2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, keep the advice coming
First homework:
make list of your objects or variables you are passing into your vb6-modul -- just they used into sql-comands (select, insert, update or delete )---
make list of your objects or variables you are passing into your vb6-modul -- just they used into sql-comands (select, insert, update or delete )---
you don't chance the data in your form, you just check it into the database... right???
ASKER
In this procedure in only put it into the table.
In another form I bring it up.
If I make a stored procedure, how do I call the stored procedure from vb6?
In another form I bring it up.
If I make a stored procedure, how do I call the stored procedure from vb6?
If I make a stored procedure, how do I call the stored procedure from vb6?
Set Recordset = Connection.Execute ( "CALL YourStoredProcedure(Param1, Param2,...)")
i have prepare something... it have took a little bit...
'reset esql
esql = ""
'----This section set your values, whereever their come -------------------
esql = esql & "With qryConfig (ServerTime, Fiscal, Combo1, Combo2, Combo3, Combo4Visible, cboActivity, Combo8, Combo9, Label15) As (" & vbNewLine
esql = esql & " Select GetDate()" & vbNewLine
esql = esql & " , '" & Fiscal.Text & "'" & vbNewLine
esql = esql & " , '" & Combo1.Text & "'" & vbNewLine
esql = esql & " , '" & Combo2.Text & "'" & vbNewLine
esql = esql & " , '" & Combo3.Text & "'" & vbNewLine
esql = esql & " , '" & CStr(Combo4.Visible) & "'" & vbNewLine
esql = esql & " , '" & cboActivity.Text & "'" & vbNewLine
esql = esql & " , '" & Combo8.Text & "'" & vbNewLine
esql = esql & " , '" & Combo9.Text & "'" & vbNewLine
esql = esql & " , '" & Label15.Caption & "'" & vbNewLine
'This section selects the people from Registration that will be put into Attendance------------
esql = esql & "), qryRegistration (... the table fields ...)" & vbNewLine
esql = esql & " Select *" & vbNewLine
esql = esql & " From tblRegistration T, qryConfig Q" & vbNewLine
esql = esql & " Where T.Fiscal = Q.Fiscal" & vbNewLine
esql = esql & " And Q.Combo11 = Q.Combo2" & vbNewLine
'----This section checks to see if there are ANY STUDENTS-------------------
esql = esql & "), qryCheckAnyStudents (... the table fields ...)" & vbNewLine
esql = esql & " Select count(*)" & vbNewLine
esql = esql & " From qryRegistration" & vbNewLine
esql = esql & "), ...." & vbNewLine