asked on

Stored proc performance issue using multiple linked servers in Sql Server 2008

I am trying to create a stored proc in SSMS 2008 and I'm having a performance issue. We've had accounts move from one SOR to another but not all the data transferred so I am having to use 2 linked servers (DB2 & TD-PROD) to get the data.
To build the #Acct_Base only takes a couple of seconds. The performance issue is coming from the #PMNT. There is a date that I need to use from #Acct_Base in the WHERE to get data for #PMNT but after 2 hours of running I cancel it.

How can I make this faster?

If OBJECT_ID(N'tempdb..#Acct_Base', N'U') Is Not Null
      Drop Table #Acct_Base

SELECT *
INTO #Acct_Base
FROM OPENQUERY(DB2,
'WITH LGL_STIP AS (
a.DEBTOR_NO
,a.FRST_PMT_DT
,a.ACCT_NUM
,ect
,ect......................
')

If OBJECT_ID(N'tempdb..#PMNT', N'U') Is Not Null
      Drop Table #PMNT

select x.* into #PMNT from
(SELECT distinct a.DEBTOR_NO
,a.FRST_PMT_DT
,a.ACCT_NUM
      ,p.LN_NO
      ,p.CUST_ACCT_ID
      ,p.DELTA_TRAN
      ,p.TRAN_EFCT_DT
      ,p.SEQUENCE
      ,p.PRIN_BAL_AM
      ,p.TOTAL_RECD
      ,p.INT_CHRG_AM
      ,p.TTLPMTS
from #Acct_Base a
left join openquery([TD-PROD],'Select
CLIENT_NO
,LN_NO
,CLIENT_NO ||''-''|| LN_NO as CUST_ACCT_ID
,DELTA_TRAN
,DELTA_PROC_DATE as TRAN_EFCT_DT
,SEQUENCE
,PRIN_PD as PRIN_BAL_AM
,TOTAL_RECD
,INT_PD as INT_CHRG_AM
,SUM(PRIN_PD + INT_PD) AS TTLPMTS
FROM EIW_S1.DLTALH02_FUNDS_PROC_CS
WHERE DELTA_TRAN in (170,175)
GROUP BY
CLIENT_NO
,LN_NO
,DELTA_TRAN
,DELTA_PROC_DATE
,SEQUENCE
,PRIN_PD
,TOTAL_RECD
,INT_PD

')       as p
on p.LN_NO = a.ACCT_NUM
where p.TRAN_EFCT_DT >= a.FRST_PMT_DT

)x

Nitin Sontakke

Not sure how much control you have over TD-PROD. Will you not be able to create a view there of the query you have?

Again not sure how much of data volume you are talking here. Can you not fire that query and have it's output in yet another temp table. And then join those two tables which are now local.

DCUnited

ASKER

I initially tried to make the #PMNT a temp table on it's own but it still took forever because it's loading all the history (about 20 years worth) and not just where it's within the p.TRAN_EFCT_DT >= a.FRST_PMT_DT which would limit the output considerably.

Nitin Sontakke

How about view....?

Again...just contemplating...can you not take the min of that date in a variable and use it to limit your insert into #PMNT?

Nitin Sontakke

Disclaimer: I have never used these constructs, so i am unaware of their limitations. As in can a dynamic sql work? Are parameters allowed? etc.

DCUnited

ASKER

I was trying to think of a way to do a dynamic sql/paramter but I don't have a lot of experience with that. Is there a way to make a @FPDate to equal a.FRST_PMT_DT (this could be any date based on account, all accounts could have different dates) so it would be p.TRAN_EFCT_DT >= @FPDate? I know syntax can be different across platforms. This would be in Teradata

Nitin Sontakke

I don't know either. You may wish to add teradata as a topic to the question.

Zberteoc

How long takes the openquery if you run:

select 
	*
from
	openquery([TD-PROD],'Select
	 CLIENT_NO
	,LN_NO
	,CLIENT_NO ||''-''|| LN_NO as CUST_ACCT_ID
	,DELTA_TRAN      
	,DELTA_PROC_DATE as TRAN_EFCT_DT
	,SEQUENCE
	,PRIN_PD as PRIN_BAL_AM
	,TOTAL_RECD
	,INT_PD as INT_CHRG_AM
	,SUM(PRIN_PD + INT_PD) AS TTLPMTS
FROM 
	EIW_S1.DLTALH02_FUNDS_PROC_CS
WHERE 
	DELTA_TRAN in (170,175) 
GROUP BY
	 CLIENT_NO
	,LN_NO
	,DELTA_TRAN      
	,DELTA_PROC_DATE
	,SEQUENCE
	,PRIN_PD
	,TOTAL_RECD
	,INT_PD
') as p

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.