2

I have some slow running queries (8 seconds across 1M records grouped by months) and have been trying to make sense of the execution plans. We use around 10 TVPs to send in a set of filters that the user chooses and I have an indexed view that accounts for 84% of the cost of the query.

The execution plan is pretty large and can't be uploaded here due to the size so I have uploaded it to here

I've spent quite a lot of time trying to optimise these queries (there are 14 of them but the core of each is much the same) and would appreciate anyone's suggestions or hints in reading them. I have also implemented the query suggested by the actual execution plan and the query was 5 times slower?

  • 1
    What exactly does the indexed view do? I've seen a lot of performance problems stemming from people slapping an index on a view, thinking it's a magic "make this view go faster" button. If the view does not perform aggregations, it is not likely to make perf better, in fact quite often you end up making it worse because that clustered index is wider - and may include columns from more tables - than what would have happened if you just used only the tables you needed in the query. – Aaron Bertrand Jun 13 '14 at 14:55
  • 1
    Looking at the Exec Plan, the row estimates are way off. Are Stats updated on the tables. Also, can you try using TEMP tables instead of using table variables ? – Kin Shah Jun 13 '14 at 15:01
  • @AaronBertrand The indexed view holds static data from several tables, mainly key values, that were used in several joins that were common across all queries. As the data is loaded once a day and doesn't change it seemed sensible to keep this data in a view that was indexed on the key value that was used in the first join. The majority of the queries work faster with the view. The ones that didn't don't use it. – bhs Jun 13 '14 at 15:31
  • @Kin thanks for the link :-) I've heard mixed things about writing TVPs to tempdb but your link contained another link that is of great interest as we are looking to move to 2014 asap. – bhs Jun 13 '14 at 15:37
  • I've also blogged about in-memory TVPs - see the top 3 results here. – Aaron Bertrand Jun 13 '14 at 15:46
11

I don't think this query is about TVPs so much as complexity. The plan has an optimizer timeout. You can see it in the F4 properties of the SELECT operator as "Reason for Early Termination of Statement = Time Out" or StatementOptmEarlyAbortReason="TimeOut" in the plan xml. These are not always a disaster, it just means the optimizer has run out of time (more iterations) to come up with a plan and picks the lowest cost one it has at that stage. Optimizer timeouts are often a sign of over-complexity in a query and the common recommendation is to simplify, eg break it up, remove unnecessary parameters and joins etc

Looking at your query, there are 13 tables. Looking in the plan, many of them just do a "SELECT all records" from the lookup table ( eg all records from dbo.Station for the @from and @to tables, all ticketFilter and ticketClass records ), so actually you don't need these tables and removing them does not change the result. I understand this is a user-driven query, so you might look at approach where you only join to the tables if the user has actually applied a filter.

I can reproduce the timeout, and remove it by commenting out 4 of the tables, eg:

SELECT d.Month_Year[PeriodName], qg.Title [Service_Area], AVG((CAST(sa.Answer AS smallmoney) * 10.00)) [Average]
FROM [dbo].StaticDataView d WITH (NOEXPAND)
    INNER JOIN [dbo].[Survey] s  ON (s.CustomerJourney = d.Journey_Id)
        INNER JOIN [dbo].[SurveyAnswer] sa   ON (sa.Survey = s.ID)
            INNER JOIN [dbo].[Question] q  ON (q.ID = sa.Question)
                INNER JOIN [dbo].[QuestionGroup] qg  ON (qg.ID = q.QuestionGroupID)

    --INNER JOIN @StationFrom sfl ON (sfl.ID = d.[Source])
    --INNER JOIN @StationTo stl ON (stl.ID = d.[Dest])
    INNER JOIN @InitialScores sc ON (s.Score = sc.ID)
    INNER JOIN @RouteList rl ON (rl.ID = d.Route_ID) -- route list
    --INNER JOIN @TicketClass tc ON (tc.ID = d.Class_ID)
    --INNER JOIN @TicketTypeFilter ttl ON (ttl.ID = d.Ticket_Filter_Type_ID)
    INNER JOIN @TicketDiscount td ON (td.ID = d.Discount_Type_ID)
    INNER JOIN @Days dy ON (dy.ID = d.[DayOfWeek])

WHERE s.DateCompleted IS NOT NULL AND sa.Answer != -1
AND (d.RSID = COALESCE(@RSID, d.RSID))
AND (d.[Date] BETWEEN @Start AND @End) -- between dates
AND (d.[Time] BETWEEN @StartTime AND @EndTime) -- between times
AND ((@Direction IS NULL AND d.Direction IN (0,1)) -- Both
        OR (@Direction = 1 AND d.Direction = 1) -- North
        OR (@Direction = 0 AND d.Direction = 0)) -- South
AND ((@PassengerJourney = 0 AND d.Journey_Type_ID IN (1,2,3)) -- ALL
    OR (@PassengerJourney IN (1,2) AND @PassengerJourney = d.Journey_Type_ID) -- Single or Return
    OR (@PassengerJourney = 3 AND NULLIF(d.RSID_LEG_2,'') IS NOT NULL)) -- multi part
GROUP BY d.Month_Year, qg.Title
OPTION ( RECOMPILE )

Does this make for a better plan? Hard to say, as in my simple rig, I can't get this query to run in more than half a second, with or without the timeout. Even SQLFiddle doesn't seem to struggle. Can you see much difference between the SQLFiddle repro and your setup?

  • thanks for your comments, I'll look into them in more depth when I get chance. The filters were design to accept multiple values, so the query could be restricted to 30 start and end stations out of the 2000 available. – bhs Jun 16 '14 at 8:56
  • multi pass spill ? – bhs Jun 17 '14 at 16:47
  • @wBob - this was a great starting point and thanks for your efforts. There are more questions than answers at the minute and the performance of this database changes by the minute. We are looking at the server setup (VM) and also looking at removing some of the TVPs. At the minute it's very, very frustrating. I'm happy to mark this as answered and will raise any other queries through new questions. Thanks again :-) – bhs Jun 18 '14 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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