Visual Studio Code vs T-SQL – Which is faster?

Task: Insert into 26,000+ rows values of Long/BigInt into 2 columns, based/calculated on values in other columns of that same row (StartIPAddress & EndIPAddress, in this case…).

The conversion is simply from a dotted quad v4 IP Address (41.0.0.0) to a long representation of same (687865856).

Round 1:

The function in IPUtils to do the conversion:

Time for all rows? 7,500 seconds on a 8-core 4.3GHz box, VS 2012, SQL Server 2012…

Round 2:

I wrote a UDF to go from dotted-quad ipv4 to long:

…and I called it on all rows like this:

So guess what DateDiff returned? Remember, in code it was 7,500 seconds, which was 125 minutes, or 2 HOURS!

 …in T-SQL is took 373 MILLISECONDS

 

Now I understand that I had to save each row separately, and that I could have loaded up another List(Of XXX) and done an AddRange(), but DAM!

pat
:)

Leave a Reply

Your email address will not be published. Required fields are marked *