Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: Basic question on queries verses opening an ADO recordset

    In MS Access XP, I have a basic question regarding a complex query (i.e 7 SQL Server linked tables) verses opening up an ADO recordset in a function in MSAccess. Using the same criteria in each (both being connected via ODBC linked tables and both making a "totals" table), is it faster execution-wise to do this via a query or via writing ADO code to do the same thing (all other parameters being the same in both) or are they equal in execution time? I know there are faster ways (i.e. stored procedures, SQL views, etc.), but I'm wondering if there is a difference in a complex query verses writing a complex SQL statement in ADO. Are there any articles regarding this subject?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Paul

    The best thing to do is benchmark it (i.e. set a double variable to the Timer() value, run the query and compare to the Timer value again. Repeat). However, my money would be on the Access query - it is compiled so access has had a sight of the query and will have completed an execution plan. Everytime you run a query contained in a string from code, access acts as though it is the very first time it has had to run the query each time you run it. Personnally, I wouldn't run much more than "SELECT Col1, Col2 FROM T1 INNER JOIN T2 ON T1.ID = T2.ID" from code - any more than that and I would be looking at sprocs of views.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - just thought - the reply is based on the ADO connection being made to the A FE. If you connected to the SQL Server than the SQL Server would do the work - in that case I think my money would be on ADO.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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