Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to add index to a temp table? And what's the difference between create #t and declare @t

Tags:

I need to do a very complex query. At one point, this query must have a join to a view that cannot be indexed unfortunately. This view is also a complex view joining big tables.

View's output can be simplified as this:

PID (int), Kind (int), Date (date), D1,D2..DN 

where PID and Date and Kind fields are not unique (there may be more than one row having same combination of pid,kind,date), but are those that will be used in join like this

left join ComplexView mkcs on mkcs.PID=q4.PersonID and mkcs.Date=q4.date and mkcs.Kind=1 left join ComplexView mkcl on mkcl.PID=q4.PersonID and mkcl.Date=q4.date and mkcl.Kind=2 left join ComplexView mkco on mkco.PID=q4.PersonID and mkco.Date=q4.date and mkco.Kind=3 

Now, if I just do it like this, execution of the query takes significant time because the complex view is ran three times I assume, and out of its huge amount of rows only some are actually used (like, out of 40000 only 2000 are used)

What i did is declare @temptable, and insert into @temptable select * from ComplexView where Date... - one time per query I select only the rows I am going to use from my ComplexView, and then I am joining this @temptable.

This reduced execution time significantly.

However, I noticed, that if I make a table in my database, and add a clustered index on PID,Kind,Date (non-unique clustered) and take data from this table, then doing delete * from this table and insert into this table from complex view takes some seconds (3 or 4), and then using this table in my query (left joining it three times) take down query time to half, from 1 minute to 30 seconds!

So, my question is, first of all - is it possible to create indexes on declared @temptables. And then - I've seen people talk about "create #temptable" syntax. Maybe this is what i need? Where can I read about what's the difference between declare @temptable and create #temptable? What shall I use for a query like mine? (this query is for MS Reporting Services report, if it matters).

like image 293
Istrebitel Avatar asked Jun 17 '11 11:06

Istrebitel


People also ask

Can you create an index on a temporary table?

You can define complex indexes on temporary tables, just as if they are permanent tables, for the most part. So if you need to index columns but have duplicate values which prevents you from using UNIQUE , this is the way to go. You do not even have to worry about name collisions on indexes.

Can you add an index to a temp table SQL?

Adding indexes to the SQL temp tables will enhance its performance if the index is chosen correctly, otherwise, it can cause performance degradation.

Can we CREATE INDEX after table is created?

Create Indexes After Inserting Table Data. You should create an index for a table after inserting or loading data (via SQL*Loader or Import) into the table. It is more efficient to insert rows of data into a table that has no indexes and then create the indexes for subsequent access.

Can we create indexes on table variables or temporary tables?

table variable doesn't allow to create index after insert.


2 Answers

#tablename is a physical table, stored in tempdb that the server will drop automatically when the connection that created it is closed, @tablename is a table stored in memory & lives for the lifetime of the batch/procedure that created it, just like a local variable.

You can only add a (non PK) index to a #temp table.

create table #blah (fld int) create nonclustered index idx on #blah (fld) 
like image 114
Alex K. Avatar answered Oct 05 '22 23:10

Alex K.


It's not a complete answer but #table will create a temporary table that you need to drop or it will persist in your database. @table is a table variable that will not persist longer than your script.

Also, I think this post will answer the other part of your question.

Creating an index on a table variable

like image 25
Brian Dishaw Avatar answered Oct 05 '22 23:10

Brian Dishaw