Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2012
    Posts
    1

    Unanswered: SQL in VB runs Slow

    Hello;

    Most of what I know about SQL and VB has been from searching message boards such as this and applying what I learn to my projects. So - this is just to point out that I've not had any formal training in these things, so something in my setup may be completely inefficient.

    I have a relatively simple query written in VB that is fired from a Form and uses 2 textboxes in that form as criteria, and the results are then passed to textboxes in a subform. Also, the subform has other textboxes that are linked to a table, and the user can edit those fields using those textboxes.

    The first time the database is opened and the form is used to run the SQL, it takes a long time - at least a minute. If you close and open back up the form and run it again (still in the same session of Access) it runs a bit quicker. But, if I just use Query Builder and recreate the query there, it runs in <2 seconds every single time.

    So, first, why would the query run slower when initated from VB via a button click?

    Second, why does it run very slow the first time, and then faster subsequent times? I have certain fields indexed already, but those indices should already exist, right? In other words, should there be new indices created automatically and used the first time Access is opened, when then speed up subsequent queries?

    Forgive me if any of this is just completely not the way it should be set up - I usually know just enough about this stuff to get myself in trouble, and can usually search and find a solution to my issues, but haven't been able to do so for this one.

    Here is the code that fires when the button is clicked on the form:

    Code:
    Option Compare Database
    
    Private Sub Command4_Click()
    
    If IsNull([txtEXPDB]) Or IsNull([txtAccCode]) Or [txtEXPDB] = "" Or [txtAccCode] = "" Then
    MsgBox ("You must enter a DB and Account Code")
    
    GoTo exitFormNoInput
    
    End If
    
    Me.Refresh
    
    Dim strSQL  As String
      Dim db      As DAO.Database
      Dim rs      As DAO.Recordset
      
      
      Set db = CurrentDb
      
      On Error GoTo exitForm_Current
      
      strSQL = "SELECT tblPrequalified.Database, tblPrequalified.AccCode, US_Hierarchy.RDS, PQ_RDS_Account_Counts.StoreCount, " & _
      "PQ_RDS_Account_Counts.PotentialSlots, PQ_RDS_Account_Counts.CurrentlyPrequalified, PQ_RDS_Account_Counts.SlotsAvailable, " & _
      "AccountSnapshot.accpbbusname, AccountSnapshot.[Curr-DG], AccountSnapshot.Notes, AccountSnapshot.Model, " & _
      "AccountSnapshot.NAGroup, AccountSnapshot.[3MoSales$], AccountSnapshot.[Prev3Sales$], AccountSnapshot.[12MoSales$], " & _
      "AccountSnapshot.[Prev12Sales$] " & _
      "FROM ((tblPrequalified INNER JOIN AccountSnapshot ON (tblPrequalified.AccCode = AccountSnapshot.acccode) AND " & _
      "(tblPrequalified.Database = AccountSnapshot.[database()])) INNER JOIN US_Hierarchy ON AccountSnapshot.PriSK = " & _
      "US_Hierarchy.[Store Key]) INNER JOIN PQ_RDS_Account_Counts ON US_Hierarchy.RDS = PQ_RDS_Account_Counts.RDS " & _
      "WHERE (((tblPrequalified.Database)='" & [Forms]![formEditPrequalInput]![txtEXPDB] & "') AND " & _
      "((tblPrequalified.AccCode)='" & [Forms]![formEditPrequalInput]![txtAccCode] & "'))"
      
      
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
      
          
      Forms![formEditPrequalInput]![Child5].Form![txtRDS] = rs![RDS] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txtStoreCount] = rs![StoreCount] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txtAllotted] = rs![PotentialSlots] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txtUsed] = rs![CurrentlyPrequalified] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txtAvailable] = rs![SlotsAvailable] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txtAccName] = rs![accpbbusname] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txtCurrDG] = rs![Curr-DG] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txtNotes] = rs![Notes] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txtModel] = rs![Model] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txtNAGroupDesc] = rs![NAGroup] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txt3MoSales] = rs![3MoSales$] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txtPrev3Sales] = rs![Prev3Sales$] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txt12moSales] = rs![12MoSales$] & ""
      
      Forms![formEditPrequalInput]![Child5].Form![txtPrev12Sales] = rs![Prev12Sales$] & ""
      
        
     Forms("formEditPrequalInput").Child5!InitialBLS.SetFocus
        
    exitForm_Current:
      
      Select Case Err.Number
    Case 3021
    MsgBox "Account Code does not appear to be in the Snapshot."
    Forms![formEditPrequalInput]![Child5].Form!txtRDS = ""
    Forms![formEditPrequalInput]![Child5].Form!txtStoreCount = ""
    Forms![formEditPrequalInput]![Child5].Form!txtAllotted = ""
    Forms![formEditPrequalInput]![Child5].Form!txtUsed = ""
    Forms![formEditPrequalInput]![Child5].Form!txtAvailable = ""
    Forms![formEditPrequalInput]![Child5].Form!txtAccName = ""
    Forms![formEditPrequalInput]![Child5].Form!txtCurrDG = ""
    Forms![formEditPrequalInput]![Child5].Form!txtNotes = ""
    Forms![formEditPrequalInput]![Child5].Form!txtModel = ""
    Forms![formEditPrequalInput]![Child5].Form!txtNAGroupDesc = ""
    Forms![formEditPrequalInput]![Child5].Form!txt3MoSales = ""
    Forms![formEditPrequalInput]![Child5].Form!txtPrev3Sales = ""
    Forms![formEditPrequalInput]![Child5].Form!txt12moSales = ""
    Forms![formEditPrequalInput]![Child5].Form!txtPrev12Sales = ""
    
      
      End Select
    
    exitFormNoInput:
    Exit Sub
    
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by NCSUAaron View Post
    So, first, why would the query run slower when initated from VB via a button click?
    Probably because the query is not persisted (or permanent: in does not appear in the Database window), then must be re-created and compiled each time you open the form. If there are many rows and if the SQL expression is complex, it can take a rather long time before the query returns a data set.

    You could try to create a persisted query and instanciate it:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Command4_Click()
    
        Dim strSQL  As String
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim rs As DAO.Recordset
      
        On Error GoTo exitForm_Current  
        If IsNull([txtEXPDB]) Or IsNull([txtAccCode]) Or [txtEXPDB] = "" Or [txtAccCode] = "" Then
            MsgBox ("You must enter a DB and Account Code")
            GoTo exitFormNoInput
        End If
        Me.Refresh
        strSQL = "SELECT tblPrequalified.Database, ...
        Set db = CurrentDb
        Set qdf = db.QueryDefs("Name of the Persisted Query")
        qdf.SQL = strSQL
        qdf.Close
        Set rs = db.OpenRecordset("Name of the Persisted Query")
    Instead of assigning the values from the Recordset to the controls of the subform, you could also try:
    Code:
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    Set Forms![formEditPrequalInput]![Child5].Form.Recordset = rs
    Generally speaking, it's a good idea to add the Option Explicit directive in the declaration section of every module. This can spare you numerous hours of debugging. Moreover, instead of:
    Code:
    Forms![formEditPrequalInput]![Child5].Form
    you can use:
    Code:
    Me.Child5.Form
    And if you repeatedly make reference to an object, you can also use:
    Code:
    With Me.Child5.Form
        .txtRDS = rs!RDS
        .txtStoreCount = rs!StoreCount
    ' etc...
    End With
    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 7
    Sinndho Good points

    I think it of it the less dots the fast it will go
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •