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

    Unanswered: problem with speed to open recordset

    i use vb 6 classic for my project.

    my conn:
    Code:
    Public Sub APRI_CONNESSIONE()
    
        On Error GoTo Err_SomeName
    
        PC_OPERANTE = Environ$("COMPUTERNAME")
    
        Set CONN = New ADODB.Connection
        With CONN
            .CommandTimeout = 1000
            .ConnectionTimeout = 1000
            .CursorLocation = adUseServer
            .Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "Data Source=E:\REPORT_L0\DATABASE\" & MIO_DB & "-L0928_TEST.mdb;Persist Security Info=False"
            .Properties("Jet OLEDB:Max Locks Per File") = 1500000
        End With
    
    Exit_SomeName:
        Exit Sub
    
    Err_SomeName:
        MsgBox Err.Number & Err.Description
        Resume Exit_SomeName
    my sql open:

    Code:
     Set RST0 = New ADODB.Recordset
            RST0.CursorLocation = adUseClient
            SQL = "SELECT DT FROM L0 WHERE CONTAB='" & TEST_CONTAB & "' GROUP BY DT "
            RST0.Open SQL, CONN, adOpenKeyset, adLockReadOnly
    this operation i very very slow!!!!

    note:
    CONTAB field is indexed
    TEST_CONTAB is dimensioned as string

    is correct to set the connection cursor to AdUserserver????
    Last edited by sal21; 12-09-11 at 06:30.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This:
    Code:
     .CursorLocation = adUseServer
    is useless when connecting to an Acces (.mdb) database. adUseServer only works with some (i.e. not all) Database Servers. When working with an Access database and Jet, everything happens on the client side. See: adUseServer versus adUseClient - Microsoft Access / VBA

    I you're looking for performances, and as far as an Access database is concerned and that your application would allow it (i.e. you do not need ADO specific features that have no equivalent in DAO), I would use the DAO library instead of the ADODB library.

    For performance topics in general, see also:Improving MDAC Application Performance
    Have a nice day!

  3. #3
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by Sinndho View Post
    This:
    Code:
     .CursorLocation = adUseServer
    is useless when connecting to an Acces (.mdb) database. adUseServer only works with some (i.e. not all) Database Servers. When working with an Access database and Jet, everything happens on the client side. See: adUseServer versus adUseClient - Microsoft Access / VBA

    I you're looking for performances, and as far as an Access database is concerned and that your application would allow it (i.e. you do not need ADO specific features that have no equivalent in DAO), I would use the DAO library instead of the ADODB library.

    For performance topics in general, see also:Improving MDAC Application Performance
    TKS for reply .... but u suggest me tu use DAO, in this case, or not?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    probably best to ask this in the VB forum, its not really an Access question
    FWIW
    I would experiment with the maxlocks perfile 1.5million seems a heck of a lot and I suspect each lock may consume soem resources

    I doubt changing the cursor location to server will make any difference on a JET database
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by sal21 View Post
    TKS for reply .... but u suggest me tu use DAO, in this case, or not?
    Yes I do. I know from experience that using the DAO library is usually (not always) faster than using ADODB on the same Access database. Things would be different if the database would reside on a SQL Server and you could use a "true" Server-Side cursor.
    Have a nice day!

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    DAO is optimized for access. That being said, ADO isn't THAT much slower than DAO, and shouldn't be the cause of a very slow Select statement.

    Instead, try using use a static connection, in conjunction with your read-only lock. (and, if you are going to iterate through the whole recordset once, use a forward-only recordset.

    In any event, the RST0.CursorLocation = adUseClient overrides the connection's CursorLocation, which would be used when a cursor location is not explicitly defined (or when using the command object.)

    And, as others have said, reduce the number of file locks.

    If you're going to be iterating through all the records in the recordset, be sure to use explicit instantiation (as you have done,) use the WITH/END WITH construct to reduce the number of object validations, and to use integer field numbers or names (rs.Field(0).Value or rs.Fields"FieldName").Value,) as a field reference, instead of the rs.Fields!FieldName convention.

    You can also add indexes on the fly, and use the recordset.filter property to pare down your data to a more manageable set. This approach is HUGELY faster than iterating through the entire recordset, searching for a value.

    Finally, IF you can open the data file exclusively, you may have better response time. Before opening the connection, set it's mode to exclusive access, as follows:
    Code:
    Conn.Mode = adModeShareExclusive
    Last edited by loquin; 02-28-12 at 13:43. Reason: clarification
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    One other point: IF the database resides on a different machine (you are accessing it over the network) database access is much slower than if the file is located on a local drive. Inserts are particularly slow: in one case, I had several hundred thousand inserts that needed to be made: over the network, the inserts were taking place at about 3 per second; When I temporarily moved the file to my computer, the insert rate was approximately 350 per second... As I understand it, one of the major bottlenecks is the poor response time associated with setting file locks on a mapped, network drive. Another is that, since the Access connection and recordset always reside on the client computer, a large portion (if not all) of the entire database file must be copied from the server location to the client computer for any operation.

    An alternative approach here might be to use an asynchronous database connection/recordset. Declare the database objects With Events. This provides you with much finer-grain monitoring and control of ADO. Although your code will necessarily be more complex, it will allow you to begin data operations when only a portion of the data has actually arrived at the recordset. (you just need to make sure, if you haven't received the .FetchComplete event, that you aren't trying to operate on records that you haven't yet received in the recordset.) And, you can monitor/display the transferred record count as the recordset is populated. (This can eliminate the appearance (to the user) of a locked program.)

    Ref http://www.xtremevbtalk.com/showthread.php?t=266305
    Last edited by loquin; 02-28-12 at 14:15.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by loquin View Post
    DAO is optimized for access. That being said, ADO isn't THAT much slower than DAO, and shouldn't be the cause of a very slow Select statement.

    Instead, try using use a static connection, in conjunction with your read-only lock. (and, if you are going to iterate through the whole recordset once, use a forward-only recordset.

    In any event, the RST0.CursorLocation = adUseClient overrides the connection's CursorLocation, which would be used when a cursor location is not explicitly defined (or when using the command object.)

    And, as others have said, reduce the number of file locks.

    If you're going to be iterating through all the records in the recordset, be sure to use explicit instantiation (as you have done,) use the WITH/END WITH construct to reduce the number of object validations, and to use integer field numbers or names (rs.Field(0).Value or rs.Fields"FieldName").Value,) as a field reference, instead of the rs.Fields!FieldName convention.

    You can also add indexes on the fly, and use the recordset.filter property to pare down your data to a more manageable set. This approach is HUGELY faster than iterating through the entire recordset, searching for a value.

    Finally, IF you can open the data file exclusively, you may have better response time. Before opening the connection, set it's mode to exclusive access, as follows:
    Code:
    Conn.Mode = adModeShareExclusive
    Tks loquin.

    All i have understand only this piece of explaination not is clear...for me naturally

    "In any event, the RST0.CursorLocation = adUseClient overrides the connection's CursorLocation, which would be used when a cursor location is not explicitly defined (or when using the command object.)"

    can you post an example to correct my code based your suggestin about cursor of RST0...

    And.. you relally suggest me to use DAO instaed ADO, or not?
    Last edited by sal21; 02-28-12 at 13:56.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    could also be an indexing (or lack of issue)

    might help if you put your query into a suitable query browser and ran explain / show queryplan to understand where the time is being consumed
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by healdem View Post
    could also be an indexing (or lack of issue)

    might help if you put your query into a suitable query browser and ran explain / show queryplan to understand where the time is being consumed
    Ok...

    note:

    1) i have indexed all relavent filed with wehre clausole
    2) changed my con with:
    Set CONN3 = New ADODB.Connection
    With CONN3
    '.CursorLocation = adUseServer
    .Mode = adModeShareExclusive
    If TEST_UTENTE = "" Then
    .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\REPORT_\DATABASE\" & MIO_DB & "-TEST.mdb;Persist Security Info=False"
    Else
    .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\9425\SAL21\REPORT_\DATABASE\" & MIO_DB & "-_TEST.mdb;Persist Security Info=False"
    End If
    End With

    3) cahnged my open method for recordset with:

    Public Function ExecuteSQL(SQL)

    If Not RST0 Is Nothing Then
    If RST0.State = 1 Then
    RST0.Close
    End If
    Set RST0 = Nothing
    End If

    Set RST0 = New ADODB.Recordset

    With RST0
    .CursorLocation = adUseClient
    '.CacheSize = 50
    'DoEvents
    'OK
    'DoEvents
    .Open SQL, CONN3, adOpenStatic, adLockReadOnly, adCmdText
    'OK
    '.Open SQL, CONN3, adOpenDynamic, adLockOptimistic, adCmdText
    '.Open SQL, CONN3, adOpenForwardOnly, adLockReadOnly, adCmdText
    '.Open SQL, CONN3, adOpen Static, adLockOptimistic, adCmdText
    '.Open SQL, CONN3, adOpenStatic, adLockBatchOptimistic, adCmdText
    '.Open SQL, CONN3, adOpenForwardOnly, adLockOptimistic, adCmdText

    End With

    End Function

    on open recodset nwo have a dubt to use:

    .Open SQL, CONN3, adOpenStatic, adLockReadOnly, adCmdText

    or

    .Open SQL, CONN3, adOpenForwardOnly, adLockReadOnly, adCmdText

    help me please
    Last edited by sal21; 02-28-12 at 16:09.

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by sal21 View Post
    ...
    "In any event, the RST0.CursorLocation = adUseClient overrides the connection's CursorLocation, which would be used when a cursor location is not explicitly defined (or when using the command object.)"
    When you create a recordset object, the recordset has has it's own properties for, among others, the cursor location. But, if you haven't explicitly set the recordset's .cursorlocation property prior to it's first use, the recordset will use the connection's .cursorlocation property by default. However, IF you explicitly set the rs.Cursorlocation property, it uses what you specify.

    So since, in your code, you explicitly state
    Code:
    RST0.CursorLocation = adUseClient
    Your recordset uses a client-side cursor, no matter WHAT you specify for the connection.

    Quote Originally Posted by sal21 View Post
    And.. you relally suggest me to use DAO instaed ADO, or not?
    No, I'm not. (I believe Sinndho was suggesting that you consider it, though.)

    IMO, since there's generally such a small improvement in performance when using DAO rather than ADO, it's more important for me to use ADO, as it allows a more common interface across the 4 databases that I work with regularly (Oracle, SQL Server, PostgreSQL, and Access.) If all you will ever work with is MS Access, then YOU should consider it. But, just the fact that ADO usage is so similar across so many database platforms, and with many non-database data sources (Excel files, text files, even websites!,) it makes more sense, IMO, to concentrate on it.

    ADO has the flexibility to be 'tuned' to fit your needs. But, in order to use that flexibility to your advantage, you need to understand
    1. what your ADO options are
    2. what the strengths/weaknesses of each option are
    3. How your data usage best fits the available options


    To learn about the first two items on this list, I would strongly suggest that you locate a copy of "Serious ADO: Universal Data Access with Visual Basic" by Rob MacDonald. It's readily available at Amazon or Half.Com. And, since it's been out of print for over 10 years, it's gotten pretty cheap (I just bought another copy for home,) but it's by far the best VB6/ADO programming book I've come across.

    You haven't said what you are doing with the recordset after you open it, nor approximately how many records are in the table in question. How you're using the data makes a difference in the approach you should take in fetching the data.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  12. #12
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by loquin View Post
    When you create a recordset object, the recordset has has it's own properties for, among others, the cursor location. But, if you haven't explicitly set the recordset's .cursorlocation property prior to it's first use, the recordset will use the connection's .cursorlocation property by default. However, IF you explicitly set the rs.Cursorlocation property, it uses what you specify.

    So since, in your code, you explicitly state
    Code:
    RST0.CursorLocation = adUseClient
    Your recordset uses a client-side cursor, no matter WHAT you specify for the connection.

    No, I'm not. (I believe Sinndho was suggesting that you consider it, though.)

    IMO, since there's generally such a small improvement in performance when using DAO rather than ADO, it's more important for me to use ADO, as it allows a more common interface across the 4 databases that I work with regularly (Oracle, SQL Server, PostgreSQL, and Access.) If all you will ever work with is MS Access, then YOU should consider it. But, just the fact that ADO usage is so similar across so many database platforms, and with many non-database data sources (Excel files, text files, even websites!,) it makes more sense, IMO, to concentrate on it.

    ADO has the flexibility to be 'tuned' to fit your needs. But, in order to use that flexibility to your advantage, you need to understand
    1. what your ADO options are
    2. what the strengths/weaknesses of each option are
    3. How your data usage best fits the available options


    To learn about the first two items on this list, I would strongly suggest that you locate a copy of "Serious ADO: Universal Data Access with Visual Basic" by Rob MacDonald. It's readily available at Amazon or Half.Com. And, since it's been out of print for over 10 years, it's gotten pretty cheap (I just bought another copy for home,) but it's by far the best VB6/ADO programming book I've come across.

    You haven't said what you are doing with the recordset after you open it, nor approximately how many records are in the table in question. How you're using the data makes a difference in the approach you should take in fetching the data.
    sorry me for:
    "You haven't said what you are doing with the recordset after you open it, nor approximately how many records are in the table in question. How you're using the data makes a difference in the approach you should take in fetching the data."

    i have approx 1.400.xxx records and 21 fileds in table in question

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    where is the time going that makes the connection "slooow"

    my immediate suspiscion is whether you have a proper indexing strategy
    what happesn if you indexc column DT
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by healdem View Post
    where is the time going that makes the connection "slooow"

    my immediate suspiscion is whether you have a proper indexing strategy
    what happesn if you indexc column DT
    DT is already indexed...

  15. #15
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    1.4 million records with 21 fields is going to take a long time to load into memory. Since you have a client-based cursor, all 1.4 million record pointers will be loaded into ADO before you can see anything. A static recordset loads the data, a keyset recordset loads record pointers, then fetches the data when the record is accessed by the client.

    If you don't need all the fields, or all the records, try to limit the data you are extracting with a WHERE clause in your SQL, and by listing the fields you need, explicitly.

    i.e.
    Code:
    Select Field1, Field2, Field3 From YourTable Where Field1>1000
    Is the .mdb file located on another computer or on a server share? If so, that will make accessing the data incredibly slow. Access inherently has performance issues when manipulating a datafile on another computer. There's lots of overhead there. Once when inserting records into a remote file, I was getting about 3 inserts per second over the network. When I copied the target file to my local PC, insertion rates jumped to over 300 per second.

    What are you actually doing with the data once it's loaded into the recordset?

    One other approach would be to declare the recordset objects WithEvents. This way, you could start working with the recordset as soon as data starts loading (essentially, a separate thread is invoked to load the data into the recordset.) This approach is also known as Asynchronous ADO. additional events are available when you declare the ADO objects WithEvents.

    There is more complexity required at the client application programming end; you should add code to indicate the data download status and percent complete, and you'll need to make sure your error handling code can handle you bumping into the recordset EOF without killing the app. When loading the data asynchronously, the .EOF is a 'moving target.'
    Last edited by loquin; 04-03-12 at 17:54.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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