Performance tuning of Stored Procedure - Need help

Question

I have a Stored Procedure which Inserts into a Table A based on joining of 2 tables Table B and Table C (each having 10 million records)

 

INSERT  into TableA

 Col1 int (PK)

,Col2 datetime

,Col3 varchar

,Col4 varchar

,Col5 float

,Col6 float

 

SELECT

                subt.Col1

               ,GetDate()

               ,stg.Col3 varchar

               ,stg.Col4 varchar

               ,stg.Col5 float

               ,stg.Col6 float

 

FROM TableB stg INNER JOIN TableC subt

ON   

        stg.[col7]= subt.[ col7] and

        stg.[col8] = subt.[ col8] and

        stg.[col9] = subt.[ col9] and

       

        stg.[col10] = subt.[ col10] and

        stg.[col11] = subt.[ col11] and

       

        stg.[col12] =subt.[col12] and

        stg.[col13] = subt.[col13] and

       

        stg.[col14] = subt.[ col14] and

        stg.[col15] = subt.[col15] and

        stg.[col16] = subt.[col16] and

 

WHERE subt=@num  (This is from a cursor, i.e. for each fetch of @num passed to this where clause)

 

The query is running very slow.

 

The Select itself taking time to return the records (for first value of @num=1, the Select statement is taking 15 mins. return 800000 records).

 

Likewise @num values will be passed from 1 to 50 to the where clause.

 

Both table have Clustered Indexes on Primary Keys.

 

How to improve this query’s performance. If I have to create Clustered / Non-Clustered indexes, on which columns I have to create ?

Answer 1

See your double post http://social.msdn.microsoft.com/Forums/en-us/transactsql/thread/2e283ffd-0f14-4f06-9bc6-a2846b2c996d

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing