6

I'm a c# developer, however I'm quite experienced with SQL. I have had a bit of experience with optimising queries but I've come up across one that has me stumped.

The query takes about 3 minutes to run for approximately 20,000 rows. The rows start streaming immediately but for the 3 minute period, the SQL server cpu is pegged at 100%.

In this particular case, my queries performed well until the client requested I test them with 20k rows. These 20k rows (Clients) were distributed amongst 20 Practitioners (so roughly 1000 each)

Here's my database schema for the relevant tables:

enter image description here

The query uses a Recursive CTE to gather, given a source practitioner, the (n-level) tree of clients below them. It then joins to some FK type tables to collect additional information.

It appears the slow-down is coming from the join to the authority table, which represents which practitioners manage which clients.

enter image description here

pooling.ClientAccountInheritedRateGroup, pooling.ClientAccountFirstContact and pooling.PractitionerAccountFirstContact are all views which all come back with sub-second selects for the full data set.

Right, now for the actual query (sorry, it's a bit of a monster):

with h (distance, id, name, parentid, [path]) as ( 
select 
    0,
    pa.id,
    pa.name,
    null,
    convert(varchar(80), ltrim(str(pa.id))) as node_seq
from     
    pooling.practitioneraccount pa
    left outer join pooling.practitioneraccountrelation as par
        on pa.id = par.childid
where  
    pa.id = @PractitionerAccountId
    or (@PractitionerAccountId is null and par.parentid is null)

union all

select 
    1,
    -1,
    'Unmanaged Clients',
    -1,
    '-1'
where @practitioneraccountid is null

union all

select   
    distance + 1,
    pa.id,
    pa.name,
    par.parentid,
    convert(varchar(80), h.path + ',' + ltrim(str(pa.id)))
from     
    pooling.practitioneraccount pa
    inner join pooling.practitioneraccountrelation as par
        on pa.id = par.childid
    inner join h on par.parentid = h.id
)

select  
    distance as Depth,
    h.id as PractitionerAccountId,
    h.name as PractitionerAccountName,
    pafc.FullName as PractitionerAccountContactName,
    h.parentid as PractitionerAccountParentId,
    path as PractitionerAccountPath,
    ca.id as ClientAccountId,
    ca.name as ClientAccountName,
    ca.irdnumber as ClientAccountIrdNumber,
    bd.id as ClientAccountBalanceDateId,
    bd.day as ClientAccountBalanceDateDay,
    bd.month as ClientAccountBalanceDateMonth,
    cafc.FirstName as ClientAccountContactFirstName,
    cafc.LastName as ClientAccountContactLastName,
    cafc.PhoneNumber as ClientAccountContactPhoneNumber,
    cafc.MobileNumber as ClientAccountContactMobileNumber,
    ca.TownOrCity as ClientAccountLocation,
    rr.Rate as ClientAccountRwtRate,
    rr.Id as ClientAccountRwtRateId,
    cairg.RateGroupId as ClientAccountInheritedRateGroupId,
    cairg.RateGroupName as ClientAccountInheritedRateGroupName,
    ag.id as ClientAccountAssociatedGroupId,
    ag.name as ClientAccountAssociatedGroupName,
    agca.uniqueid as ClientAccountAssociatedGroupUniqueId,
    agca.approved as ClientAccountAssociatedGroupApproved
from    
    h
    left outer join security.practitioneraccountauthority as paa 
        on (h.id = paa.practitioneraccountid)
    left outer join pooling.clientaccount as ca 
        on ( 
            paa.clientaccountid = ca.id 
            or (h.id = -1 and ca.id not in (
                select clientaccountid 
                from security.practitioneraccountauthority
                )
            )
        )
left outer join config.RwtRate as rr  on ca.RwtRateId = rr.Id
left outer join config.balancedate as bd on (ca.balancedateid = bd.id)
left outer join pooling.ClientAccountInheritedRateGroup as cairg 
    on (ca.id = cairg.ClientAccountId)
left outer join security.associatedgroupclientaccount as agca 
    on (ca.id = agca.clientaccountid)
left outer join security.associatedgroup as ag 
    on (agca.associatedgroupid = ag.id)
left outer join pooling.ClientAccountFirstContact as cafc 
    on (cafc.ClientAccountId = ca.Id) 
left outer join pooling.PractitionerAccountFirstContact as pafc 
    on (pafc.PractitionerAccountId = paa.PractitionerAccountId)

The CTE in the query gives us the full tree of practitioners, which is then joined via the authority table to the clients under them. The CTE portion of the query returns almost instantly, only when we start joining to the clients does the performance issue appear.

I've added an index (and statistics) onto the ClientAccountId column of the authority table

CREATE NONCLUSTERED INDEX [IX_PractitionerAccountAuthority_ClientAccountId]
     ON     [security].[PractitionerAccountAuthority] 
(
    [ClientAccountId] ASC
)

Please ask if you need additional information such as the definition of the views mentioned above.

11
  • And here's the execution plan from a run of my query: dl.dropbox.com/u/2923715/ExecutionPlan.sqlplan
    – agrath
    Oct 18, 2012 at 4:35
  • I've been running the query for the full set of practitioners (3 mins) @PractitionerAccountId = null Running it for @PractitionerAccountId=71 (one of the 20) results in an 18 second execution time (878 rows)
    – agrath
    Oct 18, 2012 at 4:59
  • Historically some databases (eg around the time of Oracle 7.3) have had problems with the "not in" construct; if you rewrite it as a "not exists" construct does this help? Oct 18, 2012 at 7:37
  • Hi Colin, Changing the not in to a not exists (with a where clause inside the query) seemed to result in a 17 second (filtered) execution time, so comparable. On the large set, it performed much worse, around 8 mins. This is SQL Server 2008 btw.
    – agrath
    Oct 18, 2012 at 9:00
  • Throw the result of the the CTE into a temp table and then join it. Let us know how that goes.
    – wBob
    Oct 18, 2012 at 10:55

2 Answers 2

2

Here is your description of the problem: "The CTE portion of the query returns almost instantly, only when we start joining to the clients does the performance issue appear".

One possible, and likely, explanation is as follows: the optimizer fails to estimate the cardinality of your subtree, and chooses an inefficient plan. With your way of storing hierarchies, this is no surprise. How would you yourself estimate the size of a subtree without actually retrieving it?

Can you use materialized path? Getting a subtree using materialized path is essentially one range scan, fast and simple, and the optimizer can have a good cardinality estimate off the statistics on one index.

In my experience, your way of storing/reading hierarchies does not scale up. I have never been able to make it work fast and use resources efficiently.

1
  • Hi Alex, that's a valid point. The optimiser would not know how many rows the CTE would return until executing it, which is after the plan is generated. Would moving it to a temp table as suggested above help? If I used a real temp table (as opposed to a table variable), maybe even adding an index to it would be possible? I'm invoking this code from a tablar UDF, I am not sure I can create tables/indexes in one but I'm about to find out..
    – agrath
    Oct 18, 2012 at 19:57
0

It turned out to be this:

 left outer join pooling.clientaccount as ca 
        on ( 
            paa.clientaccountid = ca.id 
            or (h.id = -1 and ca.id not in (
                select clientaccountid 
                from security.practitioneraccountauthority
                )
            )
        )

Causing the major slowdown, because the on condition had multiple paths depending on the input data, it could not be optimised.

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.