Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Pass through query horrible with SQL-server 2008

    We've recently migrated a lot o databases from SQL-server 2000 to SQL-server 2008. Luckily I still have both systemes available, as I'm experiencing insane query times from 2008 in Access (2003/2007)

    Using a pass through query I execute a stored procedure with three variables.

    In access 2003/2007 sending the query to the sql-server 2000 gives a response time in roughly 8-12 minutes depending on other load.

    In access 2003/2007 sending the query to the sql-server 2008 gives a response time in excess of 1 hours.

    If I open a query window in SQL management studio on sql-server 2008, and execute the same query that tok in excess of 1 from there, it takes 8-12 minutes.

    So apparently there's some complication with pass-through queries and SQL-server 2008.

    Any suggestions?
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How adept are you with Profiler? If the answer is "not much" then prolly best to get reading up
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    And running SQL Server traces.

    I did several tests on this. Unbound forms with functions to write/get the data to/from the forms works ideal. I actually had a slower response time calling a stored procedure than I did using Linked SQL Server tables and functions (in modules) to write/read the data (although I'm sure a savy stored procedure coder could've fined tuned it.)

    Try designing your stored procedure in a query(s) in MSAccess (with the SQL Server tables linked into the mdb.) I re-structured my data so 5 million recs could be totalled within 10 minutes. I used a combination of make-table and append queries and also added common grouping fields to some of the main tables (ie. I broke the golden normalization rules.)
    Last edited by pkstormy; 02-01-10 at 23:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, just to check for parameter sniffing, could you submit your pass through with the following appended to the end?
    Code:
    WITH RECOMPILE
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2005
    Posts
    183

    With recompile

    With recompile seems to affect the speed of the query. Not quite as fast as on 2000 but certainly significantly. I'll keep testing it in a few scenarios.

    On the profiler. Haven't touched it since SQL 7.0, and can hardly recall how.

    Thanks on the suggestions though, will keep you posted.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How complicated is the query? How many rows returned? Are some of the parameters passed optional, or filter on columns with uneven distribution?

    Do you have a DBA?

    WITH RECOMPILE will ensure the "best" plan is generated given the situation (taking current statistics and the supplied parameters into consideration) however the compilation process will take a little time. I think the longest I've ever seen plans compile is about 30 seconds. Usually, if you are measuring the query time in minutes then recompilation is worth the extra time.

    SQL 2008 had some changes to the query engine compared to 2005 (and especially compared to 2000) as well as plan caching however I can't really give too many details on this.

    An Access passthrough query simply submits a string to SQL Server for execution. The source should have no direct affect (except for some background settings that will affect the plan caching) on performance. As such, you will probably get better support in the SQL Server forum. Let me know if you want this moving.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2005
    Posts
    183
    I used to be MCDBA certified with 7.0 and programmed SPs, but have since wound up doing infrastructure and now I'm a project manager helping out with strange occurances that no-one knows how to solve or dig into.

    As you write "pass through" does exactly what the name implies, it passes the command on to the SQL-server. Which basicaly explain why I'm lost for words when the exact same command, executed from a management studio query window completes between 8-12 minutes.

    The SP is a fairly simple group by select statement based on 5 views (the views are merely language trsnslations of english tables, they dont join tables).
    The receives 3 parameters, an ID (indexed) a to date, and a from date.

    Basically select 7 columns, group them by 6, sum the 7th.

    Its pretty data intense, as the primary table holds 20 gb of records with plenty of inedex.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Different connections, though, specify different values for (IIRC) seven different settings. Each plan is based on the combination of these seven settings. Usually you would not expect these settings to result in a difference in the execution of the query, but they do mean that the same command executed from different applications can use different plans.

    How many rows returned? Remember that SSMS starts displaying rows as soon as they are returned which may well be before the query has completed. Again, IIRC an Access passthrough will wait until ALL rows have been returned before displaying. Also, are both Access and SSMS being run on the same machine (i.e. there is no difference between the two WRT results being sent over the network)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2005
    Posts
    183
    Rows returned can vary between 10 to 15000, consisting of approx. 12 columns, all text or numeric. No blobs.

    Having made 10-15 attempts adding recompile, I noticed that the same query could vary form 10 minutes to 2 hours or even completeley locking up, despite no blocking processes in AM.

    From what I understand, using recompile will generate a new execution plan for the query. What is that executation plan based on? Stats?

    I noticed yesterday, that we have no statistics maintainance job running on the specified DB. Am I onto something? On the old SQL2000 we had a weekly monster stats job running (approx 8 hours runtime).

    Cheers, T
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The query will be based on all sorts, but the statistics play a major part of it. A lot of it is guesswork really without breaking out profiler and looking at the actual execution plans, checking the system tables & DMVs, running sysmon to monitor any hardware bottlenecks etc.

    You don't need to necessarily run statistics jobs as part of maintenance. If the auto recompute statistics is set then this will run every time around 30% of the column values change.

    I strongly recommend you open a new thread in the SQL Server forum, outlining what you have learned so far and linking to this thread. This is a SQL Server DBA issue. I think I am the most accomplished SQL DBA that frequents the Access forum yet I have not had production responsibilities in this area for a couple of years. There are a lot of DBAs in the SQL Server forum that are 10 times better than I ever was - they could help you more.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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