Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked ODBC tables versus ADO (A97/SR2)

    Has anyone any thoughts / comments to enlighten me on the speed differences between ODBC linked tables?
    - currently I have databases with a couple of Oracle tables linked in via ODBC, and a function which takes a couple of parameters, builds some SQL which is a join query on the two tables to get a single value, e.g. codelist BCSTAT, value REC and it returns the text "Application Received"

    I've created a stored procedure in Oracle that does the same, as I've read how they're much better, and get the value using ADO
    - create the ADO Command and Connection objects, put the two parameters into the Parameters collection, Execute, and the return text is in the third Parameter
    - but this is really really slow <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    Improved it by putting the instantiation of Command and Connection in the Report Open event, and then for each records just calling a basic function which takes the two values, puts them into the Parameters, Executes and returns the text
    - better, but the old method is still faster

    Ok, I can stick with the old method, but I'd like to know why and how the ODBC linked tables perform so much better than the ADO + stored procedure <img src=/S/question.gif border=0 alt=question width=15 height=15>
    - I thought the ODBC would be dragging all that data over the network, doing the join and search in Access, whereas the ADO would send two small bits of text up to Oracle, and get back a single small bit of text, so what's going on <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked ODBC tables versus ADO (A97/SR2)

    What version of ADO are you using and where is your cursor located, client or server side? If it's client side, that may be the reason for the poor performance. I'm not sure I understand what you're doing, though. Are you running a query over and over for each record in a report?
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked ODBC tables versus ADO (A97/SR2)

    My MDAC is 2.7 RTM, so I presume ADO 2.7 <img src=/S/question.gif border=0 alt=question width=15 height=15>, and the cursor is client side

    Yeap I'm basically running a query to lookup a text description for a value(s) in the main report.

    The original approach had the decode tables linked in, but I thought it would be better to have the decode in the stored procedure and use it to return the single text description for the passed parameters [again a legacy from previous developer, I know I shouldn't be constrained, but ...]
    I suppose the ultimate thing would be to put the entire set of queries into an Oracle stored procedure, and then call it with the user parameters, and use the resulting recordset as the recordsource for the report
    - I've not tried creating PL/SQL tables yet, so not sure actually how to <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)

    Things are working, but I'm interested in the why the ODBC approach is so much better, is there some sort of cacheing / optimisation that occurs between Access and Oracle <img src=/S/question.gif border=0 alt=question width=15 height=15>
    It's also got me a bit worried, is this the sort of performance I'd get if I was using VB + a reporting control like Crystal or ComponentOne + ADO <img src=/S/question.gif border=0 alt=question width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked ODBC tables versus ADO (A97/SR2)

    I think your original approach made more sense when using linked tables. Then you don't need all that calculation do you?

    I don't have any experience with ADO 2.7 because I was made cautious by some of the warnings I saw posted. Try a server side cursor if you're going to keep doing it this way, and see if it makes a difference.
    Charlotte

Posting Permissions

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