Faster way to get an SQL-compatible string from a Guid

Question

I noticed this particular line of code when I was profiling my application (which creates a boatload of database insertions by processing some raw data):

myStringBuilder.AppendLine(
    string.Join(
        BULK_SEPARATOR, new string[]{myGuid.ToString() ...

Bearing in mind that the resultant string is going to end up in a file called via the TSQL command BULK INSERT, is there a way I can do this step faster? I know that getting a byte array is faster, but I can't just plug that into the file.

Answer 1

You aren't indicating where you are getting the guid from. Also, I don't believe that getting the bytes are going to be any faster, as you are going to do what the ToString method on the Guid class already does, iterate through the bytes and convert to a string value.

Rather, I think a few general areas that this code can possibly be improved upon in terms of performance are (and assuming you are doing this in a loop):

• Are you reusing the myStringBuilder instance upon a new iteration of your loop? You should be setting the Length (not the Capacity) property to 0, and then rebuild your string using that. This will prevent having to warm up a new StringBuilder instance, and the memory allocation for a larger string will already have been made.

• Use calls to Append on myStringBuilder instead of calling String.Join. String.Join is going to preallocate a bunch of memory and then return a string instance which you will just allocate again (if on the first iteration) or copy into already-allocated space. There is no reason to do this twice. Instead, iterate through the array you are creating (or expand the loop, it seems you have a fixed-size array) and call Append, passing in the guid and then then BULK_SEPARATOR. It's easier to remove the single character from the end, btw, just decrement the Length property of the StringBuilder by one if you actually appended Guids.

Answer 2

The fastest and simplest way would be not to use BULK INSERT with a raw file at all. Instead, use the SqlBulkCopy class. That should speed this up significantly by sending the data directly over the pipe instead of using an intermediate file.

(You'll also be able to use the Guid directly without any string conversions, although I can't be 100% sure what SqlBulkCopy does internally with it.)

Answer 3

If time is critical - could you pre-create a sufficiently long list of GUID's converted to string ahead of time, and then use it in your code? Either in C#, or possibly in SQL Server, depending on your requirements?