dcsimg
Results 1 to 16 of 16

Thread: PERFORMNCE Old DAO project

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    1,763

    PERFORMNCE Old DAO project

    I need simply to load and loop approx 180.xxx records with DAO:

    Code:
    my con:
    Public Sub OPEN_CONN()
    
        ANNO = Year(Date)
        strDBPath = "C:\DATABASE\\OPERAZIONI_" & ANNO & ".MDB"
        Set WRKSPC = DBEngine(0)
        Set DBS = WRKSPC.OpenDatabase(strDBPath, False, False)
    
    End Sub
    
    i need to use:
    Set RSDAO = DBS.OpenRecordset(SQL, dbOpenForwardOnly)
    
    or 
    
    Set RSDAO = DBS.OpenRecordset(SQL, dbOpenDynaset)
    ????
    tks

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,486

    Re: PERFORMNCE Old DAO project

    If all you are doing is reading first to last then forward only is the way to go.

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    1,763

    Re: PERFORMNCE Old DAO project

    Quote Originally Posted by DataMiser View Post
    If all you are doing is reading first to last then forward only is the way to go.
    But a good connection string Can do a best 7performance or only a good open recordset?
    Tks in oggetto case for sugestion.

  4. #4
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,486

    Re: PERFORMNCE Old DAO project

    It may help to know more detail about what you are doing with the recordset. Is it really required that you loop through that many records?

  5. #5
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,067

    Re: PERFORMNCE Old DAO project

    Set DBS = WRKSPC.OpenDatabase(strDBPath, False, False)
    you are realizing you are opening the .mdb in full shared read-write mode ?
    do not put off till tomorrow what you can put off forever

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    1,763

    Re: PERFORMNCE Old DAO project

    Quote Originally Posted by IkkeEnGij View Post
    you are realizing you are opening the .mdb in full shared read-write mode ?
    Hummmmm.
    First string connection found in Google,:-)
    Have you other idea?
    Remember i need only loop recordset.

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,486

    Re: PERFORMNCE Old DAO project

    Why do you need to loop the recordset? Hard to give good advice when you don't give any detail.

  8. #8

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    1,763

    Re: PERFORMNCE Old DAO project

    Quote Originally Posted by DataMiser View Post
    Why do you need to loop the recordset? Hard to give good advice when you don't give any detail.
    ...to fiill a listview.

  9. #9
    PowerPoster
    Join Date
    Oct 2013
    Posts
    3,186

    Re: PERFORMNCE Old DAO project

    You fill a ListView with 180.000 records??!?

    Then you better check what is really causing the slowdown.

  10. #10

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    1,763

    Re: PERFORMNCE Old DAO project

    Quote Originally Posted by Arnoutdv View Post
    You fill a ListView with 180.000 records??!?

    Then you better check what is really causing the slowdown.
    OPS....SORRY

    Not all in one shot, but with a where clausole from 180.xxxx recordset...
    With a where clausole i retrive apporx 15.xxx/20.xxx records.

  11. #11

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    1,763

    Re: PERFORMNCE Old DAO project

    Quote Originally Posted by Arnoutdv View Post
    You fill a ListView with 180.000 records??!?

    Then you better check what is really causing the slowdown.
    OPS....SORRY

    Not all in one shot, but with a where clausole from 180.xxxx recordset...
    With a where clausole i retrive apporx 15.xxx/20.xxx records.

  12. #12
    PowerPoster
    Join Date
    Feb 2006
    Posts
    19,158

    Re: PERFORMNCE Old DAO project

    180K+ rows in a ListView seems ridiculous. Why do this? It won't be all that usable. If you are trying to use a ListView as a data structure there are far better ways to go.

    If you really do want to use a ListView to permit browsing vast numbers of rows then using a ListView in virtual mode makes sense. I can't think of any reason why you can't do that with DAO. The CodeBank has several examples.

    Since the entire reason for virtual mode is to avoid loading all of the data into RAM you'd probably want to use a server-side Keyset or Dynamic cursor, probably read-only but of course that depends on whether or not you plan to do updates through this Recordset.

    If the application only needs to support a single running instance and the database isn't shared you might improve performance a bit more by opening for exclusive access.


    Since all of these suggestions are so obvious I must be missing something. What haven't you told us?

  13. #13
    PowerPoster
    Join Date
    Oct 2013
    Posts
    3,186

    Re: PERFORMNCE Old DAO project

    Quote Originally Posted by luca90 View Post
    OPS....SORRY

    Not all in one shot, but with a where clausole from 180.xxxx recordset...
    With a where clausole i retrive apporx 15.xxx/20.xxx records.
    That's also a ridiculous amount of data, if you have a full size form with a ListView it can show about 60-70 lines (rows)
    So for 15.000 lines you would have a total of 250 pages.
    What do users do with this amount of data?

  14. #14
    Frenzied Member
    Join Date
    Dec 2008
    Posts
    1,134

    Re: PERFORMNCE Old DAO project

    I don't know if there are performance (and other) benefits by using ADO instead of DAO
    But I believe most of us believe it is better to switch to ADO.
    I had a very large project written entirely with DAO
    When I decided to switch to ADO, i did it gradually.
    New Forms used ADO.
    Gradually over a period of months 'rewrote' the db accessing in the existing Forms.
    The project quite happily allowed me to have a mixture of both (DAO and ADO Forms) over those many months.
    Mind you I do not have the nasty bad habit of maintaining connections to DBs.

  15. #15
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,617

    Re: PERFORMNCE Old DAO project

    I have a mixture of the DAO and ADO in my primary project, and I don't really see any performance difference between the DAO and ADO. The primary thing you lose with the DAO is the ability to open new MS Access database and/or the ability to connect with any kind of SQL server. If you're content with your MDB databases, the DAO is absolutely fine.

    Also, Luca90, if your ListView is just a window to your recordset, why would you need to read all 180,000 records at once. Just open your recordset as a dynaset and read the records as you need them. Also, sorting has been mentioned. An index will vastly help you with your sorting. In fact, if you're showing the entire table, set the correct index and open your recordset as a table. I do all of these things with the DAO (even using the ListView as a window to a recordset), and I don't have any problems nor speed issues at all.

    Once I get a recordset open, for a case like this, I just use .Index, .BOF, .EOF, .MoveFirst, .MoveLast, .MovePrevious, & .MoveNext. No biggie.

    Maybe That'll Help,
    Elroy
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  16. #16

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    1,763

    Re: PERFORMNCE Old DAO project

    Quote Originally Posted by Elroy View Post
    I have a mixture of the DAO and ADO in my primary project, and I don't really see any performance difference between the DAO and ADO. The primary thing you lose with the DAO is the ability to open new MS Access database and/or the ability to connect with any kind of SQL server. If you're content with your MDB databases, the DAO is absolutely fine.

    Also, Luca90, if your ListView is just a window to your recordset, why would you need to read all 180,000 records at once. Just open your recordset as a dynaset and read the records as you need them. Also, sorting has been mentioned. An index will vastly help you with your sorting. In fact, if you're showing the entire table, set the correct index and open your recordset as a table. I do all of these things with the DAO (even using the ListView as a window to a recordset), and I don't have any problems nor speed issues at all.

    Once I get a recordset open, for a case like this, I just use .Index, .BOF, .EOF, .MoveFirst, .MoveLast, .MovePrevious, & .MoveNext. No biggie.

    Maybe That'll Help,
    Elroy
    Nice!
    Tks for suggestion.
    My idea is to trap the record in a getrow and loop
    Via unbound...
    In the case i think the filling of listview is very fast or not?
    I use also the Api to lock and unlok the listview. During the loop....
    Wath you thing about?

    And Curious why you suggest dinaset ineast forward....
    Peraphs for problem of direction of recordset?

    Note:
    Happy to see you in One of mine post ��
    Last edited by luca90; Oct 12th, 2018 at 12:02 PM.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width