Performance tuning the SQL query - 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.state=@num 

(This is from a cursor, i.e. for each fetch of @num passed to this where clause.Likewise @num values will be passed from 1 to 50 to the 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).

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

If you run this query through Database Tunning Advisor, which index does it suggest?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Both table have Clustered Indexes on Primary Keys.

And all those lots of columns in the JOIN clause are the primary key? If not, is there a suitable index for it? Have you check the execution plan if there may is a table scan instead of index seeks?

---

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

Answer 3

Hi Olaf,

The columns on the join clause are not Primary keys and there are no indexes created on them. I'm running the execution plan and let you know what's the result.

Thanks,

Narsimha

Answer 4

Hi Naomi,

Currenlty running the execution plan, i will reply with the result.

Thanks...

Answer 5

and there are no indexes created on them.

Hello Narsimha,

and this seems to be the problem, why it's so slow. In execution plan you may will see a "table scan" or "Index scan".

---

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

Answer 6

Yes, exactly. I suggest to run this query through Database Tunning Advisor. Although in the Execution plan you also will see missing index. Post that index here, we'll be able to tell, if that index is good or not.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

WHERE subt=@num 

(This is from a cursor, i.e. for each fetch of @num passed to this where clause.Likewise @num values will be passed from 1 to 50 to the 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).

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 ?

I sniff another issue here besides missing index(es). It seems that you are running this (already slow) INSERT/SELECT in a cursor 50 times! Going by the symptoms it looks like the two source tables are having full table scans / index scans and corresponding bookmark lookups, and this is being repeated 50 times!

Could you try removing that cursor and using one of the following variations in the WHERE clause so that there is only one pass on the source tables -

WHERE subst IN (1, 2, 3, ... .50) -- or whatever specific values are required
-- Or
WHERE subst BETWEEN 1 AND 50 -- or whatever specific values are required

Also, I guess that having an index on subst column would be nice. More index suggestions could show up after running the query through the Database Tuning Advisor.

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | (http://aalamrangi.wordpress.com)

Answer 8

This entire query is contained within the logic of a cursor, you said? 

Just out of curiosity, what happens when you plug in an actual value for @num, and run the SELECT statement by itself in SSMS (NOT within the cursor, but off by itself)?  If it runs instantly/quickly, the CURSOR is the issue, not the query itself, and it's time to shift the issue away from the query, and onto finding a way to accomplish what you're doing without using a cursor, which is very very frequently (but not absolutely always) possible.

Answer 9

@Aalam Rangi

Yes, your guess is right about indexing on the column of where clause.

With the help of Execution Plan, i was able to create indexes on -

Subt table  - A Non-unique,Non-clustered index - Subt.State (column in the where clause) as Index key column and subt.[col7],subt.[col8],subt.[col9].....what columns used in the join condition as Included columns

Stg table - A Non-unique,Non-clustered index - stg.[col12], stg.[col13] as Index key columns and columns used in the join condition as Included columns

So, now both table have indexes created as suggested from Exec. Plan.

WHERE subst IN (1, 2, 3, ... .50) -- or whatever specific values are required
-- Or
WHERE subst BETWEEN 1 AND 50 -- or whatever specific values are required

I don't know whether removing the cursor and passing the values like above improves the performance or not. I'll give it a try..

@ joshnqflorida

That's what i've done previously, removed the cursor and passed single @num values to the where clause and the select itself running slow.

Currently running the proc. for a subset of data and see how is the performance...

Answer 10

WHERE subst IN (1, 2, 3, ... .50) -- or whatever specific values are required -- Or WHERE subst BETWEEN 1 AND 50 -- or whatever specific values are required

Also, I guess that having an index on subst column would be nice. More index suggestions could show up after running the query through the Database Tuning Advisor.

I removed the cursor and passed the required values manually to the subst IN(1,2,4...50) to test and it seems to be working fine.

My question is how do i replace the cursor as the values in the IN operator are not sequence and there will be 38 values i.e. 1,3,4,5,7,9,10,11...50

Thanks.

Answer 11

If the cursor was your main problem, you can do instead

create table #Nums (Number int primary key)

insert into #Nums values (1),(2), ...etc. all the numbers you need

select ...

where subt.state IN (select Number from #Nums)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog