I have a huge INSERT script that, for my sins, I am maintaining. It’s huge and slow. That’s not a show-stopper because performance isn’t really an issue, we can leave it running overnight, but some improvement would be nice.

I thought that a large part of the bottleneck was my client sending the text of the INSERT statement over the network to the server, and that reducing the size to around a third would help, but in fact it hurts.

If I strip out all the NULL values and the column names for those NULLs, the script gets a lot smaller, but it’s also slower to execute. I’m running a test with 10,000 INSERTs and it’s like 6 minutes verses 3.5 minutes.

What’s going on? Why would INSERT statements with all the columns specified but NULLS provided be faster than INSERT statements with fewer columns specified? I guess the database is having to do checks to see if a column has a default value (it doesn’t), but I’m surprised it’s that significant.