I'm learning and managing a little query optimization if someone can give me feedback
I know that it SET NOCOUNT ON;
works to optimize and speed up a query within SQL, what I want to know is where to start putting it, if after Declaring variables, before, before Select
And what other methods are there for better query optimization?
As
SET NOCOUNT ON
the documentation says , it does not show the total number of rows affected in sentencesSELECT/INSERT/UPDATE/DELETE
. Depending on the logic of the script or stored procedure, hiding this message can result in a performance improvement, as mentioned:Let us understand that, although small, this is a message that travels through the network and reaches the client even if there is no data to be returned, without a doubt there may be a scenario where this is significant, although most likely in these cases, it will be required rather an optimization in logic rather than in this detail.
But there are also other senses for this:
Merely aesthetic: When a stored procedure that does not return data simply performs updates and other operations, it may be more "aesthetic" and perhaps more logical not to show the number of rows affected, it must be taken into account that otherwise this data would be shown by each clause mentioned above.
Consistent with the logic of the Script/Sp or technically necessary: The case of a stored procedure that returns a data recorset, but in the middle performs multiple SQL statements, in this case, it is convenient to use the
SET NOCOUNT ON
to "hide" this output for all intermediate statements and enable it with theSET NOCOUNT OFF
only with theSELECT
final, in this way, the rows obtained will be consistent with the information of affected rows. Technically necessary: I have ever come across certain clients that require this information and that it is particularly consistent with the rows received, this is because this statement prevents the sending of the DONE_IN_PROC message that marks the end of the stored procedure to the client and that some tool may eventually wait, so controlling where this message is displayed may be a technical requirement.SET NOCOUNT ON
it is not a method to optimize and speed up a query. What it does is prevent messages from showing the number of affected rows. This is done to avoid unnecessary messages when querying from an application.Being an instruction that defines how the code will behave within the session, it must be the first instruction used.