2
\$\begingroup\$

This transform is designed to look at a set of 50k-100k employee records and produce a CSV file of anyone who has the same type of benefit enrollment more than once. Each employee may or may not have dependents.

More specifically, I'm looking for two things:

  1. Is there any employee who also appears as a dependent, and has the same kind of coverage?
  2. Is there any dependent who appears more than once, and has the same kind of coverage in more than one of those appearances?

Additionally, I have to actually capture each record. I can't simply count the number of times a given condition appears.

I think I've optimized my source dataset as much as I can. I've excluded any dependent who isn't currently enrolled in one of the coverage types I care about. However, I can't exclude any employees, because even if the employee himself isn't enrolled in any plans, that employee might have dependents who are potentially duplicated.

The transform currently takes almost an hour to run, and I'd like to squeeze anything I can out of it because this is a brand new database... it will only get bigger.

For reasons that I hope will become abundantly obvious, I cannot post my source XML. For what it's worth, the XML is organized like this:

<Report_Data>
    <Report_Entry>
        <Worker_Full_Name>(employee's full name)</Worker_Full_Name>
        <Emp_ID>(employees ID)</Emp_ID>
        <Worker_SSN>(employee's gov'nt ID)</Worker_SSN>
        <WDoB>(employee's date of birth)</WDoB>
        <WkrElections>
            <WkrBenPlan Descriptor="(some plan name)">
                <ID type="GUID">(some GUID)</ID>
                <ID type="Plan_ID">(some object ID)</ID>
            </WkrBenPlan>
            <WkrBenCovType Descriptor="(the 'type' for the above-mentioned plan)">
                <ID type="GUID">(some GUID)</ID>
                <ID type="Coverage_Type_ID">(some other object ID)</ID>
            </WkrBenCovType>
        </WkrElections>
        <Dep>
            <Dep_Name>(dependent's full name)</Dep_Name>
            <DDoB>(dependent's date of birth)</DDoB>
            <Dep_ID>(dependent's "employee" ID)</Dep_ID>
            <Dep_SSN>(dependent's gov'nt ID)</Dep_SSN>
            <Dep_Elections Descriptor="(the name and type of a benefit plan - it follows the form: [some other stuff]"Benefit Elections"[coverage type][a single hyphen][plan name]>
                <ID type="GUID">(some GUID)</ID>
            </Dep_Elections>
        </Dep>
    </Report_Entry>
</Report_Data>
  • There are many Report_Entry.
  • There can be none or many WkrElections which (if present) will contain exactly one pair of WkrBenPlan and WkrBenCovType.
  • There can be none or many Dep.
  • Each Dep will contain at least one or many Dep_Elections.
  • Each Dep_Elections/@Descriptor is a string that contains (among other things) the "type" of election that I'm interested in comparing to WkrBenCovType/@Descriptor
  • None of the GUIDs are useful.
  • Each Report_Entry may or may not contain a Worker_SSN.
  • Each Dep may or may not contain a Dep_SSN or DDoB.
  • Dep_ID and Emp_ID will never match, even for the same person.
  • The dataset is unsorted
  • I have some control over the structure of this XML (but not much), so if there are any ways I can modify the source XML to speed this up, it might be worth mentioning.

Now, regarding the transform, an employee is considered to "match" a dependent, and dependent is considered to "match" another dependent if:

  • Their SSNs are identical
  • One or both does not have an SSN, and their names and birthdates are both identical
  • Note: Birthdate formatting is controlled in both types of records, so it's safe to assume that any occurrence of a given date, will be a string of digits that is identical to any other occurrence of the same date.

Furthermore, from this set of matches, I only care about those matches for which the "type" of any given benefit election appears on both matching records. i.e. if an employee appears as a dependent, but the employee record has only a "Medical" election and the dependent record has only a "Dental" election, the records will be ignored.

My approach is (ostensibly) to find employees who are also dependents, first, and then find dependents who appear more than once. I look at each employee benefit election, comparing the employee to each dependent. If the employee has an SSN, I find any dependents with the same SSN. If the employee does not have an SSN, I look for any dependents with the same name and birthdate. Then I look at each of the benefit elections for each of the matching dependents, and if any dependent has the same type as the one employee election I'm looking at, I write out a record for that worker/election/dependent combination.

Then the process is similar for dependents. I look at each dependent election, comparing it to each dependent. If the dependent has an SSN, I find any who match, otherwise i match on name and birthdate. Then I look at each election for each matching dependent and write out records for each type that appears more than once.

It's not desirable to avoid finding each record twice (the first dependent matches the second, and then the second also matches the first), because neither record can be discovered from the other... they have to each be looked up individually in the database, to be corrected. Consequently, it's necessary to have a record for each case.

Now for the XSL. What follows is the culmination of my 3-week self-taught crash-course on XSL, so... please ask the stupid questions. I'm not 100% certain that you'll be able to run it as-is. I've had to obfuscate some things, and I don't really have a way to test the results...

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:ns="(some namespace)"
    exclude-result-prefixes="xs" version="2.0">
    <xsl:output method="text" omit-xml-declaration="yes"/>
    <xsl:variable name="delimiter" select="','"/>
    <xsl:variable name="linefeed" select="'&#xA;'"/>

    <xsl:template match="/Report_Data">
        <!-- Header Row Begins -->
        <xsl:text>Discrepancy,Worker Name,Worker DoB,Worker UIN,Worker SSN,Dependent Name,Dependent DoB,Dependent SSN,Dependent ID,Coverage type,Worker's Plan,Dependent's Plan</xsl:text>
        <xsl:value-of select="$linefeed"/>
        <!-- Data Row Begins -->
            <!-- One type of record we care about are those Workers who are also dependents, and are covered under the same coverage type both as a worker and as a dependent -->
            <!-- Look at each Election for each Worker, beginning with the Coverage Type for that election -->
            <xsl:for-each select="Report_Entry/WkrElections/WkrBenCovType">
                <!-- Collect a few piece of information that we'll need, if we find a Dependent that matches this Worker -->
                <xsl:variable name="wkrSSN" select="current()/../../Worker_SSN"/>
                <xsl:variable name="wkrName" select="current()/../../Worker_Full_Name"/>
                <xsl:variable name="wkrBD" select="current()/../../WDoB"/>
                <xsl:variable name="wkrCovType" select="."/>
                <xsl:choose>
                    <xsl:when test="current()/../../Worker_SSN/text()">
                        <!-- Find any Dependent whose SSN contains our Worker's SSN -->
                        <xsl:variable name="dependentWorkers" select="/*/*/Dep[contains(Dep_SSN, $wkrSSN)]" />
                        <!-- From each of those Dependents, select all those whose Dependent Election Descriptor contains the same Coverage Type as our Worker's Coverage Type -->
                        <xsl:for-each select="$dependentWorkers/Dep_Elections[contains(./@Descriptor,$wkrCovType/@Descriptor)]" >
                            <!-- For all those Dependents who have the same SSN and coverage type as our worker, print out some useful information -->
                            <xsl:text>Worker also covered as Dependent , </xsl:text>                <!-- Discrepancy-->
                            <xsl:value-of select="$wkrSSN/../Worker_Full_Name"/>                 <!-- Worker's Name -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="$wkrSSN/../WDoB"/>                             <!-- Worker's DoB -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="$wkrSSN/../UIN"/>                              <!-- Worker's UIN -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="$wkrSSN"/>                                        <!-- Worker's SSN -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="../Dep_Name"/>                                 <!-- Dependent's Name -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="../DDoB"/>                                     <!-- Dependent's DoB -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="../Dep_SSN"/>                                  <!-- Dependent's SSN -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="../Dep_ID"/>                                   <!-- Dependent's ID -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="$wkrCovType/@Descriptor"/>                     <!-- Coverage Type -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="$wkrCovType/../WkrBenPlan/@Descriptor"/>    <!-- Worker's Benefit Plan -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="./@Descriptor"/>                               <!-- Dependent's Benefit Plan -->
                            <xsl:value-of select="$linefeed"/>
                        </xsl:for-each>
                    </xsl:when>
                    <xsl:otherwise>
                        <!-- For any Worker without an SSN, find any Dependents whose name and birthdate matches our worker -->
                        <xsl:variable name="dependentWorkers" select="/*/*/Dep[contains(DDoB, $wkrBD) and contains(Dep_Name, $wkrName)]" />
                        <!-- From each of those Dependents, select all those whose Dependent Election Descriptor contains the same Coverage Type as our Worker's Coverage Type -->
                        <xsl:for-each select="$dependentWorkers/Dep_Elections[contains(./@Descriptor,$wkrCovType/@Descriptor)]" >
                            <!-- For all those Dependents who have the same SSN and coverage type as our worker, print out some useful information -->
                            <xsl:text>Worker also covered as Dependent , </xsl:text>                <!-- Discrepancy-->
                            <xsl:value-of select="$wkrSSN/../Worker_Full_Name"/>                 <!-- Worker's Name -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="$wkrSSN/../WDoB"/>                             <!-- Worker's DoB -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="$wkrSSN/../UIN"/>                              <!-- Worker's UIN -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="$wkrSSN"/>                                        <!-- Worker's SSN -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="../Dep_Name"/>                                 <!-- Dependent's Name -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="../DDoB"/>                                     <!-- Dependent's DoB -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="../Dep_SSN"/>                                  <!-- Dependent's SSN -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="../Dep_ID"/>                                   <!-- Dependent's ID -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="$wkrCovType/@Descriptor"/>                     <!-- Coverage Type -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="$wkrCovType/../WkrBenPlan/@Descriptor"/>    <!-- Worker's Benefit Plan -->
                            <xsl:value-of select="$delimiter"/>
                            <xsl:value-of select="./@Descriptor"/>                               <!-- Dependent's Benefit Plan -->
                            <xsl:value-of select="$linefeed"/>
                        </xsl:for-each>
                    </xsl:otherwise>
                </xsl:choose>
            </xsl:for-each>

        <!-- Another type of records we care about are those dependents who both share an SSN and Coverage Type for one of their elections-->
        <!-- Look at each Election for each Dependent -->
        <xsl:for-each select="Report_Entry/Dep">

            <!-- These nested loops get pretty deep... save this top-level node for later -->
            <xsl:variable name="thisNode" select="current()"/>

                    <!-- "thisNode" shares an SSN with another record somewhere.
                    We need to look at each of thisNode's Benefit Election Types to see if the other record has any matching types-->
                    <xsl:for-each select="$thisNode/Dep_Elections">

                        <!-- Dependents don't have a Coverage Type object like workers do
                        So we have to parse the type for each Election out of its plan name -->
                        <xsl:variable name="type1" select="substring-after(current()/@Descriptor,'Benefit Elections')"/>
                        <xsl:variable name="type2" select="substring-after($type1,'(')"/>
                        <xsl:variable name="coverageType" select="substring-before($type2,' -')"/>

                        <xsl:choose>
                            <xsl:when test="$thisNode/Dep_SSN/text() and false()">
                                <!-- Find any Dependent whose SSN contains our Worker's SSN -->
                                <xsl:variable name="duplicateDependents" select="/*/*/Dep[contains(Dep_SSN, $thisNode/Dep_SSN) and (generate-id(.) != generate-id($thisNode))]" />
                                <xsl:for-each select="$duplicateDependents/Dep_Elections">

                                    <!-- Again, we have to parse the type from the plan name of each Election, to see if it matches 
                                    the one election that we're looking at for thisNode, right now -->
                                    <xsl:variable name="secondType1" select="substring-after(current()/@Descriptor,'Benefit Elections')"/>
                                    <xsl:variable name="secondType2" select="substring-after($secondType1,'(')"/>
                                    <xsl:variable name="secondCoverageType" select="substring-before($secondType2,' -')"/>

                                    <!-- Only continue if the Coverage Type we parsed out of thisNode matches the Coverage Type we just parsed out of this other dependent -->
                                    <xsl:if test="contains($secondCoverageType,$coverageType)" >
                                        <!-- If they match, print some useful information -->
                                        <xsl:text>Dependent Covered Twice, </xsl:text>              <!-- Discrepancy-->
                                        <xsl:value-of select="$thisNode/../Worker_Full_Name"/>   <!-- Worker's Name (the one covering this dependent) -->
                                        <xsl:value-of select="$delimiter"/>                         
                                        <xsl:value-of select="$thisNode/../WDoB"/>               <!-- Worker's DoB -->
                                        <xsl:value-of select="$delimiter"/>                         
                                        <xsl:value-of select="$thisNode/../UIN"/>                <!-- Worker's UIN -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:value-of select="$thisNode/../Worker_SSN"/>         <!-- Worker's SSN -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:value-of select="../Dep_Name"/>                     <!-- Dependent's Name -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:value-of select="../DDoB"/>                         <!-- Dependent's DoB -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:value-of select="../Dep_SSN"/>                      <!-- Dependent's SSN -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:value-of select="../Dep_ID"/>                       <!-- Dependent's ID -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:value-of select="$coverageType"/>                      <!-- Coverage Type -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:text>n|a , </xsl:text> <!-- In this case, there is no worker, just two dependents -->
                                        <xsl:value-of select="./@Descriptor"/>                   <!-- Dependent's Benefit Plan -->
                                        <xsl:value-of select="$linefeed"/>
                                    </xsl:if>
                                </xsl:for-each>    
                            </xsl:when>
                            <xsl:when test="($thisNode/Dep_SSN[not(node())] or $thisNode[not(Dep_SSN)]) and $thisNode/DDoB/text()">
                                <!-- For any Worker without an SSN, find any Dependents whose name and birthdate matches our worker -->
                                <xsl:variable name="duplicateDependents" select="/*/*/Dep[(contains(./Dep_Name, $thisNode/Dep_Name) and contains(./DDoB, $thisNode/DDoB)) and (generate-id(.) != generate-id($thisNode))]" />
                                <xsl:for-each select="$duplicateDependents/Dep_Elections">

                                    <!-- Again, we have to parse the type from the plan name of each Election, to see if it matches 
                                    the one election that we're looking at for thisNode, right now -->
                                    <xsl:variable name="secondType1" select="substring-after(current()/@Descriptor,'Benefit Elections')"/>
                                    <xsl:variable name="secondType2" select="substring-after($secondType1,'(')"/>
                                    <xsl:variable name="secondCoverageType" select="substring-before($secondType2,' -')"/>

                                    <!-- Only continue if the Coverage Type we parsed out of thisNode matches the Coverage Type we just parsed out of this other dependent -->
                                    <xsl:if test="contains($secondCoverageType,$coverageType)" >
                                        <!-- If they match, print some useful information -->
                                        <xsl:text>Dependent Covered Twice, </xsl:text>              <!-- Discrepancy-->
                                        <xsl:value-of select="$thisNode/../Worker_Full_Name"/>   <!-- Worker's Name (the one covering this dependent) -->
                                        <xsl:value-of select="$delimiter"/>                         
                                        <xsl:value-of select="$thisNode/../WDoB"/>               <!-- Worker's DoB -->
                                        <xsl:value-of select="$delimiter"/>                         
                                        <xsl:value-of select="$thisNode/../UIN"/>                <!-- Worker's UIN -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:value-of select="$thisNode/../Worker_SSN"/>         <!-- Worker's SSN -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:value-of select="../Dep_Name"/>                     <!-- Dependent's Name -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:value-of select="../DDoB"/>                         <!-- Dependent's DoB -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:value-of select="../Dep_SSN"/>                      <!-- Dependent's SSN -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:value-of select="../Dep_ID"/>                       <!-- Dependent's ID -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:value-of select="$coverageType"/>                      <!-- Coverage Type -->
                                        <xsl:value-of select="$delimiter"/>
                                        <xsl:text>n|a , </xsl:text> <!-- In this case, there is no worker, just two dependents -->
                                        <xsl:value-of select="./@Descriptor"/>                   <!-- Dependent's Benefit Plan -->
                                        <xsl:value-of select="$linefeed"/>
                                    </xsl:if>
                                </xsl:for-each>    
                            </xsl:when>
                            <xsl:otherwise>
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:for-each>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

So far, I've attempted breaking the very complicated duplicateDependents and dependentWorkers selection criteria into separate variables (select all the same name, then from that select all the same DoB, then from that variable, select all of them that don't generate the same ID). But that was 20% slower. I've also tried to move some of the if statements into the XPath selection for the "for-each" statements, but that's been the same or slower.

\$\endgroup\$
  • 1
    \$\begingroup\$ I'm pretty sure that <DDoB> does not contain the (Dependent's full name), but their Date of Birth ;) \$\endgroup\$ – Vogel612 Jul 24 '17 at 22:19
  • \$\begingroup\$ Lol, you might think that at first glace... and you'd be correct ;-) \$\endgroup\$ – Matt Jul 25 '17 at 14:59
  • \$\begingroup\$ ...and this morning, my data set just broke 120k records. \$\endgroup\$ – Matt Jul 25 '17 at 15:13
  • \$\begingroup\$ Without a Minimal, Complete, and Verifiable example it's difficult to suggest changes that may or may not improve performance. If you had an input that had just one example of each of the possible conditions (with private information changed of course), along with an XSLT that produces the desired output, we could run it ourselves and make suggestions. Also, what XSLT 2.0 processor are you using? If you're using Saxon, consider performing performance analysis on your transform. \$\endgroup\$ – Daniel Haley Jul 26 '17 at 17:46

Your Answer

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

Browse other questions tagged or ask your own question.