Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    15

    Unanswered: What is faster for information retrieval?

    Hi,

    Every morning I need to run thirteen queries on a database table through ODBC. I am guessing the size of this database is around 10-15 million rows and approximately 50 columns. Each query is aggregating a year of data by month with various characteristics and seems to take about five or ten seconds to run. I plan on accomplishing the running of these queries through VBA and their results will be stored in an array that will be printed into an Excel Workbook.

    My question is this: would it be faster to pull the entire data-set that I need for my report's purposes into a table (approx 150k rows, 10ish columns) and then quickly run the thirteen queries off of this smaller data-set?

    If anyone has any advice, comments, or critiques, they would be much appreciated.

    Thanks,

    Eric

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    If you use ODBC, link to the table and pull off all the rows/columns you need for the 13 reports into a new Access table. Discard the link. Run your 13 queries/reports off the new table.

    Sam

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Another option if supported by the back end is using stored procedures or pass-through queries to force the processing to happen on the back end. That way only the result set is going across the wire.
    Paul

  4. #4
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by pbaldy View Post
    Another option if supported by the back end is using stored procedures or pass-through queries to force the processing to happen on the back end. That way only the result set is going across the wire.
    This is what I do. We run a massive as400 here and all the data i work with comes from ODBC to the server. I set up a back end DB to with an auto exec macro (that creates make tables for the data i need)to run at apprx 5am in the morning. When the users come in the data is fresh and runs instantly.

Posting Permissions

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