Results 1 to 15 of 15
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: NEED EXPERT ADVISE. VB6 RDO vs ADO

    Hi guys,
    I am faced with a challenge right now in regards to an application we have.
    In the passed 4 months the application has suffered a huge proformance loss.
    I have pinned it down to Database calls.

    This VB6 app is using the old RDO. I have noticed that when I am making a stored procedure call to our SQL Server 2000, the query is done on SQL side, but it could take 6 minutes before it is displayed on the VB screen.

    My question is, if I upgrade the app to use ADO OLEDB vs RDO ODBC, will I see any proformance increase, or would it not even matter?

    Any other suggestions to increase proformance in VB when making DB calls?

    Please help before I go and spend 2 weeks converting this thing to ADO.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Has the performance drop soincided with an increase in database size?

    Are the tables adequately indexed on the server?

    With your rdo recordsets - where is the cursor being processed? At the client, or at the server?
    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


  3. #3
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    The data grows everyday, but nothing unusual. The indexes work great as well.
    Like I said earlier, the database is finished and done could be 6 minutes prior to vb actually displaying data.

    The cursor library is rdUseOdbc.

    I hope this makes sense.

    I wrote a little app to do same queries using RDO vs ADO and I really didn't see any improvement.

    Any other suggestions on VB proformance increase?

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    The question is: where is the cursor processing taking place?

    In ado, you can specify rs.Cursorlocaztion = adUseClient (or adUseServer)

    If you specify client side processing, the entire contents of the table or tables in question are copied from the server to the client before any joins, filtering, or sorting take place. Obviously, in the case of large tables, this can lead to excessive transport delays between the server and the client.

    In the case of SERVER side processing, the joins, filtering, and sorting take place at the server, and ONLY the records which match the query definition are passed from the server to the client. This of course, will minimize the transport delay, as only the exact data required by the query will be transferred over the network. And, if you allow the server to do much of the work, its processing is often more efficient than the more general-purpose processing that would run in ADO or RDO. However, the processing time for a server-side recordset also depends on the current server workload.
    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


  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    It was in 1998 that I last did ANY work with RDO.

    rdUseOdbc is the RDO equivalent of ADO's client side cursors.

    If the number of records exceed available memory, the recordset is buffered into disk memory.

    How many records does your query return?
    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


  6. #6
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Thanks for your reply.
    This one query that I am testing with returns only one record. It is actually calling a stored proc on SQL Server. But some stored procs return thousands of rows.

    The reason I started testing with this one proc that returns only one record is because the perticular screen that runs this and then populates the text boxes and labels sometimes takes 5-6 minutes to refresh with the data.

    So, what you are saying is, if I change my cursor location in RDO, I should see proformance increase? Should I not even bother going to ADO?

    Also, I am testing calling Crystal Report passing into the connection string the ODBC DSN connection and also trying with OLEDB string. Again, not noticing any improvement in proformance OLEDB vs ODBC. This report returns 150,000 rows.

    The reason I hate going to ADO is we will be rewriting this app in another language and hate doing all this work for nothing.

    But if the proformance will increase drastically going to ADO, I will do the work.

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I think you should try using a server-side cursor. (rdUseServer)
    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
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    I guess my question is, does it really matter if it's server side or client side if I am calling stored proc's? I understand, if I am doing an adhoc query, then it will put the table structure and all records and then do all the work on the client side, but with stored proc, I would only get the records I am asking for.
    I guess I am just trying to understand it.

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    If you're calling a stored procedure to populate your recordset, there shouldn't be much difference between client-side cursor and server-side cursor performance.
    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


  10. #10
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Also, what cursor type should I use for better proformance?

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I would like to clarify.

    If the cursor is at the server, only the data you need at the moment is copied from the server to the client, even though the data is collected by a stored procedure.

    So, if you have a thousand records, and you're displaying 20 on a grid, only the 20 that you're displaying would be sent over the network. When you scroll down one record, another single record is sent over the network. This can make the app be much more responsive to the end user.

    Now, there is a bit more overhead with each record sent individually, so if your goal is to copy over a hundred thousand records & iterate through them at the client, the overall faster approach for that specific instance would be to use a client-side cursor location(assuming that sufficient memory exists on the client to avoid virtual memory switching to the client hard drive) with a forward-only cursor.

    If you are displaying a few tens of records, it may be faster to simply copy the entire recordset with a client side cursor also.

    I have to say, though, if you have a recordset with only a few records that is taking a long time to populate, I think you probably need to look at the server, and not the client.
    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
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    OK, thanks.
    Another question is, does Keyset vs Dynamic have any impect on proformance?

  13. #13
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Another problem.
    I switched my app to use Server Side cursor. Now, I assume Server Side curser pull back records as Forward Only Cursor type because when I run through the recordset to populate the list box after that I do AbsolutePosition = 1 and I get an error telling me I can't do that with Forward Only Cursor.
    What I don't understand is I set my cursor type to be Dynamic with the following.
    Set qrySearch = g.SQLServerDB.CreateQuery("", m.sStoredProcedure)
    qrySearch.CursorType = rdOpenDynamic
    Set m.rsData = qrySearch.OpenResultset(2)

  14. #14
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Use a static cursor type.

    dynamic cursor have the greatest overhead of all cursors.
    Last edited by loquin; 10-06-06 at 16:24.
    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


  15. #15
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Another question.
    I have proposed for us to switched our App to use ADO. My boss has asked me to put together a document showing the differences in RDO vs. ADO and how will it help proformance.
    Can someone point me to a good article or reference somewhere, where they talk about RDO vs. ADO differences.
    Thanks.

Posting Permissions

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