DTS vs Sproc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS vs Sproc

Author

Topic

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-08-25 : 14:30:56

Just wondering. I have something in a stored procedure that can take up to 30 minutes to complete. is it likely that a DTS will execute this faster with its execute sql task?

thanks

Ben

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-25 : 14:32:56

Depends, the best way to find out is to run a test

You might want to try and post the SP here, somebody might be able to make it run faster too!

Owais

Make it idiot proof and someone will make a better idiot.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-25 : 14:34:55

Why would DTS execute it faster at all? What is it that takes 30 minutes? Adding DTS into the picture would most likely slow things down.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-25 : 14:55:01

What do you think the dts execute sql task does?
You should also try writing other applications to call the SP and see if that helps too.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-08-25 : 15:06:33

First
CREATE Procedure SPGenTempScrub
(
@cycle char(3),
@pgrade char(1),
@prevcycle char(3)
)
AS
Truncate Table TempScrub;
Insert into TempScrub(tname, ssn, cycle, uic, pguic, pgrade, prate,tirdate)
Select distinct nm_name, nm_ssn, @cycle, nm_uic, nm_puic, nm_ppg, nm_prate, nm_tirdate
From navm, cyclecontrol
where cc_cycleno = @cycle
AND nm_ppg = @pgrade
AND nm_uic In(Select uic from uics)
AND nm_ssn+nm_uic not in(Select uic+ssn from scrublist)
And nm_brcl <> '09Z'
AND nm_spig <> 'J'
AND nm_ssn not in(Select ssn from neasrpts
where cycle = @prevcycle
AND npg = @pgrade
AND ((pc >= '01' and pc <= '15')))
AND isdate(nm_tirdate) = 1;
GO

CREATE Procedure SPUpdateNucs
(
@pgrade char(1)
)
AS
Update tempscrub
set prate = Case @pgrade
when '3'
then
Left(Rtrim(prate), 2) + 'N' + Right(RTrim(prate),2)
else
Left(Rtrim(prate), 2) + 'N' + Right(RTrim(prate),1)
end
where tempscrub.pgrade = @pgrade
and ssn +uic+pgrade IN(Select ssn+uic +pgrade
From tempscrub, navm
where tempscrub.ssn = navm.nm_ssn
and navm.nm_ecd in('d100','d101','d102','d133','d130','d131','d134','d132','d110','d111','d112'))
Go

Third
CREATE Procedure SPScrubListGen
(
@cycle char(3),
@pgrade char(1),
@termeldt char(8)
)
AS
Insert into Scrublist(ssn, name, uic, cycle, pgrade, prate, erate, earlies, pguic, OrderFlag)
Select Distinct ssn, tname, uic, cycle, pgrade, tempscrub.prate, Rtrim(progression.erate), 'X', pguic, 'Y'
from tempscrub, progression
where pgrade = @pgrade
and cycle = @cycle
and progression.prate = tempscrub.prate
and datediff(mm, tirdate, @termeldt) >=
Case @pgrade
when '3'
then 6
when '4'
then 12
when '5'
then 36
when '6'
then 36
end;
Update Scrublist
Set pgrade =
Case @pgrade
when '3'
then 'E4'
when '4'
then 'E5'
when '5'
then 'E6'
when '6'
then 'E7'
end;

GO

Basically the amount of records this can go through is Min 20k to max 200k.

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-08-25 : 15:08:44

quote:
Originally posted by nr

What do you think the dts execute sql task does?
You should also try writing other applications to call the SP and see if that helps too.

Well im using ColdFusion to call the sproc. It works well. I have use Asp .Net as well and it goes at the same speed.

gwhiz
Yak Posting Veteran

78 Posts

Posted - 2003-08-25 : 15:48:03

I notice you have a few IN's I would change these to exists and not exists, this may help it run faster.
ie.

Insert into TempScrub(tname, ssn, cycle, uic, pguic, pgrade, prate,tirdate)
Select distinct nm_name, nm_ssn, @cycle, nm_uic, nm_puic, nm_ppg, nm_prate, nm_tirdate
From navm, cyclecontrol
where cc_cycleno = @cycle
AND nm_ppg = @pgrade
AND exists(Select uic from uics where uic=nm_uic )
AND not exists(Select uic from scrublist where uic+ssn =nm_ssn+nm_uic )
And nm_brcl <> '09Z'
AND nm_spig <> 'J'
AND not exists(Select ssn from neasrpts
where cycle = @prevcycle
AND nm_ssn = ssn
AND npg = @pgrade
AND ((pc >= '01' and pc <= '15')))
AND isdate(nm_tirdate) = 1;
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-25 : 15:50:07

Why do you think dts would be any different?
These applications all connect to the server and run the SP.
The only difference would be in the time it takes them to create the connection and any connection properties (ansi warnings, ...) and whether it runs inside a transaction.
You then have the time taken to return the result which will depend on the connection library and again the connection properties (cursor type, ..) and the processing capabilities of the app.

The SP SPUpdateNucs could maybe benefit from a join rather than an in clause.
Check the query plans and make sure they are using indexes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-08-26 : 07:03:54

The only reason i thought it may be faster is due to the fact that I used dts to do an extract(export) routine in about 2 minutes, that took everything, except cobol and c++, to do in over 30 minutes.

Would it be beneficial to combine these three sprocs?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources