Improve efficiency of DirectLink / Tree work item queries

[rjmurillo]

Setup

Given a work item structure

• Deliverable { Id = 1 }
  • Task { Id = 2 }
  • Bug { Id = 3 }

Problem

The existing implementation:

var deliverablesTasks = vsoContext.Deliverables.Where(p=>p.Id == 1).Children<Deliverable, Task>();
var deliverablesBugs = vsoContext.Deliverables.Where(p=>p.Id == 1).Children<Deliverable, Bug>();
...

Would need to perform the following:

1. Query on WorkItems where WIT = Deliverable and Id = 1
   SELECT * FROM WorkItems WHERE [System.WorkItemType] = 'Deliverable' AND [System.Id] = 1
2. Map the resulting Deliverable and fields
3. Query WorkItemLinks to get the Task children
   SELECT * FROM WorkItemsLinks WHERE Source.[System.WorkItemType] = 'Deliverable' AND Source.[System.Id] = 1 AND [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward' AND Target.[System.WorkItemType] = 'Task'
4. Query on WorkItems where Id=2
   SELECT * FROM WorkItems WHERE [System.Id] IN (2)
5. Map the resulting Task and fields
6. Query on WorkItems where WIT = Deliverable and Id = 1 (repeat of 1)
   SELECT * FROM WorkItems WHERE [System.WorkItemType] = 'Deliverable' AND [System.Id] = 1
7. Map the resulting Deliverable and fields (repeat of 2)
8. Query WorkItemLinks to get the Bug children
   SELECT * FROM WorkItemsLinks WHERE Source.[System.WorkItemType] = 'Deliverable' AND Source.[System.Id] = 1 AND [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward' AND Target.[System.WorkItemType] = 'Bug'
9. Query on WorkItems where Id=3
   SELECT * FROM WorkItems WHERE [System.Id] IN (3)
10. Map the resulting Bug and fields

The operation requires six trips to VSO in order to hydrate the items.

Proposal

When performing a DirectLink (e.g. Parent / Child, Child / Parent), or a Tree (Parent / Child / Grandchild), use

1. **IWorkItemStore.QueryLinks method to first query for all link relationships, **
   SELECT * FROM WorkItemsLinks WHERE Source.[System.WorkItemType] = 'Deliverable' AND Source.[System.Id] = 1 AND [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward' AND (Target.[System.WorkItemType] = 'Task' OR Target.[System.WorkItemType] = 'Bug')
2. Load all work items by ID
   SELECT * FROM WorkItems WHERE [System.Id] IN (1, 2, 3)
3. Map the resulting work item types

The call to IWorkItemStore.QueryLinks returns IEnumerable<IWorkItemLinkInfo>, which would house the following results given our scenario.

SourceId	TargetId	LinkTypeId	IsLocked
0	1	0	false
1	2	2	false
1	3	2	false

The results represent the tree having one level of children (additional levels set the SourceId and TargetId accordingly). The fields are then mapped using a 'flat' TFS query of regular IDs, which is inherently fast.

Note: this works for DirectLink and Tree when no ASOF is requested. When ASOF is requested, only Flat and DirectLink queries are supported.

http://blogs.msdn.com/b/jsocha/archive/2012/02/22/retrieving-tfs-results-from-a-tree-query.aspx

[rjmurillo]

TL;DR _642 work items queried and mapped in under 30 seconds_

Loading 642 work items queried in two trips to VSO.

• Trip one queried the links, returning the IDs for all elements in a little over 2 seconds
• Trip two queried all the fields for the IDs, taking just under 2 seconds.

Once the IWorkItem objects are returned, they must be mapped. I used the identity aware attribute mapper (the default), and spent over _25 seconds_ in the mapper.

Here's the WIQL output with timings

[
   [ Elapsed(ms) : 2049 ]
   [ Invocation count : 1 ]
   [ wiql : 
SELECT
    [OSG.Rank],
    [System.Id],
    [OSG.Type],
    [System.Title],
    [System.IterationPath],
    [System.AreaPath],
    [System.AssignedTo],
    [System.State],
    [Microsoft.VSTS.Common.CustomString01],
    [Microsoft.VSTS.Common.CustomString02],
    [Microsoft.VSTS.Common.CustomString03],
    [Microsoft.VSTS.Common.CustomString07],
    [Microsoft.VSTS.Common.CustomString08],
    [Microsoft.VSTS.Common.CustomString09]
FROM WorkItemLinks
WHERE   (
            [Source].[System.TeamProject] = 'OS'
            AND [Source].[System.AreaPath] UNDER 'OS\CORE-OS Core\WPT-Web Platform'
            AND [Source].[System.WorkItemType] = 'Scenario'
            AND [Source].[System.State] IN ('Proposed', 'Approved', 'Active', 'Committed')
            AND [Source].[System.Tags] CONTAINS 'Epic'
        )
        AND ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward')
        AND (
                [Target].[System.TeamProject] = 'OS'
                AND [Target].[System.AreaPath] UNDER 'OS\CORE-OS Core\WPT-Web Platform'
                AND [Target].[System.WorkItemType] IN ('Scenario', 'Deliverable')
            )
ORDER BY [OSG.Rank]
mode(Recursive)
 ]
   [ dayPrecision : False ]
   [ Parameter Hash : Hash: -905229164 ]
]

[
   [ Elapsed(ms) : 1929 ]
   [ Invocation count : 1 ]
   [ wiql : 
SELECT
    [OSG.Rank],
    [System.Id],
    [OSG.Type],
    [System.Title],
    [System.IterationPath],
    [System.AreaPath],
    [System.AssignedTo],
    [System.State],
    [Microsoft.VSTS.Common.CustomString01],
    [Microsoft.VSTS.Common.CustomString02],
    [Microsoft.VSTS.Common.CustomString03],
    [Microsoft.VSTS.Common.CustomString07],
    [Microsoft.VSTS.Common.CustomString08],
    [Microsoft.VSTS.Common.CustomString09]
FROM WorkItems
WHERE
    [System.TeamProject] = 'OS'
    AND [System.Id] IN (4915051,3249369,3249374,4885780,1856639,4885855,4885866,3163703,2503708,3297572,1105553,3297583,1805641,3145747,3163673,3163666,4878090,4878182,4878189,4878199,4394143,124918,3760426,150445,3249242,3167315,3249286,3840615,5004927,1878064,4915234,4984492,4915065,4913181,4497094,5025201,4463622,2916413,4497096,3026362,3026458,3026618,3026640,3026645,3026648,3401619,3371164,3264996,3276949,3086037,3401318,3401388,3401277,3084812,4763804,4880248,2762460,2762626,4894163,4884365,1093518,4855662,4893302,3399021,4911932,5002779,4892667,4893490,4893701,151044,2916771,4893221,4893233,4893247,4893292,4893316,4893323,4893338,4893354,4893361,4893383,4893388,4893399,4893412,4893425,4893435,4893441,4893509,4893534,4893543,4893546,4893571,4893579,4893587,4893609,4893662,4893723,4893740,578938,1176600,3370301,150998,3038949,3050593,4892708,4893208,3038779,4894848,4498284,3382551,3382566,4892841,4893693,3376968,624236,3038941,3038946,3038950,4915218,2312281,157335,3413419,157016,3242692,608732,4859701,163171,163556,4859745,163566,609289,163576,163205,931596,4972329,4972614,4972632,4972678,4915213,4892514,694704,4032817,712136,263215,4897994,4898028,4897990,156491,3668478,578935,3286925,3089484,4892737,4892679,4898810,5029170,4956934,4906149,4956931,4892684,4893499,2831085,4627747,4628342,4629831,4632087,4632339,3524101,4757963,4957540,4896501,4915155,4896921,3067672,2791075,3067479,2505442,751122,129836,659667,3161032,694659,241828,4897559,3135398,160653,263738,263758,3286880,156464,3172521,147310,156326,4982721,124015,4896770,4896805,4896783,4896811,4897966,288529,1705244,3664675,725424,4898065,157627,4897891,4897899,160782,4874240,3292411,241980,4898278,156357,135432,160454,160988,165706,157659,157765,157409,4897110,682646,266357,124048,156552,156277,156632,4898542,4907370,5029146,5029173,4893004,4897169,4233611,4233683,1314656,3792483,1314724,659665,161022,306417,711791,203291,1505048,694992,164078,3067532,2085243,603477,155284,161794,4906262,3067422,2520914,3060672,3060681,3060408,3060485,3060439,3060450,4302764,4302786,4912242,4760077,4760348,4760630,4548233,3412325,4614148,4548284,4695059,4548513,4548522,4548525,4912738,4824910,4824945,4825267,4825244,4913824,4548620,3412323,4815991,4117812,4117981,4118043,4118084,4118173,4118286,4118359,4118412,4118459,4118689,4118729,4118783,4118863,4118924,4119036,4119112,4119316,4119348,4548803,4911125,4911145,3429101,3429118,4410869,4728262,4728380,4825766,4777765,4618079,4827311,3412324,3412312,3412314,4442848,4861585,4892130,4892165,3412319,3412320,3125125,4912374,4912390,4912279,655611,3475645,3475646,3475647,3475648,3475649,3475816,4412532,3285343,4912573,4120989,3125115,655615,3125103,721182,673792,163077,3039265,3948457,990014,335292,4644706,164230,3039674,3038802,3055176,2957621,2939894,2940128,2942283,3373727,164217,4646651,164213,2940196,3038801,3038803,3039266,3039587,3040737,3055603,3056698,3039006,3039007,3039008,3039009,3039010,2958583,2958604,2958907,164199,163019,669624,4898752,3224989,4715251,129708,1494337,4898282,4932986,4856911,4125446,4125481,4125532,4112633,4125618,4860084,4896558,3212947,669633,669635,669636,318499,3056131,3412301,3412302,3412303,3412304,3690622,3690724,3814404,3412305,3412306,3412307,3412308,3412309,3412310,651451,123608,154523,162662,4824900,4913285,4913737,4914004,4824950,4824959,4892662,4878223,4728535,4878241,4895551,3412315,3412316,3412317,3412318,4825178,4825261,4825270,4825290,4827236,4827260,4827262,4827265,4827270,4827279,4312051,4483846,4484157,4484192,4484194,4859676,127893,719764,163564,755030,1034838,4786863,4786864,4916155,1191580,3172082,2000562,1310220,1262854,1329892,3651638,4467094,4912453,4932283,4912281,4912365,4581578,4915083,4894295,5004364,4895717,4983528,4983536,4895026,4983461,4895047,4983017,4983102,3134728,3083381,3084132,1747442,1480250,3134738,3083527,5026083,4894942,1302829,4103961,3708976,4104096,3285501,3134844,3382531,4898637,4905861,4895181,3134731,3083342,4895015,4895644,4896192,4895363,324529,900516,900525,4915058,3283093,3169044,3067549,3074273,3074300,2569555,3060536,3060581,659659,165704,157568,3067378,2082513,4896124,4821620,4896093,4896129,4896130,643943,643945,4915148,4896816,3039066,4116349,4898694,5025656,4233618,4233658,4897638,4907332,4898508,4907348,659655,659570,2930513,157431,694778,4706544,814660,4897573,4907281,4907309,711826,586460,1389022,1388994,727805,626119,2279286,2279339,2222831,3213706,4871999,4915047,3301061,3690741,4062943,4892896,4893164,3068179,2280692,4980345,4984482,3429265,3429285,3316740,3317754,4267214,4394210,124418,4915041,4899428,4899429,4899430,5002862,4853464,4853859,4853915,4853944,4927115,3414109,3082757,3083489,3088986,3171846,3152052,4980361,3253451,3253480,3239747,3240025,4451613,4853299,4853772,4853368,4853660,4853675,4853688,4855184,4892604,4935075,4892757,3038947,4896681,4897172,4897279,4897305,4897353,4897377,4897406,4897445,4897471,4897488,4896836,4980468,3283026,750507,4982831,4980535,4995936,5002841,5002609,4864709,4915586,5003249,155028,5003269,3708300,4907268,4898815,3214051,3214063)
ORDER BY [OSG.Rank]
 ]
   [ dayPrecision : False ]
   [ Parameter Hash : Hash: -23899885 ]
]

/cc @MattKotsenas @pelavall