Adding non clustered index to temp tables for firing query quick in SSMS

Question

So the current query I have takes 3 hours to run and realize i need to add non clustered index to run the query fast. So I have added this line of code but still its taking massive time with no result:

create nonclustered index #temp_index 
  on [dbo].#tempData ([class_room]) include ([class_name],[class_floor],[class_building])

This is the current query I have:

IF OBJECT_ID('tempdb..#tempdata') IS NOT NULL DROP TABLE #tempdata

    SELECT [class_room][class_name],[class_floor],[class_building]
    INTO #tempdata
    FROM class_info x

create nonclustered index #temp_index 
   on [dbo].#tempData ([class_room]) include ([class_name],[class_floor],[class_building])

;with cte1 as(
SELECT [class_room][class_name],[class_floor],[class_building]
FROM #tempdata
WHERE class_room <> '')  

select * from cte1

Can you post the actual execution plan? Which part takes a long time? The insert into tempdata or the select? — SQLChao, May 04 '16 at 16:06
For execution cost it shows: Table Insert (#tempdata), Cost: 99% — Sahil K, May 04 '16 at 16:08
Insert into temp table is taking long time as per execution plan. But when i just run upto that part it runs query fast — Sahil K, May 04 '16 at 16:34
Ok I tried adding image of plan, stackoverflow shows cancel and add another. Not sure why I am unable to add pic — Sahil K, May 04 '16 at 16:57
How many rows are in class_info and why are you bothering with the temp table at all? — Alan Macdonald, May 04 '16 at 19:24

score 2 · Answer 1 · answered May 04 '16 at 17:24

2

Try to create #tempdata first.

create table #tempdata(
tempid int identity(1,1) not null, --to protect against duplicates
[class_room] varchar(50), 
[class_name] varchar(50),
[class_floor] varchar(50),
[class_building] varchar(50),
primary key (class_room, tempid), -- test this
unique (class_name,class_floor,class_building, tempid) -- or that
)

answered May 04 '16 at 17:24

Alex Kudryashev

9,120
3
27
36

But its like creating a proper table. – Sahil K May 04 '16 at 18:13
Look at `#` sign before the name. It means temporary table. They are automatically dropped at the end of session. In all other respects they behave as usual tables. – Alex Kudryashev May 04 '16 at 19:16

Adding non clustered index to temp tables for firing query quick in SSMS

1 Answers1