?
Solved

code takes a Long time to run

Posted on 2013-06-10
12
Medium Priority
?
424 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
12 Comments
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236114
did you hear about MERGE???
0
 

Author Comment

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

Author Comment

by:al4629740
ID: 39236231
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:BAKADY
ID: 39236239
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
ID: 39236260
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 2000 total points
ID: 39236273
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
 

Author Comment

by:al4629740
ID: 39236275
ok,  keep the advice coming
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236290
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
ID: 39236296
you don't chance the data in your form, you just check it into the database... right???
0
 

Author Comment

by:al4629740
ID: 39236310
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
ID: 39236320
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
ID: 39236347
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

762 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