Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: @table VS #table

    WHIch one is the best to use in my SP's.I have lots SPs which are meant to calculate huge data,so in those SPs right now i am using lots of #tables and i found that my server performance is effected by those #tables. So i wanted to use @tables henceforth.
    Please guide me in this issue to choose the best one...

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How much data are you storing in the temp tables?

    Temp tables will use tempdb

    table variables will use memory...

    both use up resources...

    I believe BOL recommends table variables...

    What are you doing?

    Would it make sense to use physical staging tables?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2003
    Posts
    176
    maximum of 300 rows and each row having 10 columns and I run most of the SPs for every ten mins as a job.

    Thats the whole Picture.

    Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    With a 300 by 10 chunk of data, you aren't talking enough to get excited about either way. Table variables (@table) will give you faster scans, and temp tables (#table) will give you faster seeks. Most boxes I've seen have more disk than RAM, but either way your load isn't big enough to matter.

    I'm just curious, what makes you think that the #tables are affecting your server performance? Even if there were a couple dozen of them, they shouldn't be noticable. A few thousand would have some impact, but I doubt that you've got anywhere near that volume.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Another consideration is that you can put as many indexes as you want on a temporary table to increase JOIN efficiency, but on a table variable you can only have one index.

    I generally use table variables unless the dataset is going to be accessed multiple times on a non-indexed column.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Aug 2003
    Posts
    111
    another day, learnt another thing about sql server feature.
    thank you guys.



    ps: i am always excited about new things that i learn.

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734

    Re: @table VS #table

    Originally posted by bruce_Reid
    WHIch one is the best to use in my SP's.I have lots SPs which are meant to calculate huge data,so in those SPs right now i am using lots of #tables and i found that my server performance is effected by those #tables. So i wanted to use @tables henceforth.
    Please guide me in this issue to choose the best one...

    Thanks.
    It will vary by the system also.

    In our current system (we have our RAM max'd out though), we cut it off at 10k records as a general rule. If we join the table to a lot of tables later on in the procedure though, we will use temp tables to take advantage of the indexes. You need to find out what's good for your system.

    BTW, if you do a trace, you will find that table variables create a space in tempdb and use that. It will be something like #12@1aadfa0.

    The difference is how the memory space is used vs. the ability to use indexes.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Aug 2003
    Posts
    111
    With a table variable (never used one, But was about to use it to implement some sorta virtual queue for each user of a multi user environment),

    1. is it possible to define an identity column? (tried it without success)

    2. is it possible to select a subset of a table into the table variable? i.e. with out using an insert statement, but rather something more like
    SET @table_name = (SELECT col1, col2, col3 FROM TABLE1)
    I tried this without success.

    James

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by nano_electronix
    With a table variable (never used one, But was about to use it to implement some sorta virtual queue for each user of a multi user environment),

    1. is it possible to define an identity column? (tried it without success)

    2. is it possible to select a subset of a table into the table variable? i.e. with out using an insert statement, but rather something more like
    SET @table_name = (SELECT col1, col2, col3 FROM TABLE1)
    I tried this without success.

    James
    1. Yes, you can, as in:
    PHP Code:
    DECLARE @t TABLE (
       
    id        INT        IDENTITY
    ,  v        VARCHAR(20)    NOT NULL
    )

    INSERT INTO @(vVALUES ('One')
    INSERT INTO @(vVALUES ('Two')
    INSERT INTO @(vVALUES ('Three')

    SELECT FROM @
    2. Not as you've shown it, but you can sneak up on it using the INSERT INTO syntax.

    -PatP

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Indexing temp tables? It'll be a wash because of the possibility of recompiles and locking of sysobjects, sysindexes, and syscolumns in tempdb. I've never indexed a temp table because I've never had them big enough to bother. If it turns out to be of significant size, maybe using temp table was not the best choice? Percentage of table scan on it should be minimal compared to overall cost of the query, and THIS should be the main determinant whether to use temp table or not. And as far as a difference between # vs. @, - I'd use # only if I want to store a result of an " insert #tmp execute sp", otherwise - it's definitely @.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've gotten performance boosts from indexing table variables and temp tables, but only if they are referenced multiple times in a procedure. I normally use table variables now, but will revert to temp tables when I need additional indexes that can't be added to a table variable.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    We're the same way blindman. We do have some batch processes that run at night though where we have received huge performance gains from indexed temp tables. Usually they have 75k-700k records though and are joined several times.

    The performance increase on anything over several thousand can be mind-boggling by just building an index on the temp table.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  13. #13
    Join Date
    Aug 2003
    Posts
    111
    Originally posted by Pat Phelan
    1. Yes, you can, as in:
    PHP Code:
    DECLARE @t TABLE (
       
    id        INT        IDENTITY
    ,  v        VARCHAR(20)    NOT NULL
    )

    INSERT INTO @(vVALUES ('One')
    INSERT INTO @(vVALUES ('Two')
    INSERT INTO @(vVALUES ('Three')

    SELECT FROM @
    2. Not as you've shown it, but you can sneak up on it using the INSERT INTO syntax.

    -PatP
    Cheers

    For answer to question 2, did u mean something like ...

    INSERT INTO myTable99(Col1)
    SELECT '1~2~3~4~5' UNION ALL
    SELECT '1~2~3~4~5' UNION ALL
    SELECT '1,2~3,4,5'

    I pulled it above off another thread. hehehe.

    James
    Last edited by nano_electronix; 04-09-04 at 21:50.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think the question was, can you use SELECT INTO to create a table variable the same way you can create other tables on the fly, and the answer is no. Table variables must be explicitly defined, though I'm not sure what Pat meant by "sneaking up on it". Pat?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Aug 2003
    Posts
    111
    Is this possible?

    DECLARE @t TABLE (
    col1 VARCHAR(20),
    col2 VARCHAR(20),
    col3 VARCHAR(20)
    )


    INSERT INTO @t
    (col1, col2, col3)
    values
    (SELECT col1, col2, col3 FROM src_table)


    sorry i have to ask instead of trying it out myself, main reason is because i had a fresh install which i want to ghost before i do anything to it and i don't want to import any sample database into the freshly installed database.


    I guess the question i am asking about is -
    "Is it possible to do multiple insert like how i wrote the insert query above?"

    cheers

    James

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •