Menu
While I am going through the performance tuning concepts of SQL Server, I found stored procedure using SET NOCOUNT ON in the initial line and again setting it back to SET NOCOUNT OFF on the final line will help to improve performance.
My question here is: how will that be helpful while using the stored procedure with a SSIS package?
765
SET NOCOUNT ON prevents the sending of DONEINPROC messages to the client for each statement in a stored procedure.
One advantage of stored procedures is improved performance. Stored procedures typically execute faster than ordinary SQL statements because the database can create, optimize, and cache a data access plan in advance.
The SET NOCOUNT ON stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results. MySQL doesn't report the number of rows affected by a query, therefore there's no such function.
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
Source BOL: SET NOCOUNT
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
