I have been using the windows-native sqlcmd to run scripts on my local SQL Server database. I have a set of script files that will create the database, add tables and indices, populate domain data, add sprocs, etc. Running the scripts is done executing a single sqlcmd -i <file>.sql per file. This worked well and was fast enough.
After switching to the new Go implementation of sqlcmd the database build time is several times slower.
I have not been able to find any way to get timing-type statistics from sqlcmd to determine if it is connection issues, connection pooling or other problems. I can see what looks like an improvement if I pass multiple files as additional -i <file.sql> options which suggests to me that it is an issue with either the startup of the Go command or the establishment of the initial DB connection.
I did a test where I took ten of my SQL files and I ran them as ten separate sqlcmd executions and also as one sqlcmd execution with the ten files as separate -i input files. The separate commands took 20 seconds, the all-in-one took four. So it would appear be to an issue with startup-overhead or connection overhead.
As suggested by @siggemannen, I tested connections using my AD credentials and a direct DB user. The AD credentials are significantly slower than the direct DB connection implying that is the root of the slowness I am seeing in my scenario.
I am looking for any suggestions or ideas either to solve the problem or to troubleshoot it further. Can anything be done to speed up the AD authentication?
The Go sqlcmd is not really slower than the native version. The use of the Windows/AD credentials to establish a connection adds to the startup time it takes when establishing a connection, and as the use case runs a SQL file per sqlcmd instance that extra overhead adds up.
We could rework the build scripts to run many SQL files per individual sqlcmd and that would restore the performance we need. However, an alternative we can use without any rework is to connect using the SQL Server-level principal sa that is created when the database is created. This restores performance equivalent to the native sqlcmd using the AD login.