Results 1 to 15 of 15
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065

    View vs SQL

    I have a query in MS SQL Server Management Studio that runs in about 40 Seconds. When I use that query to create a view and then query the view it takes as of now 25 plus minutes to return the data?? any thoughts.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    lack of indexing, server busy doing other processes?

    not clear if you are querying sql server from access

    views have their uses (usually around simple joins) but if you are using it to produce a heavily calculated view to which your subsequent query is looking for a subset, probably better to use a stored procedure.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you could try a pass thru query.

  4. #4
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Quote Originally Posted by ranman256 View Post
    you could try a pass thru query.
    My original post says It's through MS SQL Server Management Studio Not using Access for this portion of the report.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Performance tuning isn't a strength of mine, but I'll throw out the alternative of a table-valued function. I use them because I can pass them parameters like I can a stored procedure (and you can't pass them to a view), but I can also use one in a query:

    SELECT Blah FROM FunctionName(Parameters)

    I can't say it will solve your problem, but it might be worth a try.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, and I deleted your duplicate thread in the Sharepoint forum.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Quote Originally Posted by pbaldy View Post
    Oh, and I deleted your duplicate thread in the Sharepoint forum.
    Thanks. I submitted it and then looked and couldn't find it so I did it again must have put it into the wrong forum that's why I couldn't find it.

  8. #8
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Quote Originally Posted by pbaldy View Post
    Performance tuning isn't a strength of mine, but I'll throw out the alternative of a table-valued function. I use them because I can pass them parameters like I can a stored procedure (and you can't pass them to a view), but I can also use one in a query:

    SELECT Blah FROM FunctionName(Parameters)

    I can't say it will solve your problem, but it might be worth a try.
    It's not a table valued function. It's a query that I'm using to create a view in SQL Server.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by RayMilhon View Post
    It's not a table valued function. It's a query that I'm using to create a view in SQL Server.
    I know it's not, that's why I offered the TVF as an alternative. You could create a TVF with the query, add parameters if you want, and see if it's any faster than querying the view.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Have you tried looking at the query execution plan in SSMS?
    Does your view have a primary key field?

    Out of interest if you have it working reasonably fast as a query why are you creating a view and querying that anyway?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Quote Originally Posted by isladogs View Post
    Have you tried looking at the query execution plan in SSMS?
    Does your view have a primary key field?

    Out of interest if you have it working reasonably fast as a query why are you creating a view and querying that anyway?
    The query is the first step for generating the data for the report. This query gets the Raw Data. A second query does some clean up and then a report summarizes and gives them the data they want. If I put the first query into Access It returns too much data and errors. I need to query on the server to reduce network traffic and speed things up. Because the final report going to go out daily and I'm trying to automate it. Seemed the best way was to create a view I can query for the report.

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sounds more and more like a stored procedure is the way to go - pass the parameters to the stored procedure and have the final recordset returned which you can assign to the report. might be something like this

    function getsqldata(SPName as string, optional params() as variant) as variant
    'code to execute stored procedure and assign resulting recordset to function
    end function


    and in the report load event

    set me.recordset=getsqldata("SPReport", date1, date2)

  13. #13
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    In SQL server procedures are no faster than views using the same SQL. Be carefull, order by in a view only make sense with a TOP instruction, otherwise they only slow down the view. As a SQL DBA I would first check my indexes and index fragmentation. There are some buildin reports that show the index usage and query performance. Create a view from the query, making sure the indexes are used. You can use SQL Server profiler to analyse the query.
    Don't forget to set the proper security for the view.
    Only if with:
    A second query does some clean up
    you mean this query updates, inserts or deletes data, you need a procedure. Otherwise T-SQL is in most cases powerfull enough to the job in one query.

    If you already use SQL Server, why don't you use SQL Reporting to create your reports? You can puplish your reports on the intranet and print them from any location that has access to the website and the report. You can put security on a report level deciding who can view which report, and you can download the reports in pdf, Excel, XML and a few other formats.

  14. #14
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm agree with the others comments, 40 seconds is quite along time for a query to run directly on a SQL server unless there are millions of records involved.
    25 minutes for a view makes no sense if it is the same query. Use the execution plan on the query - it will highlight any missing indexes, and give you some hints as to what's slowing things down.

    Don't do any sorting at this point.
    And as others have said a stored procedure or TVF could use parameters to limit the number of records directly, further reducing the records returned and ultimately the processing time.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  15. #15
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Can you post the T-SQL of the query? Then we could have a first check on slowing constructions like subqueries or filtering on calculated expressions.

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

Similar Threads

  1. Replies: 10
    Last Post: 08-23-2019, 11:48 AM
  2. Replies: 10
    Last Post: 02-06-2018, 02:37 PM
  3. Replies: 2
    Last Post: 03-05-2015, 01:10 PM
  4. Replies: 2
    Last Post: 03-09-2014, 03:21 PM
  5. Replies: 8
    Last Post: 04-29-2013, 11:23 AM

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