0

Table A has approximately 500 million records in it and is of the form similar to the following

| ID | FirstName | LastName | MiddleName |
------------------------------------------
|  1 |      John |      Doe |     Quincy |

Et cetera. ID is unique. Table A also has a clustered columnstore index on it.

Table B has approximately 5 billion records and is of the form similar to the following

| ID | Age | Gender |     Cousin |
----------------------------------
|  1 |  26 |      0 |     Carlos |
|  1 |   . |      0 |     Thomas |
|  1 |   . |      0 |     Andrew |
|  2 |   . |      0 |  Gabrielle |
|  2 |   . |      0 |    Douglas |
|  2 |   . |      0 |    Anthony |
|  2 |   . |      0 |      Caleb |
|  3 |   . |      0 |      Scott |
|  3 |  32 |      0 |     Jordan |

Et cetera. ID on Table B joins to ID on Table A and Table B also has a clustered columnstore index on it.

The joins and queries are pretty quick. I can join the two tables and including filters on them can yield approximately ~1.1 million records which get inserted into a temp table.

However; my end result requires that I pivot the data so that record of ID 1 is a single record, like such

| ID | FirstName | LastName | MiddleName | Age | Gender | Cousins
-----------------------------------------------------------------------------------------------
|  1 |      John |      Doe |     Quincy |  26 |      0 | Carlos, Thomas, Andrew              |
|  2 |  Person 2 | Person 2 |   Person 2 |   . |      0 | Gabrielle, Douglass, Anthony, Caleb |

I can achieve this with a cross apply like so

SELECT ID, FirstName, LastName, MiddleName, Age, Gender, r.ColumnName
FROM #tempTable
CROSS APPLY (
    SELECT r.ColumnName + ', '
    FROM #tempTable r
    WHERE e.ActionDateID = r.ActionDateID AND e.RecordNumberID = r.RecordNumberID
    FOR XML PATH('')
) r (ColumnName)

But the performance is awful. Especially when #tempTable has approximately ~1.1 million records. Has anyone a means of getting the same results but with better execution times? For this example, the cross apply takes ~35 seconds.

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.