Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41

    Unanswered: Array or Recordset?

    Ok, I know how to do both, but I am trying to decide which is better, both on resources and speed. Basically I am having to get data from a database very frequently for my program. Now originally I had this setup to connect to ADODB, then assign a recordset each time I needed data from the database, but after thinking more about it I thought that maybe it would be better if I got all the data from the database at the beginning of my program, put it all into an array of my own user type and then just got it from the array each time I needed it. Now though after thinking about it again I am wondering if this is perhaps not the best option as I am led to believe that variables drain resources fast, especially when they are public as these ones are.

    So basically what I want to know is which is better if there is a definitive answer, and if there is no definitive answer what are peoples thoughts on this?

  2. #2
    Join Date
    Nov 2004
    Posts
    108

    Exclamation

    I got all the data from the database at the beginning of my program
    Not a good idea (in my opinion) unless there is about 80% probablility that user will need all the data

    put it all into an array of my own user type
    Not a good idea and not necesary you can use disconnected recordset instead

    Every time you send an request to the DB (at least in SQL Server) the server optimizes your request and then compiles it and then run the query and gives you back the data. If you use SP the first two steps are run only the first time the SP is run so it is faster to call a SP than creating querys from your application that have to be optimized and then compiled before they can be run. Also avoid using Select * from yourTable when possible the smallest your data packet is the shortest reponse time will be.
    to err is human ; to really mess things up requires a computer

  3. #3
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    When I said all the data, I meant all the data that I did need. So for example I did not grab the ID field, though of course it is referenced in the SQL SELECT statement.

  4. #4
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Also, what do you mean about using a disconnected recordset?

    I basically am just looking for the most efficient way to do this and with several forms within my project all having to continually get data I thought it might be easier to just have it all within an array then I can just call the specific record I want from the array rather than having to either do a new Set recordset or make a Do Until loop to select the record within an already existing recordset. I am sure this would probably be a lot easier to explain if I could show all my code, but the program is pretty large already and the database has 31 tables already, almost all of them linked

  5. #5
    Join Date
    Nov 2004
    Posts
    108
    In order for you to receive the initial data you need at least a recordset. Since disconnected recordsets (recordset where connection is Set to Nothing) have Client Cursor which run in the local memory they are more efficient. So after you receive the data you can set the connection to Nothing to release the connection to the connection pool.

    You can jump to any of the record with the Find function and you can access the fields as arrays also with absolutePosition, move and fields(index) so the creation of an array structure to manage the data is not really necesary. You can also have many results on one single recordset and navigate through them with NextRecordset

    but as I said before it is just my opinion
    to err is human ; to really mess things up requires a computer

  6. #6
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Its funny, as I am still new to VB I am constantly learning more, both by doing and by reading forums, but also recently I have bought a book for even more help and just tonight I came across the subject of recordsets and in particular the find method and after I finished reading I came on to check if I had any new replies and you had mentioned the find method. Also, the disconnected recordset concept is completely new to me so many thanks for explaining that to me. I dont think efficiency is too big of a worry right now, but I am forever looking at the bigger picture and I am pretty sure that using disconnected recordsets will be a lot more efficient when my program continues to grow.

  7. #7
    Join Date
    Nov 2004
    Posts
    108

    I candle looses nothing by lightning another candle

    I've been working with VB6 for a bit more than 5 yrs and still I'm learning and I know there still lot more to learn. My favorite book of VB is Effective Visual Basic How to Improve Your VB/COM+ Applications. It is not a step by step book rather is a bla bla bla book and I hate reading but I love this book it explains the pros and cons of developing in certain ways. While most books tell you how to code this book explain the why's. If you care about efficiency this is the book you want to read.
    to err is human ; to really mess things up requires a computer

  8. #8
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Many thanks, I think that will be one of the next books I will have to pick up

Posting Permissions

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