Rewriting scripts to improve runtime

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

SQL Server Development (2000)

Rewriting scripts to improve runtime

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2007-11-14 : 00:38:10

Hello All,

I have a user-defined function that been working for quite some time now, recently the Oracle team upgraded from Oracle 8i to Oracle 9i. The process that used to take about 10 minutes to run now takes about 45 minutes to run. Below is the user-defined function and the DTS that runs it. Does anyone know how I can rewrite this process to improve runtime? Also does anyone know if I needed to install Oracle 9i client to my SQL Server box, because I’m still using the Oracle 8i client to connect to the Oracle 9i db? Please advice. Thanks.

-----------------------------------
DECLARE @server varchar(50)
DECLARE @dtRptDateDetail datetime
DECLARE @sqlString varchar(8000)

SET @server='IGS_IL'
SET @dtRptDateDetail=round(convert(float,Getdate()-1),0,1)
SET @sqlString=dbo.usf_igs_sql_string(@server,@dtRptDateDetail)

PRINT @dtRptDateDetail
EXEC (@sqlString)

---------------------------------

CREATE FUNCTION dbo.usf_IGS_SQL_STRING(@strIGS varchar(10), @dtRptDateDetail datetime)
RETURNS varchar(5000)
AS
BEGIN
DECLARE @dtRptDateDetailPlusOne datetime
SET @dtRptDateDetailPlusOne=dateadd(d,1,@dtRptDateDetail)
DECLARE @strSQL varchar(5000)
SET @strSQL='INSERT INTO dbo.igs_staging2
(RL_TRACE, SITE_CODE, AA_STAFF, AA_TIME_STAMP, AA_TYPE, AA_LINK_ID, AA_DIGIT_TYPE, AA_DIGITS, AA_DNIS_DIGITS, AA_CALL_DURATION, AA_TALK_TIME, AA_HOLD_TIME, AA_APPL_ID, AA_AGRP_ID, AA_ACTIVITY, AA_LINE_INSTANCE_TEXT, AA_DIGIT_TYPE_TEXT, AA_NOTES_TEXT, TRANSACTION_CODE_NAME, STAFF_NAME, STAFF, SUPERVISOR, STAFF_TYPE, SUP_NAME, AA_REC_DISPOSITION, AA_LINE_INSTANCE)
SELECT ''' + @strIGS + ''' RL_TRACE, * FROM openquery(' + @strIGS +',''SELECT
VW_CFG_SYSTEM.SITE_CODE,
VW_EVT_AGENT_ACTIVITY.AA_STAFF, VW_EVT_AGENT_ACTIVITY.AA_TIME_STAMP, VW_EVT_AGENT_ACTIVITY.AA_TYPE, VW_EVT_AGENT_ACTIVITY.AA_LINK_ID, VW_EVT_AGENT_ACTIVITY.AA_DIGIT_TYPE, VW_EVT_AGENT_ACTIVITY.AA_DIGITS, VW_EVT_AGENT_ACTIVITY.AA_DNIS_DIGITS, VW_EVT_AGENT_ACTIVITY.AA_CALL_DURATION, VW_EVT_AGENT_ACTIVITY.AA_TALK_TIME, VW_EVT_AGENT_ACTIVITY.AA_HOLD_TIME, VW_EVT_AGENT_ACTIVITY.AA_APPL_ID, VW_EVT_AGENT_ACTIVITY.AA_AGRP_ID, VW_EVT_AGENT_ACTIVITY.AA_ACTIVITY, VW_EVT_AGENT_ACTIVITY.AA_LINE_INSTANCE_TEXT, VW_EVT_AGENT_ACTIVITY.AA_DIGIT_TYPE_TEXT, VW_EVT_AGENT_ACTIVITY.AA_NOTES_TEXT,
VW_CFG_TRANSACTION_CODE.TRANSACTION_CODE_NAME,
VW_CFG_STAFF.STAFF_NAME, VW_CFG_STAFF.STAFF, VW_CFG_STAFF.SUPERVISOR,
VW_CFG_STAFF_SUPV.STAFF_TYPE, VW_CFG_STAFF_SUPV.STAFF_NAME "SUP_NAME",
VW_EVT_AGENT_ACTIVITY.AA_REC_DISPOSITION,
VW_EVT_AGENT_ACTIVITY.AA_LINE_INSTANCE
FROM
IGS.VW_CFG_SYSTEM VW_CFG_SYSTEM,
IGS.VW_EVT_AGENT_ACTIVITY VW_EVT_AGENT_ACTIVITY,
IGS.VW_CFG_TRANSACTION_CODE VW_CFG_TRANSACTION_CODE,
IGS.VW_CFG_STAFF VW_CFG_STAFF,
IGS.VW_CFG_STAFF VW_CFG_STAFF_SUPV
WHERE
VW_CFG_SYSTEM.SITE = VW_EVT_AGENT_ACTIVITY.AA_SITE AND
VW_EVT_AGENT_ACTIVITY.AA_SITE = VW_CFG_TRANSACTION_CODE.SITE (+) AND
VW_EVT_AGENT_ACTIVITY.AA_DNIS_DIGITS = VW_CFG_TRANSACTION_CODE.TRANSACTION_CODE (+) AND
VW_EVT_AGENT_ACTIVITY.AA_DNIS_VERSION = VW_CFG_TRANSACTION_CODE.TRANSACTION_CODE_VERSION (+) AND
VW_EVT_AGENT_ACTIVITY.AA_SITE = VW_CFG_STAFF.SITE AND
VW_EVT_AGENT_ACTIVITY.AA_STAFF = VW_CFG_STAFF.STAFF AND
VW_EVT_AGENT_ACTIVITY.AA_STAFF_VERSION = VW_CFG_STAFF.STAFF_VERSION AND
VW_CFG_STAFF.SITE = VW_CFG_STAFF_SUPV.SITE AND
VW_CFG_STAFF.SUPERVISOR = VW_CFG_STAFF_SUPV.STAFF AND
VW_CFG_STAFF.SUPERVISOR_VERSION = VW_CFG_STAFF_SUPV.STAFF_VERSION AND
VW_EVT_AGENT_ACTIVITY.AA_TIME_STAMP >= TO_DATE(''''' + CONVERT(varchar(12),@dtRptDateDetail,101) + ''''',''''MM/DD/YYYY'''') AND
VW_EVT_AGENT_ACTIVITY.AA_TIME_STAMP < TO_DATE(''''' + CONVERT(varchar(12),@dtRptDateDetailPlusOne,101) + ''''',''''MM/DD/YYYY'''') AND
VW_CFG_STAFF_SUPV.STAFF_TYPE = 2'')'
RETURN @strSQL
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources