SQL Server Performance Forum – Threads Archive

T-SQL Script Performance Issue

All – i’m having a problem with a script I’m working on. I’m trying to develop what should be a very simple and straightforward script. The purpose of the script is for it to run daily and insert new data into a table where it didn’t exist previously. Now, if i run the SQL in the cursor definition, it comes back in an acceptable time (less than 30 seconds) and returns no rows. I expect it to return no rows currently as all of the data has been seeded and I’m expecting this script to basically see that it has nothing to do. However, when I run the full script, it never comes back to me. In looking at the Activity Monitor while it’s executing, the only activity i see is activity against the TempDB, which is trying to create a temp table. Any ideas as to what I’m doing wrong here? declare
@inscnt int,
@btid varchar(50),
@pid int set @inscnt = 0; declare
btid_cursor
cursor for
select
aai.btid,
product_id
from
accum_all_inv aai
inner join
product_identifier_xref pix
on
pix.identifier_type_cd = ‘UPC’ and
pix.identifier_cd = aai.upc
where
not exists
(
select
1
from
product_identifier_xref pix2
where
pix2.identifier_cd = aai.btid and
pix2.identifier_type_cd = ‘BTID’
)
open btid_cursor fetch
next
from
btid_cursor
into
@btid,
@pid while @@fetch_status = 0
begin set @inscnt = @inscnt + 1; insert into
product_identifier_xref
(
identifier_cd,
identifier_type_cd,
product_id,
create_dt
)
values
(
@btid,
‘BTID’,
@pid,
getdate()
); fetch
next
from
btid_cursor
into
@btid,
@pid
end;
close btid_cursor
deallocate btid_cursor select
‘Inserts: ‘ + convert(varchar(50),@inscnt)

---

Can you give an example of what the required output (in your case input into the table) should look like? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

HI ya, to be honest I think that the mistake here is to use a cursor at all…? Id try something like insert into product_identifier_xref(
identifier_cd,
identifier_type_cd,
product_id,
create_dt
) select
aai.btid,
‘BTID’,
product_id,
getdate()
from accum_all_inv aai
inner join product_identifier_xref pix
on pix.identifier_type_cd = ‘UPC’
and pix.identifier_cd = aai.upc
left outer join product_identifier_xref pix2
on pix2.identifier_cd = aai.btid
and pix2.identifier_type_cd = ‘BTID’
where
pix2.identifier_cd is null select ‘Inserts: ‘ + convert(varchar(50),@@rowcount)
Cheers
Twan

---

Twan – thanks, that definitely would work. Although there are probably multiple ways to work around this, I’m still curious as to why my original code just hangs. it seems very straightforward, no?

---

I’d guess that when using a cursor it has to create the temporary table in order to not confuse the cursor? not 100% sure… as a row gets updated it can get moved to another part of the table space, this can sometimes cause the cursor to get itself tied up have you tried putting a FOR UPDATE at the end of the cursor declaration? Cheers
Twan

---

Give the trial version of SQL Optimizer for Visual Studio a try. I will try to rewrite this SQL in every possible way to find the best we to run it. You can get it athttp://www.extensibles.com/modules.php?name=Products&op=SSP Let us know what it comes up with as a result. The Relentless One
No Bug says alive http://www.extensibles.com

---

]]>