Results 1 to 13 of 13
  1. #1
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Backend DB (97)

    Hi,

    I've used an Access 97 database as a backend to an intraweb, accessed via an ASP page using ADO.

    It hasn't gone live yet, but I'm concerned about the performance implications.

    I'm not using any of the Access front-end features.

    Some of the queries will be hitting the DB fairly hard.

    I've noticed quite a lag when I first access the DB, but in subsequent accesses, it performs OK. I don't know if this is an Access thing, a ADO thing, or something else.

    I would guess I have the option of going mySQL; we have an Oracle site licence as well, but I don't know if we can go that path in the short term.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

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

    Re: Access Backend DB (97)

    It may be an optimization issue. Are the queries saved queries or SQL?
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Backend DB (97)

    They are SQL.

    Would they be better as saved queries? Or is there a time lag inherent with loading Access (or any DB) first time around?

    There might be some ASP issues as well- I may have to see if I can connect once, and leave the connection open for the session.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

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

    Re: Access Backend DB (97)

    Well, Access (and ADO from what I've seen) prefers to handle optimized queries, which mean saved queries will behave better than SQL. In Access 2000, there's a major and visible difference in performance between saved queries and SQL due to advance optimization, but I don't know how much of that is due to ADO and how much to the query engine itself.

    The first time you run a SQL query, Jet optimizes it, which can take some time, especially if you have a website involved I imagine. After that, it caches the query so it's faster next time. Of course, if you close the database or, in this case, approach it from a different machine, I imagine that it will have to optimze the SQL all over again. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Backend DB (97)

    Thanks Charlotte. I'm checking out the stored queries. I've used them in Oracle, but this is my first venture in Access.

    Thanks for your help.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Backend DB (97)

    Sure. I'll drop <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> on this one...

    .mdb does not scale. Is that safe to say? Which SQL product really does not matter these days, is suppose, as long as it's DB2, SQL Server or Oracle. Sybase has (what's it called?).

    Write stored procedures on the SQL server for the most optimized retrievals.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: Access Backend DB (97)

    <hr>Sybase has (what's it called?).<hr>
    It's called Sybase SQL Server. They licensed the original product to Microsoft.
    Charlotte

  8. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Backend DB (97)

    I remember that connection now. I believe Ashton-Tate (makers of the famed dBase) was also in on the original release of SQL Server?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: Access Backend DB (97)

    I don't recall that connection, but then I've only worked with SQL Server since 6.5. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  10. #10
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Access Backend DB (97)

    > Would they be better as saved queries?
    >Or is there a time lag inherent with loading Access (or any DB) first time around?

    Probably and yes. If there are no issues in saving the queries then do so. If the query is built up at run-time because its functionality depends on various other factors that can only be determined at run-time then it becomes a balancing act. Do you complicate the query so it can cater for the various situations; do you create multiple queries; or is it easier to leave things as they are?

    There is definitely an extra time lag before the first access to an Access DB through ADO which has nothing to do with compiled or uncompiled queries - it happens even if the Access DB contains nothing more than tables which you attempt to access, as tables, through ADO. I don't imagine there's anything that can be done about it, but someone may know of a way of minimising it.

    Hope this helps,

    Simon

  11. #11
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Backend DB (97)

    Simon,

    The queries are easy to store, with a simple parameter. So it's quite easy to make stored queries. I just didn't realise that the quesries i've previously stored for adhoc enquiries could be stored and parameterised. I've had little contact with Access- but I know enough about databases for using it to be quite intuitive and easy.

    Would that time lag through ADO be as pronounced with another DBMS? (Or am I asking the wrong forum?) Is there much difference between DAO and ADO?

    Thanks
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  12. #12
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Access Backend DB (97)

    <hr>I've noticed quite a lag when I first access the DB, but in subsequent accesses, it performs OK. I don't know if this is an Access thing, a ADO thing, or something else.<hr>
    I am reading a fascinating book called ADO 2.6 Programmer's Reference, from Wrox. Says that by default ADO uses a technique called "connection pooling," whereby closing a connection and then opening a new connection within a certain period of time using the identical connection string will re-use the previous connection (although it is a new connection as far as your code is concerned). This would explain why subsequent connections are much faster

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

    Re: Access Backend DB (97)

    I think the time lag is going to depend not so much on the DBMS (assuming it isn't a database server) as on the kind of connection you're using. If you use a database server, then you should be able to use server-side cursors and connection pooling with ADO, which would speed it up. Of course, you can also create a connection as a global object and then keep reusing that connection. It isn't quite pooling, but it does give you a slight performance improvement. If you use ODBC drivers and DAO, then you can't do much to speed the connection up, but a database server will still return records faster than Access.

    If Jet is in the picture for the queries, then you can't expect much speed improvement in either case, because the Jet query engine is comparatively slow and clunky.
    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
  •