SQL Server Performance

Table Variables

Discussion in 'T-SQL Performance Tuning for Developers' started by abertini, Dec 6, 2002.

  1. abertini New Member

    In a stored procedure I have two tables variables:

    DECLARE @Devol TABLE (Idbint,
    Familiachar(6),
    CostoRepnumeric(24, 4)
    PRIMARY KEY CLUSTERED (Idb, Familia))

    DECLARE @Venta TABLE (Idbint,
    Familiachar(6),
    CostoRepnumeric(24, 4)
    PRIMARY KEY CLUSTERED (Idb, Familia))

    When I do the join, it doesn#%92t uses the primary key, and executes a table scan for the two variables. If I write the CREATE in this way

    DECLARE @Devol TABLE (Idbint,
    Familiachar(6),
    CostoRepnumeric(24, 4),
    CONSTRAINT PK_DEVOL PRIMARY KEY CLUSTERED (Idb, Familia))

    the stored procedures doesn#%92t compiles, and if I write the SELECT in this way

    SELECT V.Idb, V.Familia, ISNULL(V.CostoRep, 0) - ISNULL(D.CostoRep, 0)
    FROM @Devol D RIGHT OUTER JOIN @Venta V (INDEX = 1) ON V.idb = D.Idb AND V.Familia = D.Familia

    Either it uses the primary key

    How can I do to force the SELECT to use the primary key and to execute an index seek? Thanks
  2. bradmcgehee New Member

    I have not tried this myself using table variables, so I know if they suggestion will work, but you might want to consider experimenting with an appropriate hint to force the use of the correct index. See this URL for information about hints.

    http://www.sql-server-performance.com/hints.asp


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. abertini New Member

    I tried it, but it doesn#%92t works.
    If I use the hint INDEX = 1 after the table variable in the from, the stored procedure doesn#%92t compiles (Instead if I use in the from a table, the stored procedures compiles).
    I tried to force the index with the name, but when I create the table variable I can#%92t give a name to the primary key because the stored procedures either compiles




    quote:Originally posted by bradmcgehee

    I have not tried this myself using table variables, so I know if they suggestion will work, but you might want to consider experimenting with an appropriate hint to force the use of the correct index. See this URL for information about hints.

    http://www.sql-server-performance.com/hints.asp


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  4. bradmcgehee New Member

    I am out of suggestions. And since there has been no other feedback so far, it looks like few other do also. So if you have any ideas for abertini, please offer them.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  5. satya Moderator

    Can you recreate the table alongwith indexes to affect the change. Try it and see.

    Satya SKJ

Share This Page