poorly written stored proc? - 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

Transact-SQL (2000)

poorly written stored proc?

Author

Topic

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2007-05-17 : 08:45:06

Hi

We had a query that was timing out in our development environment. the server has default local and remote timeouts, we dont really want to change these as have to look after about 300 hundred SQL servers and we attempt to stick defaults as much as possible when it come to low level settings in sp_configure or the whole environment becomes unmanageable. I said the query was timing out due to a low spec server and a poorly written stored proc. Now somehow its my job to sort the stored proc out, oh brother! I'm no developer and although can write a few system stored procs i can't sort this out. my suggestion for the developers to break it down into smaller stored procs on the local and remote server didn't go down to well :(

this is a desperate attempt... apologies for attaching the whole thing! Any help muchly appreciated

CREATE PROCEDURE dbo.up_trdInsertMillenniumTrans
(@Id char(9))
AS

--Setup Environment
SET NOCOUNT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

--Declare Variables
DECLARE @Error int
DECLARE @ErrMsg varchar(350)
DECLARE @ProcName varchar(30)
DECLARE @SQLpassthru varchar(8000)
--Standard startup info
SELECT @ProcName = object_name(@@PROCID)

exec('DECLARE @TempMill TABLE
(
[AuditReference] [int] NULL ,
[DealRef] [char] (9) NULL ,
[TradeType] [char] (4) NULL ,
[AuthorisedInd] [char] (1) NULL ,
[FlaggedToMatureInd] [char] (1) NULL ,
[FlowDateOut] [datetime] NULL ,
[FlowDateIn] [datetime] NULL ,
[TradeDate] [datetime] NULL ,
[RecieveInterestRate] [decimal](20, 7) NULL ,
[PayInterestRate] [decimal](20, 7) NULL ,
[SpotFXrate] [decimal](20, 7) NULL ,
[Points] [decimal](20, 7) NULL ,
[OutrightRate] [decimal](20, 7) NULL ,
[RecieveDayBasis] [int] NULL ,
[PayAmount] [money] NULL ,
[PayCurrency] [char] (3) NULL ,
[RecieveAmount] [money] NULL ,
[RecieveCurrency] [char] (3) NULL ,
[RecieveFullTradeAmount] [money] NULL ,
[PayFullTradeAmount] [money] NULL ,
[HedgeSpotInd] [varchar] (10) NULL ,
[PayFromLegalEnt] [char] (20) NULL ,
[PayFromBank] [varchar] (50) NULL ,
[PayFromBankSwiftorSortCode] [varchar] (50) NULL ,
[PayFromBankAccountName] [varchar] (50) NULL ,
[PayFromBankAccountNumber] [varchar] (50) NULL ,
[PayFromBankAttensionof] [varchar] (50) NULL ,
[PayBankCode] [varchar] (50) NULL ,
[PayViaBankCode] [varchar] (50) NULL ,
[PayViaBankswiftCode] [varchar] (50) NULL ,
[PayViaBankName] [varchar] (50) NULL ,
[PayViaBankAddress] [varchar] (50) NULL ,
[PayViaBankAccountName] [varchar] (50) NULL ,
[PayViaBankAccountNumber] [varchar] (50) NULL ,
[PayViaBankCountry] [varchar] (50) NULL ,
[CounterPartyName] [varchar] (50) NULL ,
[CounterPartyLegalEnt] [varchar] (50) NULL ,
[CounterPartyBanksName] [varchar] (50) NULL ,
[CounterPartyBanksSwiftorSortCode] [varchar] (50) NULL ,
[CounterPartyBanksAddress1] [varchar] (50) NULL ,
[CounterPartyBanksAddress2] [varchar] (50) NULL ,
[CounterPartyBanksAddress3] [varchar] (50) NULL ,
[CounterPartyBanksAccountName] [varchar] (50) NULL ,
[CounterPartyBanksAccountNumber] [varchar] (50) NULL ,
[CounterPartyBanksAttensionOf] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankCode] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankswiftCode] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankName] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankAddress] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankAccountName] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankAccountNumber] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankCountry] [varchar] (50) NULL ,
[RecieveBankName] [varchar] (50) NULL ,
[RecieveBankAddress1] [varchar] (50) NULL ,
[RecieveBankAddress2] [varchar] (50) NULL ,
[RecieveBankAddress3] [varchar] (50) NULL ,
[RecieveBankSwiftCode] [varchar] (50) NULL ,
[RecieveBankAccountName] [varchar] (50) NULL ,
[RecieveBankAccountNumber] [varchar] (50) NULL ,
[RecieveBankAttentionOf] [varchar] (50) NULL ,
[TAMComments] [varchar] (100) NULL ,
[BeneficiaryComments1] [varchar] (100) NULL ,
[BeneficiaryComments2] [varchar] (100) NULL ,
[EnteredUserID] [char] (4) NULL ,
[VerifyUserID] [char] (4) NULL ,
[AuthoriseUserID] [char] (4) NULL ,
[SequenceNumber] [int] NULL ,
[HoldsCodeForRoles] [char] (9) NULL ,
[PostedToHeliograph] [char] (1) NULL ,
[AddedBy] [varchar] (50) NULL ,
[AddedDatetime] [varchar] (16) NULL ,
[UpdatedBy] [varchar] (50) NULL ,
[UpdatedDatetime] [varchar] (16) NULL ,
[SerialNumber] [bigint] IDENTITY (1, 1) NOT NULL ,
[ExtractedForHiportfolio] [varchar] (1) NOT NULL ,
[AuditDate] [datetime] NULL ,
[AuditTime] [datetime] NULL ,
[MaturityDate] [datetime] NULL
)

INSERT INTO @TempMill(
AuditReference ,
DealRef ,
TradeType ,
AuthorisedInd ,
FlaggedToMatureInd ,
FlowDateOut ,
FlowDateIn ,
TradeDate ,
RecieveInterestRate ,
PayInterestRate ,
SpotFXrate ,
Points ,
OutrightRate ,
RecieveDayBasis ,
PayAmount ,
PayCurrency ,
RecieveAmount ,
RecieveCurrency ,
RecieveFullTradeAmount ,
PayFullTradeAmount ,
HedgeSpotInd ,
PayFromLegalEnt ,
PayFromBank ,
PayFromBankSwiftorSortCode ,
PayFromBankAccountName ,
PayFromBankAccountNumber ,
PayFromBankAttensionof ,
PayBankCode ,
PayViaBankCode ,
PayViaBankswiftCode ,
PayViaBankName ,
PayViaBankAddress ,
PayViaBankAccountName ,
PayViaBankAccountNumber ,
PayViaBankCountry ,
CounterPartyName ,
CounterPartyLegalEnt ,
CounterPartyBanksName ,
CounterPartyBanksSwiftorSortCode ,
CounterPartyBanksAddress1 ,
CounterPartyBanksAddress2 ,
CounterPartyBanksAddress3 ,
CounterPartyBanksAccountName ,
CounterPartyBanksAccountNumber ,
CounterPartyBanksAttensionOf ,
CounterPartyRecieveViaBankCode ,
CounterPartyRecieveViaBankswiftCode ,
CounterPartyRecieveViaBankName ,
CounterPartyRecieveViaBankAddress ,
CounterPartyRecieveViaBankAccountName ,
CounterPartyRecieveViaBankAccountNumber ,
CounterPartyRecieveViaBankCountry ,
RecieveBankName ,
RecieveBankAddress1 ,
RecieveBankAddress2 ,
RecieveBankAddress3 ,
RecieveBankSwiftCode ,
RecieveBankAccountName ,
RecieveBankAccountNumber ,
RecieveBankAttentionOf ,
TAMComments ,
BeneficiaryComments1 ,
BeneficiaryComments2 ,
EnteredUserID ,
VerifyUserID ,
AuthoriseUserID ,
SequenceNumber ,
HoldsCodeForRoles ,
AuditDate ,
AuditTime ,
MaturityDate ,
PostedToHeliograph ,
ExtractedForHiportfolio ,
AddedBy ,
AddedDatetime)
SELECT
AuditReference,
DealRef,
TradeType,
AuthorisedInd,
FlaggedToMatureInd,
FlowDateOut,
FlowDateIn,
TradeDate,
ReceiveInterestRate,
PayInterestRate,
SpotFXRate,
Points,
OutrightRate,
ReceiveDayBasis,
PayAmount,
PayCurrency,
ReceiveAmount,
ReceiveCurrency,
ReceiveFulltradeAmount,-- full trade amount for matching
PayFullTradeAmount,-- full trade amount for matching
HedgeSpotInd,
PayFromLegalEnt,
PayFromBank,
PayFromBankSwiftOrSortCode,
PayFromBankAccountName,
PayFromBankAccountNumber,
PayFromBankAttentionOf,
PayBankCode,
PayViaBankCode,
PayViaBankSwiftCode,
PayViaBankName,
PayViaBankAddress,
PayViaBankAccountName,
PayViaBankAcountNumber,
PayViaBankCountry,
CounterPartyName,
CounterPartyLegalEnt,
CounterPartyBanksName,
CounterPartyBankSwiftOrSortCode,
CounterPartyBanksAddress1,
CounterPartyBanksAddress2,
CounterPartyBanksAddress3,
CounterPartyBanksAccountName,
CounterPartyBanksAccountNumber,
CounterPartyBanksAttentionOf,
CounterPartyReceiveViaBankCode,
CounterPartyReceiveViaBankSwiftCode,
CounterPartyReceiveViaBankName,
CounterPartyReceiveViaBankAddress,
CounterPartyReceiveViaBankAccountName,
CounterPartyReceiveViaBankAccountNumber,
CounterPartyReceiveViaBankCountry,
ReceiveBankName,
ReceiveBankAddress1,
ReceiveBankAddress2,
ReceiveBankAddress3,
ReceiveBankSwiftCode,
ReceiveBankAccountName,
ReceiveBankAccountNumber,
ReceiveBankAttentionOf,
TAMComments,
BeneficiaryComments1,
BeneficiaryComments2,
EnteredUserID,
VerifyUserID,
AuthoriseUserID,
SequenceNumber,
HoldsCodeForRolls,
AuditDate,
AuditTime,
MaturityDate,
''N'',
''N'',
suser_sname(),
dbo.uf_ConvertToLZDate(getdate())

