Results 1 to 6 of 6

Opinions on PL/SQL and pass through query

  1. #1
    thart21 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    83

    Opinions on PL/SQL and pass through query

    I have an Access db form with a button that runs through 12 various queries to get to my 13th and final one that exports out to Excel. Combination of SELECT, MAKE TABLE, DELETE AND APPEND queries on linked Oracle tables.
    Tens of thousands of records (some in the 100k+) but only pulling a subset based on a drop down box on my form. Query takes about 3-5 minutes to complete.
    I've been reading about pass through queries and PL/SQL for Oracle and am wondering what the opinions are out there about doing this to speed up my queries. I will need to learn PL/SQL but, if it won't make a significant speed improvement, I won't bother with it at this time.



    Would appreciate anyone's thoughts on this.

    Thanks,

    Toni

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    3-5 minutes isn't long at all for 12 query + export for 100k+ records. You wont see much significant change in query time. The only times I bother with a passthru is when i got processes >30mins.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,730
    I lean towards disagreeing with TheShabz, though it depends on your specifics. I deal with SQL Server tables with up to a couple of million records. If I had processes take 3-5 minutes, users would have me strung up. My guess is that the nature of your queries is requiring Oracle to transfer entire tables over the wire so that Access can work with them, perhaps more than once. If that's true, you can see significant speed improvements by forcing the processing to be done on the server, either via pass through queries, stored procedures, etc, and only have the final product sent back over the wire. You might test on the first one or two and see what happens.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    pbaldy,

    Do you mean strung up because you have the tables locked for 3-5 mins and they cant use it or that the task is taking so long? I guess its easier for me because I came in as a savior and turned their 2 hour Excel work into 15 min Access workflows. I guess it does depend on the work done and who its for. But I can honestly say that I've never had an issue with a 3-5 minute wait for a workflow that put out 100k+ records.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,730
    I was thinking because the task took so long, though both would probably be true. If something kept them from doing their work for that long they'd scream. I got the impression from the OP that there were 100k records in tables, not that the process put out 100k (though I would still expect an export of 100k records to run faster than that).

    I'm jealous of you and your patient users though!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Now I'm intrigued. Our account table alone has about 6 million actives, let alone God knows how many historical accounts still on there. I often outer-join to another table with 2-3 mil records on it. 5-6 queries later, and export on an access db stored on a network drive. 3-5 mins sounds pretty good to me. =P

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pass a value from a query to a form
    By cwwaicw311 in forum Forms
    Replies: 22
    Last Post: 03-22-2010, 10:21 AM
  2. Pass a value from a form to another
    By cwwaicw311 in forum Forms
    Replies: 3
    Last Post: 03-16-2010, 12:42 AM
  3. Pass a Parameter From a form to a Query
    By DDillesha in forum Forms
    Replies: 1
    Last Post: 10-28-2009, 12:49 PM
  4. I want to automatically pass a value to a query
    By Slategrey252 in forum Queries
    Replies: 1
    Last Post: 10-01-2009, 05:38 AM
  5. My Access Upsizing Nightmare...Opinions and Thoughts?
    By Jerimiah33 in forum Import/Export Data
    Replies: 0
    Last Post: 01-30-2007, 12:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums