Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    72

    Unanswered: speed up code...

    I have this code to call a query directly on a Access mdb.
    But when the code goto: Set RST = CMD.Execute the operation to get rset is very,very slow!!!!!

    Code:
    Sub QUERY_ACCESS_1()
    
        On Error GoTo errore
    
        Dim TEST As Long, var_SPORT As String, VAR_COD As String
        Dim varDataRows As Variant, I As Long, TOTALE_CODICI As Long
        Dim CNSQL As ADODB.Connection
        Dim RST As ADODB.Recordset
    
        Set RST = New ADODB.Recordset
        Set CNSQL = New ADODB.Connection
        Set CMD = New ADODB.Command
    
        If Not CNSQL.State = adStateClosed Then
            CNSQL.Close
        End If
    
        CNSQL.CursorLocation = adUseServer
        CNSQL.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=\\xxx.xxxx.xxxx.xxxxx\yyyy\PUBBLICA\VARIE\L0928.mdb;"
    
        CMD.ActiveConnection = CNSQL
        CMD.CommandText = "L0928_Query"
        CMD.CommandType = adCmdStoredProc
    
        'NOTA: IL NOME DELLA QUERY DEVE ESSERE DIFFERENTE A NOME TABELLA E ALTRI CAMPI NEL DB
        Set RST = CMD.Execute
    
        varDataRows = RST.GetRows
        TEST = UBound(varDataRows, 2) + 1
    
        RST.Close
        Set RST = Nothing
        CNSQL.Close
        Set CNSQL = Nothing
    
        For I = 0 To TEST - 1
            var_SPORT = varDataRows(0, I)
            VAR_COD = varDataRows(1, I)
            TOTALE_CODICI = varDataRows(2, I)
        Next I
    
        Exit Sub
    
    errore:
        MsgBox "Errore Numero: " & CStr(Err.Number) & vbCrLf & _
               "Descrizione: " & Err.Description & vbCrLf & _
               "Sorgente dell'Errore: " & Err.Source
    
        Err.Clear
        
    End Sub
    
    this is the query stored in access mdb:
    
    SELECT DISTINCT L0928.PROVA03, L0928.PROVA05, Count(L0928.PROVA05) AS SOMMA5
    FROM L0928
    GROUP BY L0928.PROVA03, L0928.PROVA05;
    
    note: the  number of rs are 115.457
    Last edited by sal21; 09-02-09 at 09:07.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How long does the query take to run if you execute it directly in the L0928.mdb database? Is it really a stored procedure, not a query or a view? (these are very, very rare in Access).
    Where is the code executing this and where is the L0928.mdb database? What is the network connection between these two like?

    How many rows in table L0928? How many rows are returned? Are there any indexes on the table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by pootle flump
    How long does the query take to run if you execute it directly in the L0928.mdb database? Is it really a stored procedure, not a query or a view? (these are very, very rare in Access).
    Where is the code executing this and where is the L0928.mdb database? What is the network connection between these two like?

    How many rows in table L0928? How many rows are returned? Are there any indexes on the table?
    - is a simle query in access mdb... i dont know the stored procedure in ado
    - the rst are 115.457
    - no fileds indexed
    - excution is arround one minute, i think...
    Attached Thumbnails Attached Thumbnails Immagine.jpg  

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    thanks
    Quote Originally Posted by sal21
    - the rst are 115.457
    You mean the recordset contains 115 thousand rows?
    Do you know how many rows in the table (this query will probably return less rows than there are in the table)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can change the SQL of that query by removing the DISTINCT keyword - it is not required. I don't know if JET is smart enough to realise this, so it might speed thing up.

    Try adding the following INDEX to the table:
    (PROVA03, PROVA05, PROVA05)
    PROVA05 must the be last column in that index. Note that this will slow down inserts into this table, and possibly updates too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Apart from indexing, which is obvious, if the query returns a major part of the row in the underlying table(s) it would probably be quicker to create a link to the table(s) in the external database and perform the query locally. This is because of the way Access loads rows from an "attached" table. It's usually more efficient than opening an ADO recordset with the same amount of data.

    Have a nice day!

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Sinndho
    It's usually more efficient than opening an ADO recordset with the same amount of data.!
    Really? Do you have a source?

    I was going to suggest that using what is effectively a client side cursor might be slowing stuff down too.

    Another thing - isn't the code looping through the every row in the recrodset only to pick up three values?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    @pootle flump:

    The advice I gave was coming from some intensive tests I made a few months ago. As I wanted to be sure, I re-wrote a test procedure (see attachment) and, though the difference is not as significant as what I found formerly, there is still a little advantage in favour of DAO, at least when dealing with an Access database (.mdb file). When you work with a SQL server it's another story.

    I performed the test on a PC with an Intel Core 2 Quad processor 3 GHz, 4 GB RAM (motherboard Intel DP35DP) running Windows XP Pro SP3 with MDAC 2000.085.1132.00 (xpsp.080413-0852).

    The database consists in two tables: CF_Data is an Orders table that contains +- 12000 rows and LCF_Data which is an OrderLines table that contains +- 22000 rows. There is a One to Many relationship between the tables (One: CF_Data to Many: LCF_Data). Both tables have primary keys and indexes.

    The tests were performed on a database installed on a MS SQL Server running on the same machine: MS SQL Server 2005 SP3 (2005.090.4035.00) and on an Access database (.mdb file) in Access 2003 format. Both the data files of the SQL server and the .mdb file reside on the same disk.

    The tests where run in a module in Access 2003 SP3 (11.8166.8221).

    For the tests, I opened a recordset using ADO and DAO, both on the database on the SQL server and on the .mdb file. When using ADO the recordsets where open using three different cursor types (Static, KeySet and Dynamic) all on the server side. When using DAO the recordsets were open in Dynaset and Snapshot modes. In all cases the MoveLast method was used to ensure that all rows where reached (in ADO, adOpenForwardOnly was not used because it does not allow the Movelast method).

    Every test was performed 100 times, the total duration (in milliseconds) was obtained using the GetTickCount API call before entering the loop and after exiting it.

    The tests were performed using a SELECT * WHERE ... query, the first time with an INNER JOIN between the tables and a second time on the single LCF_Data table. The WHERE clause was the same in both cases. With this WHERE clause +- 2/3 of the rows where returned.

    In the results table, JETADO and JETDAO means that the Jet.OLEDB driver was used while SQLADO and SQLDAO means that the SQL Server driver was used.

    Here are the results, as expected ADO with SQL Server is the winner but when dealing with an Access database DAO has the advantage:

    Code:
    ADO With JOIN
    ---
                  adOpenStatic
                                JETADO:        45797 
                                SQLADO:        33703 
                  adOpenKeyset
                                JETADO:        6500 
                                SQLADO:        8219 
                  adOpenDynamic
                                JETADO:        46171 
                                SQLADO:        2016 
    DAO With JOIN
    ---
                  dbOpenSnapshot
                                JETDAO:        45469 
                                SQLDAO:        45812 
                  dbOpenDynaset
                                JETDAO:        6250 
                                SQLDAO:        6516 
    ADO TABLE
    ---
                  adOpenStatic
                                JETADO:        36797 
                                SQLADO:        16047 
                  adOpenKeyset
                                JETADO:        3281 
                                SQLADO:        10078 
                  adOpenDynamic
                                JETADO:        37219 
                                SQLADO:        812 
    DAO TABLE
    ---
                  dbOpenSnapshot
                                JETDAO:        34875 
                                SQLDAO:        35625 
                  dbOpenDynaset
                                JETDAO:        2719 
                                SQLDAO:        3125
    Attached Files Attached Files
    Have a nice day!

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Good info Sinndo! Thanks for the upload! I personally converted to ADO years ago and almost always use SQL Server as the backend. When I was coding for 5+ million records, I used unbound forms (and sometimes stored procedures.) Man that application ran faster than a speeding bullet (even with slow external connections)! Now (where I work), I deal with recordsizes in the thousands, don't design unbound forms (since it's more time-consuming), and the only problems I have with speed is due to the citrix/network connection (I'll sometimes change my queries to a snapshot recordset type to give them a little boost where needed.)

    Just to add a note, Form design is also crucial. A form with lots of subforms upon subforms, listboxes, comboboxes, and dlookups can really take a toll. Especially if you're loading ALL the records when the form opens (with subforms). A slow or problematic network (ie. using daisy chain hubs) is a real killer for performance. If I suspect this, I'll test it on my local drive and compare it with the networked app. (Note: I'm also now using VPN with the frontend on the user's desktop and a direct connection to SQL Server - bypassing any slow network connections.) Again, an application which really flys!

    Using SQL Server, here's some examples of recordset coding I use in ADO:

    (for opening non-updating recordsets...)
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly <- note this also allows me to get the recordcount. I use it mostly to retrieve values.
    (for opening recordsets to update...)
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic <- note this does not allow me to get the recordcount but allows me to udpate.

    Sometimes I'll use the adopenstatic or adopensnapshot but I found the above to be sufficient for most speed problems (I did have occasional problems with adopendynaset though).

    I'm a firm believer if data is important, it should reside on SQL Server. I like ADO because it's easy to troubleshoot and write code. But there are a lot of DAO coders who simply don't like ADO (don't know why.) I just know that ADO works well, works fast, and has never caused me problems.
    Last edited by pkstormy; 09-05-09 at 02:08.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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