-1

So the current query I have takes long time to run. When i run execution plan, it shows: Table Insert (#tempdata), Cost: 99%. I realized i need to add non clustered index to run the query fast. So I have added this line of code but still there is no difference:

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
Shadow
  • 33,525
  • 10
  • 51
  • 64
Sahil K
  • 5
  • 1
  • 5
  • 5
    What leads you to believe that adding a non-clustered index is going to help with insert performance here? How many rows are you putting in the temporary table? Is there a reason that you even need the temporary table? – Tom H May 04 '16 at 17:49
  • 2
    Why are you creating a CTE if you're just going to select * from it? Why not just select into #tempData from class_info where class_room <> ''? Or, why create a temp table at all? What are you trying to accomplish? – Stan Shaw May 04 '16 at 17:49
  • @Tom: 9 million records – Sahil K May 04 '16 at 18:12
  • Thank you for posting the report of your current status. Was there some kind of **question** you were going to ask? (This doesn't seem to be related to MySQL at all; not at all clear why this is tagged with mysql.) As far as the SQL presented... *What the plastic?* Why is a temp table being created? Adding a non-clustered index isn't going to improve performance of loading the temporary table. – spencer7593 May 04 '16 at 18:24
  • You really think creating an index that used once is faster than running a single table scan. Why are you not just running the select on the table and skipping inserting 9 million rows. This is mess up. – paparazzo May 04 '16 at 20:20

1 Answers1

1

This is a bit long for a comment.

Can you explain why you are not just running this code?

SELECT [class_room], [class_name], [class_floor], [class_building]
FROM class_info x
WHERE class_room <> '';

If performance is an issue, I first would recommend getting rid of unnecessary reads and writes -- such as creating a temporary table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I guess as per an earlier question I asked, creating an index on class_room won't help. Right? Problem is "locality within indexes." – I_am_Batman May 04 '16 at 18:15