FROM OPENQUERY( MILLENNIUM, ''SELECT
T3.xAudref AuditReference,
RTRIM(T1.xRefNR) DealRef,
RTRIM(T3.xTType) TradeType,
CASE SUBSTRING(T3.xLogix,16,1)
WHEN 2 THEN ''''Y''''
ELSE ''''N''''
END AuthorisedInd,

CASE SUBSTRING(T3.xLogix,14,1)
WHEN 1 THEN ''''Y''''
ELSE ''''N''''
END FlaggedToMatureInd,

cast(T1.xTrxDate as datetime) FlowDateOut,
cast(T2.xTrxDate as datetime) FlowDateIn,
cast (T3.xTradeDate as datetime) TradeDate,
T3.xRIR ReceiveInterestRate,
T3.xPIR PayInterestRate,
T3.xArrFee SpotFXRate,
T3.XReval/10000 Points,
T3.xReval/10000 + T3.xArrFee OutrightRate,
T3.xRDB ReceiveDayBasis,
T1.xAmount PayAmount,
RTRIM(T1.xCCYCode) PayCurrency,
T2.xAmount ReceiveAmount,
RTRIM(T2.xCCYCode) ReceiveCurrency,
T3.xRPR ReceiveFulltradeAmount,
T3.xPPR PayFullTradeAmount,
RTRIM(T3.xBook) HedgeSpotInd,
RTRIM(T1.xLegal) PayFromLegalEnt,
RTRIM(T1.xFromBank) PayFromBank,
CASE T3.xTType
WHEN ''''DEPP''''
THEN RTRIM(T2.xSWCode)
ELSE RTRIM(T3.xPaysCode)
END PayFromBankSwiftOrSortCode,

RTRIM(T1.xFromAcName) PayFromBankAccountName,
RTRIM(T1.xAcctNR) PayFromBankAccountNumber,
RTRIM(T1.xPayCode) PayFromBankAttentionOf,
RTRIM(T1.xBankCode) PayBankCode,
RTRIM(T1.xBB_Code) PayViaBankCode,
RTRIM(T1.xBB_Swift) PayViaBankSwiftCode,
RTRIM(T1.xBB_Name) PayViaBankName,
RTRIM(T1.xBB_Address) PayViaBankAddress,
RTRIM(T1.xBB_City) PayViaBankAccountName,
null PayViaBankAcountNumber,
RTRIM(T1.xBB_Country) PayViaBankCountry,
RTRIM(T3.XCTP) CounterPartyName,
RTRIM(T2.xLegal) CounterPartyLegalEnt,
RTRIM(T1.xName) CounterPartyBanksName,
RTRIM(T1.xSWCode) CounterPartyBankSwiftOrSortCode,
RTRIM(T1.xPayToAddr1) CounterPartyBanksAddress1,
RTRIM(T1.xPayToAddr2) CounterPartyBanksAddress2,
RTRIM(T1.xPayToAddr3) CounterPartyBanksAddress3,
RTRIM(T1.xAccNameTo) CounterPartyBanksAccountName,
RTRIM(T1.xAcctNrTo) CounterPartyBanksAccountNumber,
RTRIM(T2.xPayCode) CounterPartyBanksAttentionOf,
RTRIM(T2.xBB_Code) CounterPartyReceiveViaBankCode,
RTRIM(T2.xBB_Swift) CounterPartyReceiveViaBankSwiftCode,
RTRIM(T2.xBB_Name) CounterPartyReceiveViaBankName,
RTRIM(T2.xBB_Address) CounterPartyReceiveViaBankAddress,
RTRIM(T2.xBB_City) CounterPartyReceiveViaBankAccountName,
null CounterPartyReceiveViaBankAccountNumber,
RTRIM(T2.xBB_Country) CounterPartyReceiveViaBankCountry,
RTRIM(T3.xRecBankNm) ReceiveBankName,
RTRIM(T3.xRecAddr1) ReceiveBankAddress1,
RTRIM(T3.xRecAddr2) ReceiveBankAddress2,
RTRIM(T3.xRecAddr3) ReceiveBankAddress3,
RTRIM(T3.xRecsCode) ReceiveBankSwiftCode,
RTRIM(T3.xRecAcName) ReceiveBankAccountName,
RTRIM(T3.xRecAcNumb) ReceiveBankAccountNumber,
RTRIM(T3.xRecAttn) ReceiveBankAttentionOf,
RTRIM(T3.xComment) TAMComments,
RTRIM(T1.xDesc1) BeneficiaryComments1,
RTRIM(T1.xCostAcc) BeneficiaryComments2,
RTRIM(T3.xTransactor) EnteredUserID,
LEFT(RTRIM(T3.xUserID),4) VerifyUserID,
RTRIM(T3.xAuthority) AuthoriseUserID,
RTRIM(T1.xSequenceNR) SequenceNumber,
RTRIM(T3.xRAccr) HoldsCodeForRolls,
CAST(T6.xDate as datetime) AuditDate,
CAST(T6.xTime as datetime) AuditTime,
CAST(T3.xMAT as datetime) MaturityDate
FROM
Richmond.richmond.xEFTTRxs T1 JOIN
Richmond.richmond.xTransact T3 ON (T1.xRefNR = T3.xDealRef)
JOIN Richmond.richmond.xAud T6 ON (T3.xAudRef = T6.xReference)
LEFT OUTER JOIN (SELECT *
FROM Richmond.richmond.xEFTTRXS
WHERE xDesc4 = ''''MATURITY'''') T2
ON (T1.xRefNR = T2.xRefNR)
WHERE
T1.xRefNR = ''''' + @Id + '''''
AND T1.xDesc4 = ''''STARTDATE''''
AND T3.xTType in (''''DEPP'''')
AND (SUBSTRING(T3.xLogix,16,1) = 2
OR SUBSTRING(T3.XLogix,14,1) = 1)'')
INSERT INTO trd_MillenniumTrans (
AuditReference ,
DealRef ,
TradeType ,
AuthorisedInd ,
FlaggedToMatureInd ,
FlowDateOut ,
FlowDateIn ,
TradeDate ,
RecieveInterestRate ,
PayInterestRate ,
SpotFXrate ,
Points ,
OutrightRate ,
RecieveDayBasis ,
PayAmount ,
PayCurrency ,
RecieveAmount ,
RecieveCurrency ,
RecieveFullTradeAmount ,
PayFullTradeAmount ,
HedgeSpotInd ,
PayFromLegalEnt ,
PayFromBank ,
PayFromBankSwiftorSortCode ,
PayFromBankAccountName ,
PayFromBankAccountNumber ,
PayFromBankAttensionof ,
PayBankCode ,
PayViaBankCode ,
PayViaBankswiftCode ,
PayViaBankName ,
PayViaBankAddress ,
PayViaBankAccountName ,
PayViaBankAccountNumber ,
PayViaBankCountry ,
CounterPartyName ,
CounterPartyLegalEnt ,
CounterPartyBanksName ,
CounterPartyBanksSwiftorSortCode ,
CounterPartyBanksAddress1 ,
CounterPartyBanksAddress2 ,
CounterPartyBanksAddress3 ,
CounterPartyBanksAccountName ,
CounterPartyBanksAccountNumber ,
CounterPartyBanksAttensionOf ,
CounterPartyRecieveViaBankCode ,
CounterPartyRecieveViaBankswiftCode ,
CounterPartyRecieveViaBankName ,
CounterPartyRecieveViaBankAddress ,
CounterPartyRecieveViaBankAccountName ,
CounterPartyRecieveViaBankAccountNumber ,
CounterPartyRecieveViaBankCountry ,
RecieveBankName ,
RecieveBankAddress1 ,
RecieveBankAddress2 ,
RecieveBankAddress3 ,
RecieveBankSwiftCode ,
RecieveBankAccountName ,
RecieveBankAccountNumber ,
RecieveBankAttentionOf ,
TAMComments ,
BeneficiaryComments1 ,
BeneficiaryComments2 ,
EnteredUserID ,
VerifyUserID ,
AuthoriseUserID ,
SequenceNumber ,
HoldsCodeForRoles ,
AuditDate ,
AuditTime ,
MaturityDate ,
PostedToHeliograph ,
ExtractedForHiportfolio ,
AddedBy ,
AddedDatetime
)
SELECT
AuditReference ,
DealRef ,
TradeType ,
AuthorisedInd ,
FlaggedToMatureInd ,
FlowDateOut ,
FlowDateIn ,
TradeDate ,
RecieveInterestRate ,
PayInterestRate ,
SpotFXrate ,
Points ,
OutrightRate ,
RecieveDayBasis ,
PayAmount ,
PayCurrency ,
RecieveAmount ,
RecieveCurrency ,
RecieveFullTradeAmount ,
PayFullTradeAmount ,
HedgeSpotInd ,
PayFromLegalEnt ,
PayFromBank ,
PayFromBankSwiftorSortCode ,
PayFromBankAccountName ,
PayFromBankAccountNumber ,
PayFromBankAttensionof ,
PayBankCode ,
PayViaBankCode ,
PayViaBankswiftCode ,
PayViaBankName ,
PayViaBankAddress ,
PayViaBankAccountName ,
PayViaBankAccountNumber ,
PayViaBankCountry ,
CounterPartyName ,
CounterPartyLegalEnt ,
CounterPartyBanksName ,
CounterPartyBanksSwiftorSortCode ,
CounterPartyBanksAddress1 ,
CounterPartyBanksAddress2 ,
CounterPartyBanksAddress3 ,
CounterPartyBanksAccountName ,
CounterPartyBanksAccountNumber ,
CounterPartyBanksAttensionOf ,
CounterPartyRecieveViaBankCode ,
CounterPartyRecieveViaBankswiftCode ,
CounterPartyRecieveViaBankName ,
CounterPartyRecieveViaBankAddress ,
CounterPartyRecieveViaBankAccountName ,
CounterPartyRecieveViaBankAccountNumber ,
CounterPartyRecieveViaBankCountry ,
RecieveBankName ,
RecieveBankAddress1 ,
RecieveBankAddress2 ,
RecieveBankAddress3 ,
RecieveBankSwiftCode ,
RecieveBankAccountName ,
RecieveBankAccountNumber ,
RecieveBankAttentionOf ,
TAMComments ,
BeneficiaryComments1 ,
BeneficiaryComments2 ,
EnteredUserID ,
VerifyUserID ,
AuthoriseUserID ,
SequenceNumber ,
HoldsCodeForRoles ,
AuditDate ,
AuditTime ,
MaturityDate ,
PostedToHeliograph ,
ExtractedForHiportfolio ,
AddedBy ,
AddedDatetime

FROM
@TempMill
')

exec('DECLARE @TempMill TABLE
(
[AuditReference] [int] NULL ,
[DealRef] [char] (9) NULL ,
[TradeType] [char] (4) NULL ,
[AuthorisedInd] [char] (1) NULL ,
[FlaggedToMatureInd] [char] (1) NULL ,
[FlowDateOut] [datetime] NULL ,
[FlowDateIn] [datetime] NULL ,
[TradeDate] [datetime] NULL ,
[RecieveInterestRate] [decimal](20, 7) NULL ,
[PayInterestRate] [decimal](20, 7) NULL ,
[SpotFXrate] [decimal](20, 7) NULL ,
[Points] [decimal](20, 7) NULL ,
[OutrightRate] [decimal](20, 7) NULL ,
[RecieveDayBasis] [int] NULL ,
[PayAmount] [money] NULL ,
[PayCurrency] [char] (3) NULL ,
[RecieveAmount] [money] NULL ,
[RecieveCurrency] [char] (3) NULL ,
[RecieveFullTradeAmount] [money] NULL ,
[PayFullTradeAmount] [money] NULL ,
[HedgeSpotInd] [varchar] (10) NULL ,
[PayFromLegalEnt] [char] (20) NULL ,
[PayFromBank] [varchar] (50) NULL ,
[PayFromBankSwiftorSortCode] [varchar] (50) NULL ,
[PayFromBankAccountName] [varchar] (50) NULL ,
[PayFromBankAccountNumber] [varchar] (50) NULL ,
[PayFromBankAttensionof] [varchar] (50) NULL ,
[PayBankCode] [varchar] (50) NULL ,
[PayViaBankCode] [varchar] (50) NULL ,
[PayViaBankswiftCode] [varchar] (50) NULL ,
[PayViaBankName] [varchar] (50) NULL ,
[PayViaBankAddress] [varchar] (50) NULL ,
[PayViaBankAccountName] [varchar] (50) NULL ,
[PayViaBankAccountNumber] [varchar] (50) NULL ,
[PayViaBankCountry] [varchar] (50) NULL ,
[CounterPartyName] [varchar] (50) NULL ,
[CounterPartyLegalEnt] [varchar] (50) NULL ,
[CounterPartyBanksName] [varchar] (50) NULL ,
[CounterPartyBanksSwiftorSortCode] [varchar] (50) NULL ,
[CounterPartyBanksAddress1] [varchar] (50) NULL ,
[CounterPartyBanksAddress2] [varchar] (50) NULL ,
[CounterPartyBanksAddress3] [varchar] (50) NULL ,
[CounterPartyBanksAccountName] [varchar] (50) NULL ,
[CounterPartyBanksAccountNumber] [varchar] (50) NULL ,
[CounterPartyBanksAttensionOf] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankCode] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankswiftCode] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankName] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankAddress] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankAccountName] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankAccountNumber] [varchar] (50) NULL ,
[CounterPartyRecieveViaBankCountry] [varchar] (50) NULL ,
[RecieveBankName] [varchar] (50) NULL ,
[RecieveBankAddress1] [varchar] (50) NULL ,
[RecieveBankAddress2] [varchar] (50) NULL ,
[RecieveBankAddress3] [varchar] (50) NULL ,
[RecieveBankSwiftCode] [varchar] (50) NULL ,
[RecieveBankAccountName] [varchar] (50) NULL ,
[RecieveBankAccountNumber] [varchar] (50) NULL ,
[RecieveBankAttentionOf] [varchar] (50) NULL ,
[TAMComments] [varchar] (100) NULL ,
[BeneficiaryComments1] [varchar] (100) NULL ,
[BeneficiaryComments2] [varchar] (100) NULL ,
[EnteredUserID] [char] (4) NULL ,
[VerifyUserID] [char] (4) NULL ,
[AuthoriseUserID] [char] (4) NULL ,
[SequenceNumber] [int] NULL ,
[HoldsCodeForRoles] [char] (9) NULL ,
[PostedToHeliograph] [char] (1) NULL ,
[AddedBy] [varchar] (50) NULL ,
[AddedDatetime] [varchar] (16) NULL ,
[UpdatedBy] [varchar] (50) NULL ,
[UpdatedDatetime] [varchar] (16) NULL ,
[SerialNumber] [bigint] IDENTITY (1, 1) NOT NULL ,
[ExtractedForHiportfolio] [varchar] (1) NOT NULL ,
[AuditDate] [datetime] NULL ,
[AuditTime] [datetime] NULL ,
[MaturityDate] [datetime] NULL
)

INSERT INTO @TempMill(
AuditReference ,
DealRef ,
TradeType ,
AuthorisedInd ,
FlaggedToMatureInd ,
FlowDateOut ,

FlowDateIn ,
TradeDate ,
RecieveInterestRate ,
PayInterestRate ,
SpotFXrate ,
Points ,
OutrightRate ,
RecieveDayBasis ,
PayAmount ,
PayCurrency ,
RecieveAmount ,
RecieveCurrency ,
RecieveFullTradeAmount ,
PayFullTradeAmount ,
HedgeSpotInd ,
PayFromLegalEnt ,
PayFromBank ,
PayFromBankSwiftorSortCode ,
PayFromBankAccountName ,
PayFromBankAccountNumber ,
PayFromBankAttensionof ,
PayBankCode ,
PayViaBankCode ,
PayViaBankswiftCode ,
PayViaBankName ,
PayViaBankAddress ,
PayViaBankAccountName ,
PayViaBankAccountNumber ,
PayViaBankCountry ,
CounterPartyName ,
CounterPartyLegalEnt ,
CounterPartyBanksName ,
CounterPartyBanksSwiftorSortCode ,
CounterPartyBanksAddress1 ,
CounterPartyBanksAddress2 ,
CounterPartyBanksAddress3 ,
CounterPartyBanksAccountName ,
CounterPartyBanksAccountNumber ,
CounterPartyBanksAttensionOf ,
CounterPartyRecieveViaBankCode ,
CounterPartyRecieveViaBankswiftCode ,
CounterPartyRecieveViaBankName ,
CounterPartyRecieveViaBankAddress ,
CounterPartyRecieveViaBankAccountName ,
CounterPartyRecieveViaBankAccountNumber ,
CounterPartyRecieveViaBankCountry ,
RecieveBankName ,
RecieveBankAddress1 ,
RecieveBankAddress2 ,
RecieveBankAddress3 ,
RecieveBankSwiftCode ,
RecieveBankAccountName ,
RecieveBankAccountNumber ,
RecieveBankAttentionOf ,
TAMComments ,
BeneficiaryComments1 ,
BeneficiaryComments2 ,
EnteredUserID ,
VerifyUserID ,
AuthoriseUserID ,
SequenceNumber ,
HoldsCodeForRoles ,
AuditDate ,
AuditTime ,
MaturityDate ,
PostedToHeliograph ,
ExtractedForHiportfolio ,
AddedBy ,
AddedDatetime)
SELECT
AuditReference,
DealRef,
TradeType,
AuthorisedInd,
FlaggedToMatureInd,
FlowDateOut,
FlowDateIn,
TradeDate,
ReceiveInterestRate,
PayInterestRate,
SpotFXRate,
Points,
OutrightRate,
ReceiveDayBasis,
PayAmount,
PayCurrency,
ReceiveAmount,
ReceiveCurrency,
ReceiveFulltradeAmount,-- full trade amount for matching
PayFullTradeAmount,-- full trade amount for matching
HedgeSpotInd,
PayFromLegalEnt,
PayFromBank,
PayFromBankSwiftOrSortCode,
PayFromBankAccountName,
PayFromBankAccountNumber,
PayFromBankAttentionOf,
PayBankCode,
PayViaBankCode,
PayViaBankSwiftCode,
PayViaBankName,
PayViaBankAddress,
PayViaBankAccountName,
PayViaBankAcountNumber,
PayViaBankCountry,
CounterPartyName,
CounterPartyLegalEnt,
CounterPartyBanksName,
CounterPartyBankSwiftOrSortCode,
CounterPartyBanksAddress1,
CounterPartyBanksAddress2,
CounterPartyBanksAddress3,
CounterPartyBanksAccountName,
CounterPartyBanksAccountNumber,
CounterPartyBanksAttentionOf,
CounterPartyReceiveViaBankCode,
CounterPartyReceiveViaBankSwiftCode,
CounterPartyReceiveViaBankName,
CounterPartyReceiveViaBankAddress,
CounterPartyReceiveViaBankAccountName,
CounterPartyReceiveViaBankAccountNumber,
CounterPartyReceiveViaBankCountry,
ReceiveBankName,
ReceiveBankAddress1,
ReceiveBankAddress2,
ReceiveBankAddress3,
ReceiveBankSwiftCode,
ReceiveBankAccountName,
ReceiveBankAccountNumber,
ReceiveBankAttentionOf,
TAMComments,
BeneficiaryComments1,
BeneficiaryComments2,
EnteredUserID,
VerifyUserID,
AuthoriseUserID,
SequenceNumber,
HoldsCodeForRolls,
AuditDate,
AuditTime,
MaturityDate,
''N'',
''N'',
suser_sname(),
dbo.uf_ConvertToLZDate(getdate())

FROM OPENQUERY( MILLENNIUM,''SELECT
T3.xAudref AuditReference,
RTRIM(T1.xRefNR) DealRef,
RTRIM(T3.xTType) TradeType,

CASE SUBSTRING(T3.xLogix,16,1)
WHEN 2 THEN ''''Y''''
ELSE ''''N''''
END AuthorisedInd,

CASE SUBSTRING(T3.xLogix,14,1)
WHEN 1 THEN ''''Y''''
ELSE ''''N''''
END FlaggedToMatureInd,
cast(T1.xTrxDate as datetime) FlowDateOut,
cast(T2.xTrxDate as datetime) FlowDateIn,
cast (T3.xTradeDate as datetime) TradeDate,

T3.xRIR ReceiveInterestRate,
T3.xPIR PayInterestRate,
T3.xArrFee SpotFXRate,

T3.XReval/10000 Points,
T3.xReval/10000 + T3.xArrFee OutrightRate,
T3.xRDB ReceiveDayBasis,
T1.xAmount PayAmount,
RTRIM(T1.xCCYCode) PayCurrency,
T2.xAmount ReceiveAmount,
RTRIM(T2.xCCYCode) ReceiveCurrency,
T3.xRPR ReceiveFulltradeAmount,-- full trade amount for matching
T3.xPPR PayFullTradeAmount,-- full trade amount for matching
RTRIM(T3.xBook) HedgeSpotInd,
RTRIM(T1.xLegal) PayFromLegalEnt,
RTRIM(T1.xFromBank) PayFromBank,

CASE T3.xTType
WHEN ''''DEPP''''
THEN RTRIM(T2.xSWCode)
ELSE RTRIM(T3.xPaysCode)
END PayFromBankSwiftOrSortCode,

RTRIM(T1.xFromAcName) PayFromBankAccountName,

case substring(t4.xLogix,10,1)
when 1 then rtrim(t4.xAliasAcNum)
else RTRIM(T1.xAcctNR) end PayFromBankAccountNumber,

RTRIM(T1.xPayCode) PayFromBankAttentionOf,
RTRIM(T1.xBankCode) PayBankCode,
RTRIM(T1.xBB_Code) PayViaBankCode,
RTRIM(T1.xBB_Swift) PayViaBankSwiftCode,
RTRIM(T1.xBB_Name) PayViaBankName,
RTRIM(T1.xBB_Address) PayViaBankAddress,
RTRIM(T1.xBB_City) PayViaBankAccountName,
null PayViaBankAcountNumber,
RTRIM(T1.xBB_Country) PayViaBankCountry,
RTRIM(T3.XCTP) CounterPartyName,
RTRIM(T2.xLegal) CounterPartyLegalEnt,
RTRIM(T1.xName) CounterPartyBanksName,
RTRIM(T1.xSWCode) CounterPartyBankSwiftOrSortCode,
RTRIM(T1.xPayToAddr1) CounterPartyBanksAddress1,
RTRIM(T1.xPayToAddr2) CounterPartyBanksAddress2,
RTRIM(T1.xPayToAddr3) CounterPartyBanksAddress3,
RTRIM(T1.xAccNameTo) CounterPartyBanksAccountName,
case substring(t5.xLogix,10,1)
when 1 then rtrim(t5.xAliasAcNum)
else RTRIM(T1.xAcctNrTo) end CounterPartyBanksAccountNumber,
RTRIM(T2.xPayCode) CounterPartyBanksAttentionOf,
RTRIM(T2.xBB_Code) CounterPartyReceiveViaBankCode,
RTRIM(T2.xBB_Swift) CounterPartyReceiveViaBankSwiftCode,
RTRIM(T2.xBB_Name) CounterPartyReceiveViaBankName,
RTRIM(T2.xBB_Address) CounterPartyReceiveViaBankAddress,

RTRIM(T2.xBB_City) CounterPartyReceiveViaBankAccountName,
null CounterPartyReceiveViaBankAccountNumber,
RTRIM(T2.xBB_Country) CounterPartyReceiveViaBankCountry,
RTRIM(T3.xRecBankNm) ReceiveBankName,
RTRIM(T3.xRecAddr1) ReceiveBankAddress1,

RTRIM(T3.xRecAddr2) ReceiveBankAddress2,
RTRIM(T3.xRecAddr3) ReceiveBankAddress3,
RTRIM(T3.xRecsCode) ReceiveBankSwiftCode,
RTRIM(T3.xRecAcName) ReceiveBankAccountName,
RTRIM(T3.xRecAcNumb) ReceiveBankAccountNumber,
RTRIM(T3.xRecAttn) ReceiveBankAttentionOf,
RTRIM(T3.xComment) TAMComments,
RTRIM(T1.xDesc1) BeneficiaryComments1,
RTRIM(T1.xCostAcc) BeneficiaryComments2,
RTRIM(T3.xTransactor) EnteredUserID,
LEFT(RTRIM(T3.xUserID),4) VerifyUserID,
RTRIM(T3.xAuthority) AuthoriseUserID,
RTRIM(T1.xSequenceNR) SequenceNumber,
RTRIM(T3.xRAccr) HoldsCodeForRolls,
CAST(T6.xDate as datetime) AuditDate,
CAST(T6.xTime as datetime) AuditTime,
CAST(T3.xMAT as datetime) MaturityDate

FROM Richmond.richmond.xEFTTRxs T1 JOIN
Richmond.richmond.xTransact T3 ON (T1.xRefNR = T3.xDealRef)
JOIN Richmond.richmond.xAud T6 ON (T3.xAudRef = T6.xReference)
LEFT OUTER JOIN (SELECT * FROM Richmond.richmond.xEFTTRXS WHERE xAmount > 0) T2 ON (T1.xRefNR = T2.xRefNR)
left JOIN richmond.richmond.xBank T4 on (rtrim(t1.xAcctNR) = rtrim(t4.xAccount) and rtrim(t1.xBankCode) = rtrim(t4.xSWCode))
left JOIN richmond.richmond.xBank T5 on (rtrim(t1.xAcctNRTo) = rtrim(t5.xAccount) and rtrim(t2.xBankCode) = rtrim(t5.xSWCode))

WHERE T1.xRefNR = ''''' + @Id + '''''
AND T1.xAmount <0
AND T3.xTType in (''''FORX'''', ''''FXFD'''', ''''FXSW'''',''''CTRF'''')
AND (SUBSTRING(T3.xLogix,16,1) = 2 --is Authorised
OR SUBSTRING(T3.XLogix,14,1) = 1)
'')

INSERT INTO trd_MillenniumTrans (
AuditReference ,
DealRef ,
TradeType ,
AuthorisedInd ,
FlaggedToMatureInd ,
FlowDateOut ,
FlowDateIn ,
TradeDate ,
RecieveInterestRate ,
PayInterestRate ,
SpotFXrate ,
Points ,
OutrightRate ,
RecieveDayBasis ,
PayAmount ,
PayCurrency ,
RecieveAmount ,
RecieveCurrency ,
RecieveFullTradeAmount ,
PayFullTradeAmount ,
HedgeSpotInd ,
PayFromLegalEnt ,
PayFromBank ,
PayFromBankSwiftorSortCode ,
PayFromBankAccountName ,
PayFromBankAccountNumber ,
PayFromBankAttensionof ,
PayBankCode ,
PayViaBankCode ,
PayViaBankswiftCode ,
PayViaBankName ,
PayViaBankAddress ,
PayViaBankAccountName ,
PayViaBankAccountNumber ,
PayViaBankCountry ,
CounterPartyName ,
CounterPartyLegalEnt ,
CounterPartyBanksName ,
CounterPartyBanksSwiftorSortCode ,
CounterPartyBanksAddress1 ,
CounterPartyBanksAddress2 ,
CounterPartyBanksAddress3 ,
CounterPartyBanksAccountName ,
CounterPartyBanksAccountNumber ,
CounterPartyBanksAttensionOf ,
CounterPartyRecieveViaBankCode ,
CounterPartyRecieveViaBankswiftCode ,
CounterPartyRecieveViaBankName ,
CounterPartyRecieveViaBankAddress ,
CounterPartyRecieveViaBankAccountName ,
CounterPartyRecieveViaBankAccountNumber ,
CounterPartyRecieveViaBankCountry ,
RecieveBankName ,
RecieveBankAddress1 ,
RecieveBankAddress2 ,
RecieveBankAddress3 ,
RecieveBankSwiftCode ,
RecieveBankAccountName ,
RecieveBankAccountNumber ,
RecieveBankAttentionOf ,
TAMComments ,
BeneficiaryComments1 ,
BeneficiaryComments2 ,
EnteredUserID ,
VerifyUserID ,
AuthoriseUserID ,
SequenceNumber ,
HoldsCodeForRoles ,
AuditDate ,
AuditTime ,
MaturityDate ,
PostedToHeliograph ,
ExtractedForHiportfolio ,
AddedBy ,
AddedDatetime
)
SELECT
AuditReference ,
DealRef ,
TradeType ,
AuthorisedInd ,
FlaggedToMatureInd ,
FlowDateOut ,
FlowDateIn ,
TradeDate ,
RecieveInterestRate ,
PayInterestRate ,
SpotFXrate ,
Points ,
OutrightRate ,
RecieveDayBasis ,
PayAmount ,
PayCurrency ,
RecieveAmount ,
RecieveCurrency ,
RecieveFullTradeAmount ,
PayFullTradeAmount ,
HedgeSpotInd ,
PayFromLegalEnt ,
PayFromBank ,
PayFromBankSwiftorSortCode ,
PayFromBankAccountName ,
PayFromBankAccountNumber ,
PayFromBankAttensionof ,
PayBankCode ,
PayViaBankCode ,
PayViaBankswiftCode ,
PayViaBankName ,
PayViaBankAddress ,
PayViaBankAccountName ,
PayViaBankAccountNumber ,
PayViaBankCountry ,
CounterPartyName ,
CounterPartyLegalEnt ,
CounterPartyBanksName ,
CounterPartyBanksSwiftorSortCode ,
CounterPartyBanksAddress1 ,
CounterPartyBanksAddress2 ,
CounterPartyBanksAddress3 ,
CounterPartyBanksAccountName ,
CounterPartyBanksAccountNumber ,
CounterPartyBanksAttensionOf ,
CounterPartyRecieveViaBankCode ,
CounterPartyRecieveViaBankswiftCode ,
CounterPartyRecieveViaBankName ,
CounterPartyRecieveViaBankAddress ,
CounterPartyRecieveViaBankAccountName ,
CounterPartyRecieveViaBankAccountNumber ,
CounterPartyRecieveViaBankCountry ,
RecieveBankName ,
RecieveBankAddress1 ,
RecieveBankAddress2 ,
RecieveBankAddress3 ,
RecieveBankSwiftCode ,
RecieveBankAccountName ,
RecieveBankAccountNumber ,
RecieveBankAttentionOf ,
TAMComments ,
BeneficiaryComments1 ,
BeneficiaryComments2 ,
EnteredUserID ,
VerifyUserID ,
AuthoriseUserID ,
SequenceNumber ,
HoldsCodeForRoles ,
AuditDate ,
AuditTime ,
MaturityDate ,
PostedToHeliograph ,
ExtractedForHiportfolio ,
AddedBy ,
AddedDatetime

FROM
@TempMill
')

--Catch the error if there was one from the previous statements and pass it to the error handler
SELECT @Error = @@ERROR
IF @Error != 0
BEGIN
SELECT @ErrMsg = 'INSERT INTO trd_MillenniumTrans failed for Id - ' + @Id

GOTO ErrorHandler
END

-- Standard Exit
RETURN 0

-- Error Handling
ErrorHandler:
BEGIN
IF @@TRANCOUNT <> 0
BEGIN
ROLLBACK TRANSACTION
END

SELECT @ErrMsg = @ProcName + ' ERROR ' + @ErrMsg

RAISERROR (@ErrMsg , 11, 1)

RETURN -1
END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-17 : 08:54:44

why the dynamic sql?

using a rtrim or any other function in the join disables the proper use of indexes.

are proper indexes in place? on primary keys, joined columns where columns etc...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2007-05-17 : 09:21:23

proper indexes are in place.
I think they had to use the dynamic stuff as the query was other 8000 bytes i length and this causes some serious issues in MSSQL2000.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
[/quote]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-17 : 09:29:06

issues? what kind of issues?

did you remove the trim functions from the joins?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-17 : 09:32:48

This is not just poorly written proc, but also poorly formatted !!
It is hard to find end of one statement and start of another

At least follow some basic indentation principles and place your code inside [code] tags to retain your formatting.

1. I don't see what are the issues that is causing to adhere to D-sql
2. Don't use trim functions on columns other than having CHAR datatype

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2007-05-17 : 09:43:29

you cannot have a row of data over 8000k in MSSQL 2000 without having to resort to BLOBs, these have been removed in 2005 as proved troublesome.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-17 : 09:53:26

having a row of over 8k isn't a reason to use dynamic sql.

not to mention this part:
WHERE T1.xRefNR = ''''' + @Id + '''''
AND T1.xAmount <0
AND T3.xTType in (''''FORX'''', ''''FXFD'''', ''''FXSW'''',''''CTRF'''')
AND (SUBSTRING(T3.xLogix,16,1) = 2 --is Authorised
OR SUBSTRING(T3.XLogix,14,1) = 1)

unless you're missing a ( and a ) then you have a serious logic flaw.
substring will also cause a table scan..

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2007-05-17 : 11:28:05

ok, here is the main part of the developers explanation.

The original passthrough query was a long union query, which uses the 'OPENQUERY' Command and passes the actual TSQL union query in as a long string.
I had to add a number of fields to this query. Unfortunately when this was done, the Query string was over 8000 characters. Too long for a Passthrough query.
To remedy this, I created a temp table in memory, split the union query into two and performed two passthrough queries seperately (that way the query strings were short enough to run), inserting the records into the temp table. Then using the data in the temp table, I insert those records into the trd_millenniumtrans table.

I included this bit for everyone's enjoyment

This take a long time to process (as you can imagine). Not being a SQL DBA, I can think of no other way to acheive this in a more efficient manner.
Can you request that a DBA takes a look at it and see if they can come up with a better way of acheiving the same result?
The original and new SPs are attached.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-17 : 11:37:42

It's another case where the database design is so bad that pretty much any SQL written against it will also be a big mess. What is really needed is a database redesign (normalize!!!!) and then your code becomes so much shorter and simpler and definitely more efficient.

http://www.datamodel.org/NormalizationRules.html

- Jeff
http://weblogs.sqlteam.com/JeffS

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-17 : 11:39:11

well since this is from an openquery i suppose it's from excel and it's some kind of staging part of the import process.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-17 : 12:06:02

Ah, I see, that's a good point.

- Jeff
http://weblogs.sqlteam.com/JeffS

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2007-05-18 : 04:59:13

you'd like to think so... but alas no. its a Open Query to another SQL server

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-18 : 05:05:38

then you need to return the smallest set possible from anoher server.
also try using linked servers.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2007-05-18 : 07:27:16

if the linked servers are in place but they use open query , will it ignore the linked server?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-18 : 07:44:25

OpenQuery executes the query on the specified linked server.

but what i meant was you can use the 4 part naming convention like
server.database.owner.table
to access your data instead of using openquery.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources