Querying on a string attribute: equals vs contains
Hi All, I noticed something strange with a FIM query, related to a (custom) string attribute. This attribute is called "AccountType", and can have only certain values: Primary, Secondary and Service. If I run (from PowerShell, from the public web service client, does not matter) a query like "/Person[AccountType='Primary']" the query is very slow (takes ~45 secs), while if I run "/Person[contains(AccountType,'Primary')]" the query is extremely fast (~0.6 secs). This does not happen on every attribute - for example, if I filter on AccountName in one way or the other it's the same. I would like to understand why there is such a big difference in the two cases. Has any of you observed a similar behavior? Thanks, PaoloPaolo Tedesco - http://cern.ch/idm
December 16th, 2010 7:17am

Do both queries return the same result set? I'm fairly new to XPATH, but I had gotten the impression that doing "contains" searches wouldn't actually work, at least in the situations I have tried them (i.e. Event Log filtering).
Free Windows Admin Tool Kit Click here and download it now
December 17th, 2010 3:06pm

Hi Chris, Well, the results are the same in my particular case, but the two functions are obviously different. Equals returns exact matches ("/Person[AccountName='aaa']" returns only "aaa") while contains returns objects that match on the beginning or end of the string ("/Person[contains(AccountName,'aa')]" would return "xaa" and "aax" - but not "xaax"). What have you tried with 'contains' that didn't give the expected result? Cheers, PaoloPaolo Tedesco - http://cern.ch/idm
December 20th, 2010 4:36am

I wasn't using PowerShell, but rather trying to build a filter in the Server 2008 event logs. Following examples I had seen on-line, I got no results at all and I suspect the syntax was wrong. I had seen references in other posts here regarding "contains" searches but I couldn't find them when I went to reply to your post. Sorry for the unintended red herring.
Free Windows Admin Tool Kit Click here and download it now
December 20th, 2010 9:21am

Paolo, I have 'contains' queries running slower than equals (as expected) PS C:\> measure-command {Export-FIMConfig -uri "http://localhost:5725/resourcemanagementservice" -onlyBaseResources -customconfig "(/Role[RoleName='DREAM Mapping'])"} Days : 0 Hours : 0 Minutes : 0 Seconds : 0 Milliseconds : 79 Ticks : 794609 TotalDays : 9.19686342592593E-07 TotalHours : 2.20724722222222E-05 TotalMinutes : 0.00132434833333333 TotalSeconds : 0.0794609 TotalMilliseconds : 79.4609 PS C:\> measure-command {Export-FIMConfig -uri "http://localhost:5725/resourcemanagementservice" -onlyBaseResources -customconfig "(/Role[contains(RoleName,'DREAM Mapping')])"} Days : 0 Hours : 0 Minutes : 0 Seconds : 0 Milliseconds : 106 Ticks : 1063244 TotalDays : 1.23060648148148E-06 TotalHours : 2.95345555555556E-05 TotalMinutes : 0.00177207333333333 TotalSeconds : 0.1063244 TotalMilliseconds : 106.3244 the first run usually takes 300 milliseconds for 'equal' and second and further runs took ~80ms the first run for 'contains' took 380 milliseconds and further runs - ~100ms
December 20th, 2010 9:37am

Paolo, I have 'contains' queries running slower than equals (as expected) PS C:\> measure-command {Export-FIMConfig -uri "http://localhost:5725/resourcemanagementservice" -onlyBaseResources -customconfig "(/Role[RoleName='DREAM Mapping'])"} Days : 0 Hours : 0 Minutes : 0 Seconds : 0 Milliseconds : 79 Ticks : 794609 TotalDays : 9.19686342592593E-07 TotalHours : 2.20724722222222E-05 TotalMinutes : 0.00132434833333333 TotalSeconds : 0.0794609 TotalMilliseconds : 79.4609 PS C:\> measure-command {Export-FIMConfig -uri "http://localhost:5725/resourcemanagementservice" -onlyBaseResources -customconfig "(/Role[contains(RoleName,'DREAM Mapping')])"} Days : 0 Hours : 0 Minutes : 0 Seconds : 0 Milliseconds : 106 Ticks : 1063244 TotalDays : 1.23060648148148E-06 TotalHours : 2.95345555555556E-05 TotalMinutes : 0.00177207333333333 TotalSeconds : 0.1063244 TotalMilliseconds : 106.3244 the first run usually takes 300 milliseconds for 'equal' and second and further runs took ~80ms the first run for 'contains' took 380 milliseconds and further runs - ~100ms
Free Windows Admin Tool Kit Click here and download it now
December 20th, 2010 9:37am

I'm wondering - given it's a custom attribute - that perhaps you have lots of /Person objects with null values in this string? Have you tried running the database re-index job since adding the attribute? Sounds to me like the SQL statistics are somehow forcing the underlying query down the wrong access path ... may well revert to the sort of performance Evgeniy is getting after you simply do this. Also, where you have a binding that you want to always have one of the 3 values you suggest, then you will want to pre-populate this value with a default value for all existing users so you can set the REQUIRED checkbox on the binding (if you haven't already done so - presuming you have a REGEX for the 3 values?). Just a hunch, but once there's only valid data in this field (and db indexing is done) you may also get more sensible results ...Bob Bradley, www.unifysolutions.net (FIMBob?)
December 20th, 2010 9:59am

I'm wondering - given it's a custom attribute - that perhaps you have lots of /Person objects with null values in this string? Have you tried running the database re-index job since adding the attribute? Sounds to me like the SQL statistics are somehow forcing the underlying query down the wrong access path ... may well revert to the sort of performance Evgeniy is getting after you simply do this. Also, where you have a binding that you want to always have one of the 3 values you suggest, then you will want to pre-populate this value with a default value for all existing users so you can set the REQUIRED checkbox on the binding (if you haven't already done so - presuming you have a REGEX for the 3 values?). Just a hunch, but once there's only valid data in this field (and db indexing is done) you may also get more sensible results ...Bob Bradley, www.unifysolutions.net (FIMBob?)
Free Windows Admin Tool Kit Click here and download it now
December 20th, 2010 9:59am

the answer will be seen in a SQL monitor for such a long queries (45 secs) and a plan for both queries.
December 20th, 2010 10:11am

the answer will be seen in a SQL monitor for such a long queries (45 secs) and a plan for both queries.
Free Windows Admin Tool Kit Click here and download it now
December 20th, 2010 10:11am

Paolo, I have 'contains' queries running slower than equals (as expected) PS C:\> measure-command {Export-FIMConfig -uri "http://localhost:5725/resourcemanagementservice" -onlyBaseResources -customconfig "(/Role[RoleName='DREAM Mapping'])"} Days : 0 Hours : 0 Minutes : 0 Seconds : 0 Milliseconds : 79 Ticks : 794609 TotalDays : 9.19686342592593E-07 TotalHours : 2.20724722222222E-05 TotalMinutes : 0.00132434833333333 TotalSeconds : 0.0794609 TotalMilliseconds : 79.4609 PS C:\> measure-command {Export-FIMConfig -uri "http://localhost:5725/resourcemanagementservice" -onlyBaseResources -customconfig "(/Role[contains(RoleName,'DREAM Mapping')])"} Days : 0 Hours : 0 Minutes : 0 Seconds : 0 Milliseconds : 106 Ticks : 1063244 TotalDays : 1.23060648148148E-06 TotalHours : 2.95345555555556E-05 TotalMinutes : 0.00177207333333333 TotalSeconds : 0.1063244 TotalMilliseconds : 106.3244 the first run usually takes 300 milliseconds for 'equal' and second and further runs took ~80ms the first run for 'contains' took 380 milliseconds and further runs - ~100ms
December 20th, 2010 5:31pm

the answer will be seen in a SQL monitor for such a long queries (45 secs) and a plan for both queries.
Free Windows Admin Tool Kit Click here and download it now
December 20th, 2010 6:05pm

Equals returns exact matches ("/Person[AccountName='aaa']" returns only "aaa") while contains returns objects that match on the beginning or end of the string ("/Person[contains(AccountName,'aa')]" would return "xaa" and "aax" - but not "xaax"). I've been revisiting the definition of the "contains" function (under Function Calls) and there appears to be no indication anywhere backing up the above statement. Does that mean that the above is an "undocumented feature" of contains? :) For anyone who has hit the above problem, I've found the following work-around gives a match on the missing xaax example above: /Person[starts-with(AccountName,'%aa')] I'm not sure if support for the above SQL wildcard syntax is intentional, but it works ... Bob Bradley, www.unifysolutions.net (FIMBob?)
April 19th, 2011 9:08pm

Bob, "contains" actually tries to find a word or part of the phrase in the list of words returned by SQL word braker. and I believe this is the reason for having full text indexing on FIM db. so, if you will search for "expi" in the displayName on all Request objects you may find something like this: "Membership update for temporal set expiring group resources".
Free Windows Admin Tool Kit Click here and download it now
April 19th, 2011 9:39pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics