none
CLR example/ideas to return table from string needing parsing based on tab RRS feed

  • Question

  • Hi guys,

    I'm new to C#, but not new to SQL.  I have a table of about 40 million rows that has a single column.  The single column is TAB delimited values that I have previously BULK INSERTed.  Because the file that I am importing from has 8 different record types identified by the first value in each row, I can not use a field terminator because each record type has different column counts (ranging from 25 columns to 35 columns).

    So my approach has been to bulk insert the row as a single varchar(8000) column then parse the data in sql.  I have written a UDF, but it is pretty slow ( about 4 mins per million records).

    I have been looking at CLR and am struggling to find a good example.  My thought was that I would send a table object (or a row??) to the CLR assembly and have it return a row or table that I then insert?  Also becuase of the data size it seems like I would have to do a data stream to not run out of memory on the server. 

    I was also looking at Adam Mechanic's CLR splitter, as well as the new STRING_SPLIT function but it splits it in rows and I need columns.

    Here are a few links I have looked at:

    https://stackoverflow.com/questions/5653963/how-is-a-clr-table-valued-function-streaming

    https://www.codeproject.com/Articles/680161/Getting-Started-With-SQL-Server-CLR-User-Defi

    https://www.codeproject.com/Articles/14936/StringBuilder-vs-String-Fast-String-Operations-wit

    https://social.msdn.microsoft.com/Forums/en-US/8da9f894-dd15-4678-93f3-46fee0fc7a78/performance-implications-of-string-parsing?forum=clr

    https://www.c-sharpcorner.com/UploadFile/ff2f08/table-valued-clr-function-in-sql-server-2005/

    And many others..

    Any thoughts / help would be appreciated!

    Thanks,
    Matt

    PS:  I am using SQL Server 2016
    Wednesday, June 20, 2018 2:18 PM

All replies