index - table variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

index - table variable

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-13 : 09:15:02

I am using table variables to hold data from the sources table.
Source table is tblMain with millions of records...
table variables: @tbl1 and @tbl2 are populated from tblMain
Then @tblresult is populated by joining @tbl1 and @tbl2
It seems i can not place indexes on the table variables and so the join query is extremely slow...
It is not possible to create permanent tables for these instead, because, this query is run by each user in the database.

Question:
How can I improve the speed of this query please?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 09:18:21

you may use temporary tables (# tables) if you want to create indexes

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 09:19:27

What is about using #temp_tables?
@Tablevars should not be used if there are more than 100 records or so...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 09:19:48

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-13 : 09:30:24

The rule I follow in most cases is

quote:
to use table variables for returning results from user-defined functions that return table values and to use temporary tables for storage and manipulation of temporary data

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-13 : 09:56:16

Yes, I was under the impression that temp tables cause locking...
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-13 : 10:15:20

If it is ok, please let me know how to create several indexes in a temp table in sql. I know how to add one but not sure how to add more than one.
I basically have one line per index. Is it ok to have it like this i.e. creating the indexes one by one or do they all have to be in one query?
Thanks

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 10:46:46

"I was under the impression that temp tables cause locking..."

Shouldn't be a problem provided that the #table is pre-created.

However, doing

SELECT A, B, C
INTO #MyTempTable
FROM MyTable

will cause locking, and this will be severe if the FROM MyTable query is slow.

You can create a Primary Key on an @TableVar, so if one index (per table) is enough maybe that would do you?


DECLARE @MyTableVar TABLE
(
   MyCol1 INT,
   MyCol2 INT,
   MyCol3 INT,
   PRIMARY KEY
   (
       MyCol1, MyCol2
   )
)

Primary key needs to be unique, so add an IDENTITY column if there are DUPs in the keys you want to use for your Index.

P.S. I don't worry too much about the number of rows in @TableVar tables, as they will just use TEMPDB if they are large, but I expect would use a different approach if I was needing to store millions of rows in a temp table.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-13 : 11:46:11

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources