8

I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this:

SELECT ISNULL(SEId + '-' + PEId, '0-0') AS Id,
   *,
   DATEADD(minute, Duration, EventTime) AS EventEndTime
FROM (
    SELECT se.SEId, pe.PEId,
        COALESCE(pe.StaffName, se.StaffName) AS StaffName, -- << Problem!
        COALESCE(pe.EventTime, se.EventTime) AS EventTime,
        COALESCE(pe.EventType, se.EventType) AS EventType,
        COALESCE(pe.Duration, se.Duration) AS Duration,
        COALESCE(pe.Data, se.Data) AS Data,
        COALESCE(pe.Field, se.Field) AS Field,
        pe.ThisThing, se.OtherThing
    FROM PE pe FULL OUTER JOIN SE se 
      ON pe.StaffName = se.StaffName
     AND pe.Duration = se.Duration
     AND pe.EventTime = se.EventTime
    WHERE NOT(pe.ThisThing = 1 AND se.OtherThing = 0)
) Z

That probably doesn't justify the whole reason for the query structure, but maybe gives you an idea--this view joins two very poorly designed tables that I don't have control over and tries to synthesize some information out of it.

So, since this is a view used from the application, while trying to optimize I wrap it in another SELECT, like this:

SELECT * FROM (
    -- … above code …
) Q
WHERE StaffName = 'SMITH, JOHN Q'

because the application is searching for specific staff members in the result.

The problem seems to be the COALESCE(pe.StaffName, se.StaffName) AS StaffName section, and that I'm selecting from the view on StaffName. If I change that to pe.StaffName AS StaffName or se.StaffName AS StaffName, the performance problems disappear (but see updated 2 below). But that won't do because one side or the other of the FULL OUTER JOIN could be missing, so one or the other field may be NULL.

Can I refactor this replacing the COALESCE(…) with something else, which will get rewritten down into the subquery?

Other notes:

  • I've already added some indexes to fix performance problems with the rest of the query--without the COALESCE it is very fast.
  • Somewhat to my surprise, looking at the execution plan does not raise any flags, even when the wrapping subquery and WHERE statement is included. My total subquery cost in the analyzer is 0.0065736. Hmph. It takes four seconds to execute.
  • Changing the application to query differently (e.g. returning pe.StaffName AS PEStaffName, se.StaffName AS SEStaffName and doing WHERE PEStaffName = 'X' OR SEStaffName = 'X') might work, but as a last resort--I'm really hoping I can optimize the view without having to resort to touching the application.
  • A stored procedure would probably make more sense for this, but the application is built with Entity Framework, and I could not figure out how to get it to play nice with a SP that returns a table type (another topic entirely).

Indexes

The indexes I've added so far look something like this:

CREATE NONCLUSTERED INDEX [IX_PE_EventTime]
ON [dbo].[PE] ([EventTime])
INCLUDE ([StaffName],[Duration],[EventType],[Data],[Field],[ThisThing])

CREATE NONCLUSTERED INDEX [IX_SE_EventTime]
ON [dbo].[SE] ([EventTime])
INCLUDE ([StaffName],[Duration],[EventType],[Data],[Field],[OtherThing])

Update

Hmm…I tried simulating the stricken change above, and it didn't help. I.e, before ) Z above, I added AND (pe.StaffName = 'SMITH, JOHN Q' OR se.StaffName = 'SMITH, JOHN Q'), but the performance is the same. Now I really don't know where to start.

Update 2

@ypercube 's comment on needing the full join made me realize that my synthesized query left out a probably important component. While, yes, I need the full join, the test I did above by dropping the COALESCE and testing just one side of the join for a non-null value would make the other side of the full join irrelevant, and the optimizer was probably using this fact to speed up the query. Also, I've updated the example to show that StaffName is actually one of the join keys--which probably has significant bearing on the question. I'm also now leaning toward his suggestion that breaking this into a three-way union instead of full join may be the answer, and will simplify the abundance of COALESCEs I'm doing anyway. Trying it now.

  • What indexes have you added? Are you including the StaffName in the index? – Mark Sinkinson Jul 14 '14 at 10:10
  • @MarkSinkinson I have a nonclustered index on each table on KeyField, both indexes INCLUDE the StaffName field and several other fields. I can post the index definitions in the question. I'm working on this on a test server so I can add any indexes you think might be helpful to try! – S'pht'Kr Jul 14 '14 at 10:15
  • 1
    You have the WHERE pe.ThisThing = 1 AND se.OtherThing = 0 condition which cancels the FULL OUTER join and makes the query equivalent to an inner join. Are you sure you need a FULL join? – ypercubeᵀᴹ Jul 14 '14 at 12:51
  • @ypercube I'm sorry, that was bad air-coding on my part, point is more that i've got conditions on both tables, but yes I account for nulls on either side in the real query. I'm merging the two tables and looking for matches, but I need the available data from either table when there's not a matching record in left or right--so yes, I need the full join. – S'pht'Kr Jul 14 '14 at 15:00
  • 1
    A thought: it's a longshot but you can try to break the internal query into three parts (INNER JOIN, LEFT JOIN with WHERE IS NULL check, RIGHT JOIN with IS NULL) and then UNION ALL the three parts. This way there will be no need to use COALESCE() and it might (just might) help the optimizer figure out the rewriting. – ypercubeᵀᴹ Jul 14 '14 at 15:25
