Solved

code takes a Long time to run

Posted on 2013-06-10
12
405 Views
Last Modified: 2013-06-10
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

Open in new window

0
Comment
Question by:al4629740
  • 8
  • 4
12 Comments
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
did you hear about MERGE???
0
 

Author Comment

by:al4629740
Comment Utility
No.  Give me an example of how I might use it please.
0
 

Author Comment

by:al4629740
Comment Utility
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

Open in new window

0
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
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...
0
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
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
0
 
LVL 4

Accepted Solution

by:
BAKADY earned 500 total points
Comment Utility
PROBLEM #2:

too many database requests:

it won't me wonder your network crash!!!

this case is appropriate to use stored procedures...
it means you don't need to call any record from the server to check the values in them,...
you will send your parameter to the sql server and the code run there...
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:al4629740
Comment Utility
ok,  keep the advice coming
0
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
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 )---
0
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
you don't chance the data in your form, you just check it into the database... right???
0
 

Author Comment

by:al4629740
Comment Utility
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?
0
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
If I make a stored procedure, how do I call the stored procedure from vb6?
Set Recordset = Connection.Execute ( "CALL YourStoredProcedure(Param1, Param2,...)")

Open in new window

0
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
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

Open in new window

with some code like this you can create dynamic sql-commands in your program and pass them like a stored procedure...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now