February 20, 2003 at 4:46 pm
This is all very helpful. I was just wondering if there is a way that this can be done in a query like one would create a SUM (see below)?
SELECT Product_Code, CAN_Fiscal_Year, SUM(Sales_Amount) AS [Total Sales]
FROM CAN tblx
WHERE (NOT (Sales_Year IS NULL))
GROUP BY Product_Code, CAN_Fiscal_Year
Thanks for any help. I could always set this up in a SP using a temp table to produce the the same...
February 24, 2003 at 8:17 am
ARGH! To think of all the pain and suffering I've gone through when I could have used...
SELECT @var = @var + IsNull(colx + ', ', '') FROM tblX WHERE colz = 12
Thank you!
However, save yourself some grief and remember to initialize the @var with something other than NULL.
February 24, 2003 at 8:43 am
IF you are wanting to assign a variable,
use SET. It is 3x more efficient even on multiple variable assignment.
SELECT is a SQL command performing other task not necessary to simply assign an expression to a variable, wasting resource.
February 24, 2003 at 8:56 am
quote:
use SET. It is 3x more efficient even on multiple variable assignment.
pitreconsulting, can you explain how you came up with the 3x? I am curious to see hard data supporting this. I have always used SET for assigning variables, but it would be nice to see documentation as to which is better, and under what circumstances. A lot of times without hard facts developers(myself included) make assumptions /educated guesses about how things work behind the scenes. Hard data is always preffered.
Tim C.
//Will write code for food
Tim C //Will code for food
February 24, 2003 at 10:06 am
In the tests that I have run, I have found the performance of SET and SELECT to be identical for single variable assignments. However, I am currently working in a high volume environment and always use SELECT.
In a test that assigned three variables. One million iterations took 4.8 seconds with SELECT vs. 7.8 using SET.
Guarddata-
February 24, 2003 at 10:28 am
Interesting... Guard were you using SELECT with "SET NOCOUNT ON"? Did you time just the SET's and SELECT's for the variable assignments or were there other statements in the SQL that might have affected the time results? How did you run the timer? From the client or profiler? Sorry for the questions, was was just curious about your DOE, and what control was used...
Tim C.
//Will write code for food
Tim C //Will code for food
February 26, 2003 at 8:45 pm
[One more thank you for the concatenation trick.]
If there was an execution difference between
SET @X=7
and
SELECT @X=7
don't you think the optimizing query processor would replace the slow one with the quick one when your code compiles?
When I do a showplan on the following:
DECLARE @X INT
SET @X=7
DECLARE @Y INT
SELECT @Y=8
I get the logical operation "SELECT" returned for both actions.
Personally, I use SET unless I'm reading a table. It's an aesthetic choice.
February 27, 2003 at 9:29 am
Tim,
I don't remember if we had SET NOCOUNT ON -- been over a year since that little test.
We ran the test in Query analyzer with the only difference in the code being the SET or SELECT line. Times were obtained by using the GETDATE() function. Something like...
SELECT @iLoop = 0, @stTime = GETDATE()
WHILE @iLoop < 1000000
BEGIN
SET @testVar = 'Value' -- or SELECT here
SELECT @iLoop = @iLoop + 1
END
SELECT DATEDIFF( MS, @stTime, GETDATE()) 'Milliseconds'
Not too fancy, perhaps there is a flaw - but seems to show a difference
Guarddata-
February 27, 2003 at 9:47 am
Guard, you used the set and select both in your loop. Not sure if it makes too much of a difference but I tried this.
declare @iLoop integer
declare @stTime datetime
SELECT @iLoop = 0, @stTime = GETDATE()
WHILE @iLoop < 1000000
BEGIN
SELECT @iLoop = @iLoop + 1
END
SELECT DATEDIFF( MS, @stTime, GETDATE()) 'Select in Milliseconds'
SELECT @iLoop = 0, @stTime = GETDATE()
WHILE @iLoop < 1000000
BEGIN
set @iLoop = @iLoop + 1
END
SELECT DATEDIFF( MS, @stTime, GETDATE()) 'Set in Milliseconds'
After running it a few times I noticed that the they both win about the same amount of times. I am running it in a production environment too though.
I agree with click-fund with it being an aesthetic choice. I like Set better.
February 28, 2003 at 11:10 am
Cheddar,
You are right - they are about the same for setting a single variable (I think that was part of my first comment). And for appearances, again I agree that most people prefer SET.
I only wanted to point out that if performance is the issue and multiple values are involved, SELECT is my choice.
Guarddata-
March 1, 2003 at 7:57 am
I vote for Antares' tip for "tip of the year" already even though we're just started.
I've used a friggin cursor to loop through and build a string containing email addresses so many times I could write it with my eyes closed.
SELECT @var = @var + IsNull(colx + ', ', '') FROM tblX WHERE colz = 12
is now taped to my forehead.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
March 1, 2003 at 6:16 pm
I appreciate that Bob but again I must pass that honorable statement on to Robert Marda (rmarda) as I got it from him.
March 3, 2003 at 9:13 am
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.
March 3, 2003 at 3:41 pm
Chief, I agree. My first line in ALL my stored procedures is always SET NOCOUNT ON.
Nice tip on concatination Antares686/rmarda.
I prefer SET over SELECT. Basically an aesthetic choice.
Cheers,
Kevin
March 6, 2003 at 12:04 am
I've posted this comment previously a while ago in another thread - but one of the differences between Set and Select which I find are important is that the annoying little "1 row(s) returned" statement is actually a client-communication process - in that there is a round trip from server to client and back again which slows processing down. For instance, say you have a network ping of 100ms - you can imagine the effect. Anyhow, the Set statement doesn't do it, and neither does the "Set NoCount On" statement.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply