SQL Server Performance

Use of Table variable in SQL Server

Discussion in 'SQL Server 2005 General Developer Questions' started by Lijo Cheeran Joseph, Feb 16, 2010.

  1. Lijo Cheeran Joseph New Member

    Hi Team,
    I am using SQL Server 2005. I have heard that we can use a table variable to use instead of LEFT OUTER JOIN.
    What I understand is that, we have to put all the values from the left table to the table variable, first. Then we have to UPDATE the table variable with the right table values. Then select from the table variable.
    Has anyone come across this kind of approach? Could you please suggest a real time example (with query)?
    I have not written any query for this. My question is - if someone has used a similar approach, I would like to know the scenario and how it is handled. I understand that in some cases it may be slower than the LEFT OUTER JOIN.
    Please assume that we are dealing with tables that have less than 5000 records.
    Thanks
  2. FrankKalis Moderator

    A table variable has no influence on the behaviour of a LEFT OUTER JOIN. In fact, you use a table variable in such a LEFT JOIN in the exactly same way you use any other base table, temp table, or view.
    I think you're are confusing things a bit here. Maybe you can explain what you really want to do please?
  3. Lijo Cheeran Joseph New Member

    I am trying to learn optimization in SQL. Following queries explains my thoughts. Both of them took 19 seconds.
    Is there any scenario in which the second approach will perform better? Introduction if indexes/ more data in either of the tables / more complex calculations - anything?CREATE TABLE #MainTable ( CustomerID
    INT PRIMARY KEY, FirstName
    VARCHAR(100) ) DECLARE @Count INT
    SET
    @Count = 0DECLARE @Iterator INT
    SET
    @Iterator = 0WHILE
    @Count <8000BEGIN
    INSERT INTO #MainTable SELECT @Count, 'Cust'+CONVERT(VARCHAR(10),@Count)
    SET @Count = @Count+1ENDCREATE
    TABLE #RightTable ( OrderID
    INT PRIMARY KEY, CustomerID INT, Product
    VARCHAR(100) ) CREATE
    INDEX [IDX_CustomerID] ON #RightTable (CustomerID)WHILE
    @Iterator <400000BEGINIF @Iterator % 2 = 0
    BEGIN
    INSERT INTO #RightTable SELECT @Iterator,2, 'Prod'+CONVERT(VARCHAR(10),@Iterator)
    END
    ELSE
    BEGIN
    INSERT INTO #RightTable SELECT @Iterator,1, 'Prod'+CONVERT(VARCHAR(10),@Iterator)END
    SET
    @Iterator = @Iterator+1END-- Approach 1: Using LEFT JOINSELECT
    mt.CustomerID,mt.FirstName,COUNT(rt.Product) [CountResult]FROM #MainTable mt
    LEFT JOIN #RightTable rt ON mt.CustomerID = rt.CustomerID GROUP BY mt.CustomerID,mt.FirstName ---------------------------
    -- Approach 2: Using Table variable UpdateDECLARE
    @WorkingTableVariable TABLE( CustomerID
    INT, FirstName
    VARCHAR(100),ProductCount
    INT ) INSERT
    INTO @WorkingTableVariable (CustomerID,FirstName)SELECT CustomerID, FirstName FROM #MainTable UPDATE
    @WorkingTableVariable SET
    ProductCount = [Count]FROM @WorkingTableVariable wt
    INNER JOIN(SELECT CustomerID,COUNT(rt.Product) AS [Count]
    FROM #RightTable rtGROUP BY CustomerID) IV ON wt.CustomerID = IV.CustomerID SELECT
    CustomerID,FirstName, ISNULL(ProductCount,0) [CountResult] FROM @WorkingTableVariable ORDER
    BY CustomerID--------DROP
    TABLE #MainTableDROP
    TABLE #RightTable

    Thanks for your kind support
  4. FrankKalis Moderator

    All else being equal I see no reason why the second approach should be faster as it has much more work to do.
  5. satya Moderator

    I would go with Frank's opinion too, in this case I support you to check the execution plans too.

Share This Page