4

This was rather longshot but since the OP says it worked, I'm adding it as an answer (feel free to correct it if you find anything wrong).

Try to break the internal query into three parts (INNER JOIN, LEFT JOIN with WHERE IS NULL check, RIGHT JOIN with IS NULL check) and then UNION ALL the three parts. This has the following advantages:

  • The optimizer has less transformation options available for FULL joins than for (the more common) INNER and LEFT joins.

  • The Z derived table can be removed (you can do that anyway) from the view definition.

  • The NOT(pe.ThisThing = 1 AND se.OtherThing = 0) will be needed only on the INNER join part.

  • Minor improvement, the use COALESCE() will be minimal if any at all (I assumed that se.SEId and pe.PEId are not nullable. If more columns are not nullable, you'll be able to remove more COALESCE() calls.)
    More important, the optimizer may push down any conditions in your queries that involve these columns (now that COALESCE() is not blocking the push.)

  • All the above will give the optimizer more options to transform/rewrite any query that uses the view so it may find an execution plan that indexes on the underlying tables can be used.

In all, the view can be written as:

SELECT 
    se.SEId + '-' + pe.PEId AS Id,
    se.SEId, pe.PEId,
    pe.StaffName, 
    pe.EventTime,
    COALESCE(pe.EventType, se.EventType) AS EventType,
    pe.Duration,
    COALESCE(pe.Data, se.Data) AS Data,
    COALESCE(pe.Field, se.Field) AS Field,
    pe.ThisThing, se.OtherThing,
    DATEADD(minute, pe.Duration, pe.EventTime) AS EventEndTime
FROM PE pe INNER JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (pe.ThisThing = 1 AND se.OtherThing = 0) 

UNION ALL

SELECT 
    '0-0',
    NULL, pe.PEId,
    pe.StaffName, 
    pe.EventTime,
    pe.EventType,
    pe.Duration,
    pe.Data,
    pe.Field,
    pe.ThisThing, NULL,
    DATEADD(minute, pe.Duration, pe.EventTime) AS EventEndTime
FROM PE pe LEFT JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (pe.ThisThing = 1)
  AND se.StaffName IS NULL

UNION ALL

SELECT 
    '0-0',
    se.SEId, NULL,
    se.StaffName, 
    se.EventTime,
    se.EventType,
    se.Duration,
    se.Data,
    se.Field,
    NULL, se.OtherThing, 
    DATEADD(minute, se.Duration, se.EventTime) AS EventEndTime
FROM PE pe RIGHT JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (se.OtherThing = 0)
  AND pe.StaffName IS NULL ;
0

My intuition would be that this should not be a problem as by the time COALESCE(pe.StaffName, se.StaffName) AS StaffName does anything all the rows from the two sources should have already been pulled in and matched up so the function call is a simple in-memory compare-to-null-and-pick. Obviously this isn't the case so perhaps something in one of the sources (if they are views or inline derived tables) or the base tables (i.e. lack of indexes) is making the query planner think it needs to scan these columns separately.

Without more detail of the exact query you are running, the supporting structures, and the query plans produced, anything we suggest is conjecture.

To try to force the comparison to be done after all else, you could try just select out both values in the deribed table (pe.StaffName AS pe.StaffName, se.StaffName AS seStaffName) then do the pick in the outer query (COALESCE(peStaffName, seStaffName) AS StaffName), or you could even push the data from the inner query into a temporary table then do the outer query by selecting from that (but that would require a stored procedure, and depending on the number of rows this dump-to-tempdb could be expensive and therefore problematic in its own right).

  • Thanks David, I've been erring on the side of paranoia as to how much I should disclose on this even as far as structure (pe => PatientEvent, so…) but I know that makes it harder. I think it is in fact doing the join based on indexes and then doing a "simple in-memory compare" to filter…but the unfiltered derived table Z currently comes back with ~1.5m rows. What I want it to do is to rewrite that predicate down into the query for Z so it will use the indexes…but now I'm also confused because when I manually put the predicate there, it still doesn't use an index…so now I'm not sure. – S'pht'Kr Jul 15 '14 at 7:18

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.