list to table, versus delimitedsplit8k

  • ben.brugman

    SSChampion

    Points: 13321

    Dear reader

    The list to table is build around a while loop, one item is taken of the list and put in a table, the list is shortened and the while loop is repeated.

    On seeing this construction I thought this would be a good candidate to be replaced with the delimitedsplit8k function.

    So to 'convince' the responsible persons I scripted some code to show that a significant improvement was possible.

    Using the 'same' call for both situations the list to table construction (with the while etc.) and the delimitedsplit8k, I did some timing. The timing was not significantly different but in general the list to table (with the while etc.) was performing better.

    This is not a convincing scenario to use the delimitedsplit8k.

    During 'harvesting' some extra info I stumbled on what could be the cause, I will publish this in the next mail.

    Ben

    (3 strings of about 6000 characters each and a total of about 3000 to 4000 items was used to test both scenario's).

     

  • ben.brugman

    SSChampion

    Points: 13321

    As said, a follow up.

    Because I was still checking and testing some scenario's I didn't respond before.

    The 'main culprit' why the delimitedsplit8k was not performing as expected was that during generation of the testdata I used a construct which caused the strings to be nvarchar. Changing that to varchar(8000) give an advantage to the delimitedsplit8k function.

    Ben

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply