0

I want to join a column that has multiple values, and join them as comma separated list values into a single field. The SQL query works, however I think I run into performance issues.

After 30 minutes of loading my SQL code it gives timeout problems: DataSource.Error: Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Whenever I select top 1000 rows, it still loads for an entire minute.

The LEDGERTRANS.TXT is a string, and also with MAX() surrounding it, it gives me a timeout.

MRPRJPROJTRANSPOSTING has 2,5M rows LEDGERTRANS +/- 5M rows, though I only join what I need from it.

The following code is what I use:

SELECT 
MRPRJPROJTRANSPOSTING.VOUCHER,
[Factuuromschrijving(en)] = STUFF(
    (SELECT ',' + LEDGERTRANS.TXT 
        FROM LEDGERTRANS
        WHERE LEDGERTRANS.VOUCHER = MRPRJPROJTRANSPOSTING.VOUCHER 
AND LEDGERTRANS.DATAAREAID = MRPRJPROJTRANSPOSTING.DATAAREAID FOR 
XML PATH (''))
        , 1, 1, '' )
FROM MRPRJPROJTRANSPOSTING

Is there maybe another way to make a similar join?

Kind regards, Igor

titatovenaar
  • 309
  • 4
  • 12
  • Is upgrading to SQL 2017 an option? - SQL finally has [STRING_AGG](https://stackoverflow.com/a/40692104/314291). With the STUFF hack above, the best you can really do is ensure that the JOIN columns are all indexed as best possible. Otherwise, if a coded solution is an option, then you may try and just join the two tables together, select the columns needed, and then do the [String concatenation in memory](https://stackoverflow.com/a/30045441/314291) – StuartLC Jun 17 '19 at 12:40
  • There is no join in your code?? Delimited list like this are painful for performance and logic because it violates 1NF. This has all the telltale signs of being an [xy problem](http://xyproblem.info/). – Sean Lange Jun 17 '19 at 14:01

0 Answers0