2

View the actual execution plan here

The plan is showing what happens when a stored procedure is called to get the payments that are associated with a client.

Unfortunately there is no other way to get this data than by inner joining a whole bunch of tables together.

Objec5.Index11 is on the Payments table (nothing sensitive, just a record from the gateway to confirm the transaction). This is 457mb of data according to SQL Sentry Plan Explorer. The rest of the clustered index seeks following on from the inner joins are all roughly the same amount of data equating to somewhere between 10% and 13% of the execution cost of this query.

My questions are:

  • What is the best approach to getting data from multiple tables? I've joined them together via inner joins and dropped them into a view
  • Should the view have a clustered index? Can this only be achieved with schemabinding? If so, will this prevent field level changes to all tables within the view?
  • Would having better estimated row values help SQL Server run this query more efficiently? Or is the approach so fubar that this just is not efficient by design?
  • Is there anything i can do in the short term to boost the performance of this query?

Any help would be appreciated.

I've provided responses to each of the questions below:

  • SET STATISTICS IO ON output here
  • All of the tables have a clustered index on them on the primary key
  • The query consistently performs poorly. However, modifying the query to join on the tables themselves as opposed to views speeds the time the stored proc takes to run quite significantly (from 20 mins to 6 mins) but it still is not fast enough for production
  • The query used to run within 1 - 2 minutes. Now the fastest is 6 minutes
  • Index11 is the payments table, there are 1,570,473 rows according to the execution plan actual rows. This is the same table where there were over 4 million logical reads and 1.4 million scans - This does not seem normal to me or conducive to performance? I could be wrong ...
Improve this question
8
  • Your seeks have a big difference between estimated rows and actual rows. That's one place to start.
    – Forrest
    Mar 1, 2017 at 22:16
  • How would I go about getting that estimate more accurate? I've tried running the query through the analyzer and applying its recommendations which did a bunch of statistics stuff, but the estimates are still way off?
    – user2997757
    Mar 1, 2017 at 22:17
  • 1
    Without knowing the table structures or volume of data it's a tough thing to ask. creating necessary indexes is a good start and it could be due to the schema and hardware you are running on it's as good as it gets. One option to consider is creating a proc and leveraging #temp tables that contain sub-sets of your data and using these temp tables in your joins.
    – Jeff A
    Mar 1, 2017 at 22:20
  • You can try to update statistics on the tables referenced in the view. You can force hash joins on the joins that use nested loops operators, what may help.
    – Piotr Rodak
    Mar 1, 2017 at 23:59
  • 2
    I took a look at the plan and nothing stands out, so lots of questions for you. Please edit this information into the question as it makes sense to do so. Which version of SQL Server? Which trace flags are on? Can you add SET STATISTICS IO, TIME ON output? Does the query consistently perform poorly? Was there blocking? Do other queries perform poorly? You imply that the query used to be faster before the view. That could mean that a predicate isn't being pushed down. How fast did the query used to be? Can you describe the change you made in more detail? Thanks!
    – Joe Obbish
    Mar 2, 2017 at 4:49

1 Answer 1

Reset to default
0

Unable to get to the root cause of the performance issue I've backed out the stored procedure changes. These changes introduced mostly left joins on views. Not sure whether the 1.4m row table scan was having an impact on performance.

With the code changes reverted reports are now running every 3 - 4 minutes. This includes the time to write the file to disk. This code will likely not see the light of day again.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.