Performance tuning of Stored Procedure - Need help

• Wednesday, May 02, 2012 5:40 PM

   

   
  

  0

  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 ?

  • Reply
  • Quote