April 2, 2003 at 12:38 pm
I've been frustrated by this topic for a while, mostly because there is no good authoritative opinion on whether one is inherently better than the other. I tried using the sample code from guarddata's post to run the loop over multiple variable assignments using the following three structures inside the loop:
/*
SET @testVar1 = 'Value' -- or SELECT here
SET @testVar2 = 'Value' -- or SELECT here
SET @testVar3 = 'Value' -- or SELECT here
SET @testVar4 = 'Value' -- or SELECT here
SET @testVar5 = 'Value' -- or SELECT here
*/
/*SELECT @testVar1 = 'Value'
SELECT @testVar2 = 'Value'
SELECT @testVar3 = 'Value'
SELECT @testVar4 = 'Value'
SELECT @testVar5 = 'Value'
*/
SELECT @testVar1 = 'Value', @testVar2 = 'Value', @testVar3 = 'Value', @testVar4 = 'Value', @testVar5 = 'Value'
I didn't see much difference between the 5 individual SELECT statements vs. the 5 individual SET statements although the SELECT statements were slightly faster in all tests. But when I used the last option to do the 5 assignments in a single select statement it ran in about half the time of the previous two trials. Wouldn't that be an argument to use SELECT instead of SET when assigning multiple variables at the same time? I ran this through SQL Query Analyzer on a 2000 Server using SET NOCOUNT ON at the top of the query. I'm only just starting to scratch the surface of performance tuning so if anyone can provide any insight into flaws this type of test might be subject to I'd appreciate it. Otherwise I'm not going to go out of my way to replace the usage of SELECT with SET to assign local variables in the majority of stored procedures I'm working with. Thanks.
April 2, 2003 at 9:47 pm
Another thing one must take into account is the influence of SET / SELECT on the servers variables. SET does not affect the @@ROWCOUNT while SELECT (without the NOCOUNT on toggle) does.
When debugging T-SQL this matters.
April 3, 2003 at 12:17 am
hello family,
i am not exactly an "authority", but it really comes down to preference. as #click-fund# mentioned before, the SET command is the same as a SELECT assignment statement. i use SELECT because it is a lot more flexible than set. something that hasn't been mentioned is using the SELECT for an INSERT:
e.g.
SELECT
@title='somebook',
@author='joe blow',
@price=5.00;
INSERT INTO books
SELECT @title, @author, @price
i have found that using SELECT statements for my INSERTs is much faster than field/value lists and aesthetically it is better. the SELECT is really a consistency thing for me. we know SELECT is common across DBMSs. oh, and thanks for the NOCOUNT tip. i'm gonna run some tests to see if some gains can be achieved in my SPs.
-->April 3, 2003 at 7:10 am
This has been a really helpful thread for me. A note on NOCOUNT OFF:
We had a bedeviling problem with update method in an ADO cursor used in an ASP due to having NOCOUNT off in the SQL SERVER. Cursor Conflict Error ADO should be more transparent in it's server requirements but eventually we tracked it down to this and fixed the problem.
April 3, 2003 at 7:17 am
This has been a really helpful thread for me. A note on NOCOUNT OFF:
We had a bedeviling problem with update method in an ADO cursor used in an ASP due to having NOCOUNT off in the SQL SERVER. Cursor Conflict Error ADO should be more transparent in it's server requirements but eventually we tracked it down to this and fixed the problem.
April 3, 2003 at 8:25 pm
One thing to note in using Set RowCount is that I have seen a lot of examples where people set it at the top of a stored procedure and then reset it at the end. Fine, until you nest your stored procedures - which on completion leave it set off and return to the previous SP on the stack which continues to pump out xx records affected statements.
The only other option is to wrap it around all of your select statements individually (or within blocks). Annoying!
Just as a rule, I tend to use Set where I can but if it means requerying data to populate multiple variables then I use the Select statement instead with the NoCount statement on either side.
April 4, 2003 at 6:52 am
quote:
For stored procedures, I always include SET NOCOUNT ON as the first statement. I don't want the stored procedure to return those "1 row affected" output and only output the result set I intend.
The reason I don't encourage this is that there are times you want the rowcount returned, particularly for action queries which won't return a recordset. We have applications that have operations executed by the users that will then inform the users the numbers of updated/inserted/deleted rows. Automatically adding SET NOCOUNT ON would make this impossible.
If you are using middle tier objects to access your stored procs, I recommend instead the use of the adExecuteNoRecords option. You can use this when you don't need the recordcount, and omit it when you do. This allows the stored procedure to go either way, and the middle tier object, which should encapsulate the business rules, decided whether it needs the information or not.
April 4, 2003 at 8:17 am
One more hand up to agree with click-fund's take on the aesthetics of SET and SELECT
quote:
The reason I don't encourage this is that there are times you want the rowcount returned, particularly for action queries which won't return a recordset. We have applications that have operations executed by the users that will then inform the users the numbers of updated/inserted/deleted rows. Automatically adding SET NOCOUNT ON would make this impossible.
In the case of operations without a recordset returned, the SP could RETURN @@ROWCOUNT to notify the application of records affected.
April 4, 2003 at 8:27 am
True, I've seen this used as an output variable, but then you have to set up the retrieval process with an output parameter rather than send the variable that will be populated along with the execute statement. It's a less than elegant solution where an elegant one exists. It also does not allow the business object to make the decision as to whether the row count is needed or not--it always calculates it, whether the information is retrieved or not.
April 4, 2003 at 8:53 am
Yikes. You're right. I stand corrected.
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply