Results 1 to 6 of 6
  1. #1
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    Stroed Procedure or Inline Table Valued Function??


    Background:
    Access FE with SQL Server Express BE. The main table has 1.5 million records, so report time is slow. I want to limit the records going across the LAN.

    Baby steps... just working on Reports right now.
    Still trying to convert the record sources (queries) from Access saved queries to something in SQL Server Express.
    As I understand it, Views are akin to Access saved queries, except Views cannot have parameters. So Views are out.

    I have one Access query converted to a Stored Procedure and I just created an Inline Table Valued Function.
    There are two parameters - company PK (long) and effective date (Date).
    Both the SP and the Fn appear to return the same data (still checking the values).


    Q1) Is one better than the other as far as being the record source for a report?

    Q2) I can execute both is SSMS, but how do I call them from the Access report? (can't find any examples)

    Q3) Do I create a saved pass through query, edit the query def to change the parameters (VBA) and use the saved PT query as the record source in the Access report? OR ..........????




    TIA

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I am by no means an expert, but I've used both. I use a saved pass through query and manipulate the SQL as you describe. I'm not connected to my development machine right now, but searching for "pass through fix up" or something along those lines should turn up functions to which you pass the SQL and query name and the function alters the query. The one I use also lets me specify the connection string and returns records properties.

    I only used stored procedures for a long time, but then discovered TVF's. What I was after was something I could select from or join to, which you can't do with a stored procedure (that I know of). My sense is you can do more complicated programming in a stored procedure, but don't quote me on that. I may just not know enough about TVF's.

    Dinner time for me, hungry wife.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,
    Q1: as a SQL DBA I prefer stored procedures, because they are more flexible and give you more control. For instance: you can use temp tables, error handling (try...catch), dynamic SQL (important weapon against SQL injections).
    In your case: first check out your indexes, the right index can speed up a query on 1,5 M records considerably. If you're SQL version has the option, you can activate the query store to manage this.
    Also, if one of the parameters is date, you probably don't need the data from 5 years ago. So maybe you can create an indexed view that only returns the data from the last 2 years and create a stored procedure on that view?

    Q2/3: It has been a long time since we used an Access front end, so my knowledge is probably outdated there, but I kind of remember I created an ADO cmd object to do the job.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Thanks Paul,
    I's nice to know that I'm at least on the right track. I'm only working on converting the report record sources (Access saved queries) to *something* in SQL Server right now, trying to cut down on network traffic.
    I'll start looking for the code to change the query def.
    I have a SP and a TVF for the same report - wasn't sure which one would be better/faster/easier. (after hours and hours of reading/watching videos still not sure )


    Thanks Noella,
    You are right - I very rarely need data from 4 - 5 years ago (historical request/review) - mostly need the last 3 months.
    I think a view is closest to an Access saved query, but a View (as I understand it) cannot have parameters like an Access saved query.
    Since I am only converting report queries right now, I think TVF's and code to modify Access PT queries is what I'll work on for now and save the SP for the future.


    Again, thank you both.....

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem Steve. Noella is far more knowledgeable about SQL Server than I, so any thoughts I offered should be taken with that in mind.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    A SQL Server view can't have parameters, but sometimes you can create a 'more general' view with fields representing possible values for those parameters instead. And then your FE simply uses the view with WHERE clause instead those parameters (properly designed SQL Server views are more efficient compared to Access queries, so usually it takes much more of data to slow them down).

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

Similar Threads

  1. Recursive Function using inline query vs Stored Query
    By greatwhite in forum Programming
    Replies: 1
    Last Post: 07-26-2019, 04:31 AM
  2. Replies: 4
    Last Post: 05-04-2017, 02:05 PM
  3. Replies: 4
    Last Post: 06-23-2014, 12:18 PM
  4. Monthly account closing function/procedure
    By Pragmatic in forum Programming
    Replies: 1
    Last Post: 11-15-2013, 12:41 PM
  5. Replies: 2
    Last Post: 07-19-2011, 04:28 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
  •  
Other Forums: Microsoft Office Forums