Ad hoc query is running crazy slow since last week

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)

Ad hoc query is running crazy slow since last week

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-18 : 08:39:56

Sofi writes "Hi,
I have a job that execute a stored proc. It was running fine like 3
minutes to finish but a week ago it started to take 4 hours. Its using
ad hoc quries in that stored procedure. I reindex all the tables that
it was using. I check execution plan there was no tabloe scans. I
recompile the stored proc but no use. its sql 2000 with service pack 3
installed on it. Here is the query anyone has any clue what went wrong
suddenly. Link server is working fine.

i am running it like this

exec usp_MO_ActivityTradesBPS 'CORP'

CREATE PROCEDURE dbo.usp_MO_PositionsBPS

@Load varchar(25) = null,
@AsOfDate datetime

AS

Set @Load = '%'

If @Load is null or @Load = ''

begin

Set @Load = '%'

end

If IsNull(@AsOfDate,'') = ''
Set @AsOfDate = dbo.fn_BusinessDate(GetDate()-1)

If @Load = 'SANFRAN'

begin

Set @Load = '%'

SELECT RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' +
T.ACCOUNTTYPECD + T.CUSIPNR [key],
RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' + T.ACCOUNTTYPECD
BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR, T.CUSIPNR,
T.ADPSECURITY1DS, T.TDQUANTITY, T.SECURITYPRICEAM, T.LEDGERBALANCEAM,
T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
T.DAILYNETPL, T.CURRENCYISOCD
FROM BPS_Positions T
WHERE T.BRANCHCD = '001'
and T.BRANCHCD + '-' + T.ACCOUNTNR not in (Select Distinct
SM.Bps_AcctNr
From MO_InvStrategyMap SM
Where SM.Bps_AcctNr is not null)
and (T.TDMTDSECFEE <> 0
or T.TDCOmsnAm <> 0
or T.TDQUANTITY <> 0
or T.MarketValAm <> 0
or T.MTDNETPL <> 0
or T.MTDPL <> 0
or T.LEDGERBAlanceAm <> 0
or T.DAILYNETPL <> 0
or T.TDDivIntAm <> 0
or T.TDGRCREDITMTDOP7 <> 0)
ORDER BY T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD, T.ADPSECURITYNR,
T.CUSIPNR, T.CURRENCYISOCD

end

Else

begin

SELECT AM.Inventory + T.CUSIPNR [Key], rtrim(T.BRANCHCD) + '-' +
rtrim(T.ACCOUNTNR) BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR,
T.CUSIPNR,
T.ADPSECURITY1DS, T.TDQUANTITY, case when T.TDQuantity <> 0
then T.MarketValAm/T.TDQuantity
else T.SecurityPriceAm end as SecurityPriceAm,
T.LEDGERBALANCEAM, T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
T.DAILYNETPL, T.CURRENCYISOCD
FROM BPS_PositionsHistorical T
JOIN MO_InvAccountMap AM
ON T.BRANCHCD + '-' + T.ACCOUNTNR = AM.AccountNr
JOIN (Select Distinct TraderCd
From MO_InvStrategyMap
Where PrimaryRecFl = 1
and (Desk like @Load
or Category like @Load
or SubCategory like @Load
or TraderName like @Load
or TraderCd like @Load)) SM
ON SM.TraderCd = AM.Inventory
WHERE T.BusinessDate = @AsOfDate
and AM.DataSource like 'BPS%'
and (T.TDMTDSECFEE <> 0
or T.T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources