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

    How inefficient is this? (FPDB-ADO) (A2000/Jet4/SQLServer2K)

    An ADO question for you gurus... I am trying to "tune" a FrontPage web that was built using FrontPage Database components. For those unfamiliar, FP generates ASP code to interact with the database using your choice of connection methods. In this case, a System DSN to a Jet (Access 2000) database was used. This intermediate database uses linked tables to tap into an inviolable back end database, and is used as a query store as well as storing some unique data. (When I say inviolable, I mean I cannot add tables to or store procedures in this database. No exceptions.) The back-end database recently changed format from MDB to SQL Server 2000.

    It seems that the way I built the FP web, in my youthful naivete, was the least efficient arrangement possible. Attached is a diagram of how the database function works for the first query that one must run. This easily could be coded to run directly against the SQL Server database (I've tested that part), but rewriting all of the code to lay out the results pages is not something I relish. So I want to know "how bad is this" in terms of stress on our server resources.

    Reading this literally, one instance of OLE DB, corresponding to the MDB, is performing a simple join on two SQL Server tables that returns two columns for every record in Table1 (about 2000 records) with a third column for a field from Table2 (this table contains under 200 records) to a second instance of OLE DB, which then filters these 2000 records down to a handful of matching records and returns that recordset to the ASP processor. That seems absurdly inefficient. Am I right, or is there some intelligence on the back end that collapses the two layers of queries into a single operation?
    Attached Images Attached Images

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: How inefficient is this? (FPDB-ADO) (A2000/Jet4/SQLServer2K)

    Since nobody else has picked up the gauntlet, why not? Probably the first question is how many times in a day does this process occur? If it's once or a handful of times during the day, what, me worry!

    If it's more frequent than that, and is exposed the the Internet, so that you can't really guage potential demand, then some concern is probably in order. If by OLE DB, you are really referring to ODBC, which is how most Access databases link, then the ODBC driver should be converting this to a single query that runs against the tables. Note that you will have two connections to the SQL database that are more or less permanent. If you are actually using ADO to make the connection at the Access level, then things may get a bit more complicated, and you could well be passing the entire recordset of 2000 records to the MDB and then running the query there. ADO in it's infancy (read that Access 2000) isn't the epitome of efficiency, although it got considerably better with 2002. The final question is how robust is you SQL Server. If it's fairly beefy, you probably still don't need to be concerned, but if it's a Pentium Pro with 256MB of RAM, then you probably should. (The admins probably should too!)

    A final question - if the SQL Server database is inviolable, how did you get the tables you are using built? It would seem a view in SQL, or a stored proc executed via a pass through query would be the preferred way of handling it.
    Wendell

  3. #3
    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: How inefficient is this? (FPDB-ADO) (A2000/Jet4/SQLServer2K)

    <P ID="edit" class=small>(Edited by jscher2000 on 14-Feb-03 19:30. New info!)</P>Wendell, thanks. Let me go through that in reverse order. The inviolable database is "the accounting system," and the ASP application provides non-Accounting employees access to about a dozen specific queries. Average daily usage probably is in the low hundreds of database hits. Since we've never worked with SQL Server before, we simply pointed the linked tables to the new database (previously they pointed to an MDB) and started from there.

    The ASP code uses ADO to access the queries in the "front end" database. The front end database uses linked tables to access the "back end" database. I think I'm going to have to try some different scenarios and see what I can see. I'll try to report back anything concrete.

    <img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2>

    Well, I have converted one page from the FrontPage Database Component/intermediate MDB/SQL Server approach to a direct ADO query on SQL Server. Here some timing results for the second and subsequent queries in a session (first query is always slow due to establishing a connection):

    <table border=1 cellspacing=0 cellpadding=3 bordercolor=black bgcolor=yellow><td>Records retrieved</td><td>Old Way (seconds)</td><td>New Way (seconds)</td><td>2 - 6</td><td>0.1563</td><td>0.0156 to 0.0313</td><td>20</td><td>0.1875 to 0.2031</td><td>(not capped at 20)</td><td>33</td><td>(capped at 20)</td><td>0.0234 to 0.0469</td></table>

    Timings were calculated by setting time1 = Timer at the beginning of the ASP page, and inserting Timer - time1 in the document at the bottom of the page. The new code doesn't feel five to ten times faster, but that probably is due to the time the browser takes to render tables.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: How inefficient is this? (FPDB-ADO) (A2000/Jet4/SQLServer2K)

    Those results look pretty much as I expected they would. The difference in switching from ODBC connections though an MDB to a direct hit on SQL Server can typically give a 10 to 1 performance improvement. On the other hand, the 0.2 seconds or so retrieval time using ODBC is in the noise level compared to the IIS and browser times, so you don't really perceive a change. Given that, is it worth your time to recode everything?
    Wendell

  5. #5
    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: How inefficient is this? (FPDB-ADO) (A2000/Jet4/SQLServer2K)

    > Given that, is it worth your time to recode everything?

    Oh probably not, but here I am 15+ hours down the road already!! We are temporarily running the IIS stuff right on the SQL box, so the less resources used from start to finish the better. I've added a lot of requested functionality, and some stuff just "because I can."

    I also am running into some conversion issues with my queries, which used constructions that don't seem to be legal in SQL Server. The SQL Books Online also list lots of exciting-sounding things that I don't think are available through ADO. Even with all those blind learning curves (to mix metaphors), the ASP and web design challenges are taking at least half the time. Once freed from the constraints of FrontPage's database components, my imagination starts running wild down all sort of blind alleys. (I think the eyestrain is starting to get to me. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> )

Posting Permissions

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