SQL Server Performance

Separate variable value Vs Inline select statement

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Jun 23, 2009.

  1. atulgoswami New Member

    This question came to my mind from my previous question (post).
    Option1: Assigning a value to local variable from a select statement separately and then use in final select statement.
    Declare @Ncall varchar (20)
    Select @Ncall = codevalue from table1 where <some condition>
    and then
    Select <list of columns>, @Ncall from table2
    Option2: Directly use value (from inline select statement) in final select statement
    Select <list of columns>,
    Ncall = Select codevalue from table1 where <somecondition>
    from table 2
    Which one from above mentioned option will be optimal way if consider performance. This is inline with my previous post and wondering if it could help.
    Thanks
  2. FrankKalis Moderator

    In that case I would avoid any potential issue and assign a value to @Ncall before the final SELECT statement. The optimizer should detect in the second case that the uncorrelated subquery only need to be executed once, but with the first approach you avoid any potential confusion.
  3. atulgoswami New Member

    [quote user="FrankKalis"]
    In that case I would avoid any potential issue and assign a value to @Ncall before the final SELECT statement. The optimizer should detect in the second case that the uncorrelated subquery only need to be executed once, but with the first approach you avoid any potential confusion.
    [/quote]
    Except this, will there be any performace gain? I am also looking for some suggestion in my earlier post, where i was thinking if table is very large then would be a good idea to put the data in table variable and then fire and assign it to local variable.
  4. FrankKalis Moderator

    I do use mostly the first approach and resolve such things outside the main query. I'm not really sure about performance, but that should be easy to test.

Share This Page