Results 1 to 4 of 4
  1. #1
    cavigbeji
    Guest

    enterable criteria

    First off, I am using ODBC to access a mainframe database. I have written a macro to create a table to store criteria that a user enters. Then the macro runs several queries using the stored criteria using a DLookup function. It seems to be taking a really long time to run the queries vs. running them without the lookup criteria. Is there a better way to approach this and/or how can I reduce the runtime. Thanks

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

    Re: enterable criteria

    What is the mainframe database in? If it is a database server like SQL Server, Oracle, etc., then you don't really want to have Jet manage the query because it will be very slow on large datasets. You would be better advised to create a stored procedure on the back end that accepts parameters and call it with the values from your table. That way, the back end will do the work, and it should execute in a reasonable length of time. However, since you didn't include any real details about the programs or versions or the number of records involved, there isn't any way to give you more specific answers.
    Charlotte

  3. #3
    cavigbeji
    Guest

    Re: enterable criteria

    We have a DMSII database and I will be accessing some structures with just a few records and other stuctures with up to 500,000 records.

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

    Re: enterable criteria

    In that case it sounds like you may be stuck doing it from Jet, unless you have a wizard who can build you stored procedures for your back end. If you're including the Dlookup in the query itself, that's part of the problem. You'd be better off building some code to pass the parameters into the saved query, doing your lookup there in the code and passing the result into the query. Dlookup is extremely slow, so you only want to execute it once at most. However, there may be other ways to optimize your SQL. Why don't you post it where we can all take a look and possibly suggest improvements.
    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
  •