insert using cursor or store proc

Question

insert using cursor or store proc

sqlstar2011

SSCertifiable

Points: 6000
More actions
January 22, 2009 at 3:11 pm

#155164

I having following insert statement and its taking FOREVER to run.. probably because the sql is looking up the table its writing to..
insert into T_D
(collector, pl, fyi, fyp, fyc, fyv)
select collector, pl, fyi, fyp, fyc, fyv
from T_C
where collector + str(pl) + fyi not in
(select collector + str(pl) + fyi
from T_D)
go
what can I do to make this run faster? I already have indexes on collector + str(pl) + fyi in T_C and T_D tables. And execution plan is looking at the indexes. Inserting into a temporary table isnt fast either. Would writing a cursor be better than this approach?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

eric.craver SSC Enthusiast Points: 177 More actions · Answer 1

shahab (1/22/2009)
I having following insert statement and its taking FOREVER to run.. probably because the sql is looking up the table its writing to..
insert into T_D
(collector, pl, fyi, fyp, fyc, fyv)
select collector, pl, fyi, fyp, fyc, fyv
from T_C
where collector + str(pl) + fyi not in
(select collector + str(pl) + fyi
from T_D)
go
what can I do to make this run faster? I already have indexes on collector + str(pl) + fyi in T_C and T_D tables. And execution plan is looking at the indexes. Inserting into a temporary table isnt fast either. Would writing a cursor be better than this approach?

A sub query is going to have more scans than a join. Try something like this

insert into T_D

(collector, pl, fyi, fyp, fyc, fyv)

select c.collector, c.pl, c.fyi, c.fyp, c.fyc, c.fyv

from T_C c

left join T_D d on

c.collector + str(c.pl) + c.fyi = d.collector + str(d.pl) + d.fyi

where d.collector is null

sqlstar2011 SSCertifiable Points: 6000 More actions · Answer 2

did you mean to write

where d.collector is not null ?

eric.craver SSC Enthusiast Points: 177 More actions · Answer 3

No, if you are looking for not in insert table. Left join where null shows all data in main table and not in join table.

sqlstar2011 SSCertifiable Points: 6000 More actions · Answer 4

interesting approach.. i will give this a shot. thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

The other part of the problem is the concatenations and the formulas on columns. No chance of an index being used for anything practical there.

Also, you're inserting into the same columns that the criteria is based on. Sometimes that can work like a cursor because it will sometimes cause a scan for every row (as already pointed out, ie. "Scans"). One of the best ways to avoid that is to predetermine (ie. Mark) the source table with just the rows that don't already exist in the target table or build a temp table that identifies the rows in the source table that don't already exist. I usually use the second method so I can test the inserts before actually doing them.

But, I believe the biggest problem is what I said to begin with... the concatenations in the criteria are just killing you. And, no... the WHERE NOT IN isn't the problem... it's the concatenations.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)