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

    Unanswered: SQL in VB runs Slow


    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:

    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
    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$] & ""
      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
    Exit Sub
    End Sub

  2. #2
    Join Date
    Mar 2009
    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:
    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
        strSQL = "SELECT tblPrequalified.Database, ...
        Set db = CurrentDb
        Set qdf = db.QueryDefs("Name of the Persisted Query")
        qdf.SQL = strSQL
        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:
    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:
    you can use:
    And if you repeatedly make reference to an object, you can also use:
    With Me.Child5.Form
        .txtRDS = rs!RDS
        .txtStoreCount = rs!StoreCount
    ' etc...
    End With
    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 10
    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

Posting Permissions

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