Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unanswered: mutiple queries in one: SQL-Server & A

    i have a big db recently migrated to MS SQL-Server

    one form currently queries the server for six lookups to feed various combos and then again for the data for the form itself. seven sequential passthru queries! it is taking a few seconds to fill the combos and then the form data.

    i'm a long way from hot with SQL-Server, but from my beginner's steps through the documentation it seems that SQL-Server has some sort of capability to accept multiple queries in one and return the records "somehow" ...to my Access front-end?

    so my stupid question: is it possible to write a single passthru that can then feed several combos from different tables. how?

    my fallback is to make local clones of the lookups... but they do change (ok, slowly, maybe once or twice a day). i would prefer to avoid this complication if i can do a single "meta-query".

    thanks for any thoughts.

    izy
    currently using SS 2008R2

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Adp or mdb?

    Also, could you please provide a more thorough explanation of the data you are attempting to return, and the structure you're querying?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Teddy,

    .MDB

    since posting that, i have more-or-less decided to make local clones of my lookups. i imagine a scheme using a status table with a timestamp for each lookup table ... if the user's copy of a lookup is older, make a new local table. it will be faster and easier than muti-recordset returns plus callbacks to populate the combos.

    the original problem:

    i have a unbound form with 6 combos plus some other fields

    the lookup tables for the combos are really simple:

    tblCurrency:
    IDcurrency(nvarchar 3); FXrate(float)

    the other five are all similar in structure: ID; text ...example:
    tblCountry:
    IDcountry(int); CountyName(nvarchar 24)

    then there is the record to be edited:

    tblWhatever:
    IDwhatever; IDcurrency; IDcountry, IDu; IDv; IDw; IDx; Stuff1; Stuff2; Stuff3 etc

    the pass-thru on tblWhatever performs reasonably well - comfortably below one second.

    but when i add filling the six combos with another six pass-thrus the response turns to treacle: each query takes a comfortable sub-second but add the series together and there is a 5 second lag: unpleasant.

    izy
    Last edited by izyrider; 06-11-04 at 04:06.
    currently using SS 2008R2

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Carefull with the "timestamp" feature of sql server, many people mistake it's actual intention. I think you have the right idea, just making sure

    Also, have you indexed the keys on your target lookups? If you haven't, be prepared for a massive performance gain.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sloppy use of the "timestamp" word: sorry.
    i should have said UPDATE with GETDATE()

    i think i'm pretty well indexed where i need to be, but i'll double check.

    izy
    currently using SS 2008R2

Posting Permissions

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