SET vs SELECT

  • 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...

  • 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.

  • 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.

  • 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

  • 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-

  • 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

  • [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.

  • 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-

  • 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.

  • 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-

  • 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

  • I appreciate that Bob but again I must pass that honorable statement on to Robert Marda (rmarda) as I got it from him.

  • 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.

  • 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

  • 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