Solved

code takes a Long time to run

Posted on 2013-06-10
12
420 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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