2

I am testing the behavior of a SELECT statement that returns 170,000,000 rows using SQLCMD. My goal is to cut out the time spent receiving the result-set on the client end (the time the process shows the ASYNC_NETWORK_IO waittype while an output file is being written to disk). I would like to have SQLCMD run my SELECT statement but not actually save or output the result-set. Just returning the number of rows affected would be fine. Is this possible?

sqlcmd -E -t 0 -S server\instance -d databaseName -i hugeselect.sql -o none?
2
  • 2
    -o nul: Commented Oct 22, 2012 at 20:47
  • The above commment is the best solution. While this didn't completely eliminate the client time, it cut it down to being as fast as possible. Commented Oct 23, 2012 at 17:23

3 Answers 3

2

This isn't an answer for SQLCMD specifically (I don't know of an option to discard results), but an option for testing the performance of a query and ignoring the network overhead or client-side rendering is to use the free SentryOne Plan Explorer. By design we drop the results at the server and don't bring them across, allowing you to focus on the actual performance on the query rather than the performance of your network or the power of your desktop.

2
  • I'm using the free version of your tool already to examine query plans while I'm doing this. Are you describing something I could do with the Pro version specifically? Commented Oct 22, 2012 at 21:51
  • No, the free version does this. If you generate an actual execution plan from within the tool, it will return actual runtime metrics (duration, CPU, reads, etc.), but it does not return any query results. It discards those on purpose. Commented Oct 22, 2012 at 22:03
0

You could try piping it to nul, see if that helps, eg:

sqlcmd -E -t 0 -SyourServer -Q"SELECT * FROM sys.objects" > nul
1
  • Piping to nul won't accomplish what he is after. The result set will still go from the server to the client over the client. Commented Oct 22, 2012 at 22:19
0

Remus Rusanu had the best answer in his first comment.

sqlcmd -E -t 0 -S server\instance -d databaseName -i hugeselect.sql -o nul:

While this didn't completely eliminate the client time, it cut it down to being as fast as possible.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.