Ad hoc query running crazy slow.

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.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 AM.Inventory, T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD,
T.ADPSECURITYNR, T.CUSIPNR, T.CURRENCYISOCD
end
GO
Assuming this is consistently reproduced, a few things you might want to
investigate:
- Do you have the original (3-minute) query plan? If so, what's different
from the current one?
- Looking at the query plan, where are you spending most of your time?
- During execution, are you seeing any resource constraints? E.g. > 5 avg
disk queue length, high avg disk sec/read,
- Are you seeing waits on cxpackets when you run dbcc sqlperf (waitstats)?
Might indicate parallellized queries waiting.
Suggest you dig into the query plan to see where it's spending most of its
time and check to ensure you don't have resource bottlenecks.
joe.
"DARR" <umariqbaldar@.gmail.com> wrote in message
news:1168992585.559292.8530@.s34g2000cwa.googlegrou ps.com...
> 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.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 AM.Inventory, T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD,
> T.ADPSECURITYNR, T.CUSIPNR, T.CURRENCYISOCD
> end
> GO
>

Actual Query is fast, Report is slow and does not render

I have a Report with 3 queries and when i run the queries individually they work beautifully, but when i run the report i wait 15 - 20 minutes and it doesnt bring back anything saying please wait. Also it pegs the sql server hard, when i run the report it tries to bring the server to its knees (not quite but VERY close it takes 60 - 70% of the processing power of the server until i close the report).

Each Query by itself works flawlessly, and returns all of the data within 5-10 seconds(Run through Query Analizer). Is there some funny thing that is resource intensive with actually rendering the report, it is basically just displaying the returned data, no crazy calculations. There can be a pretty big row count but nothing crazy. around 1500- 2000 on the last one
Is there a way that it is trying to run all three queries at the same time and that is killing the server?

Any help offerred is greatly appriciated

And as stated before the queries are fine and not the bottleneck, their effeciency is not the question.

Have you run all three queries together from QA with the same criteria that is taking a long time?
Can you post the queries?
Can you use 3 stored procs instead?

|||as i said it isnt the actual query it is the time it takes to create the report

I ran all queries involved in this report in a total of 5 minutes and that includes the amount of time to switch between the queries and enter the value of the parameter.

The report itself took 46 Minutes, nothing different, i actually ran the queries from the actual query used in the report when i achieved the 5 minute mark

Does something in SP2 fix this?|||Sorry - late to this POST
You have to install SP2 and I think there may be a "HotFix" as well --
Also, is there a Document Map you are creating when generating the report? I have had situations where I created a Document Map with a SQL table of about 400,000 records and I got inpatient and started to select a hyperling on a row of data that was displayed - but the entire worker process was not completed and my machine "hung" everytime!
Then if NO DOCUMENT MAP, then is there a problem with the length of the parameters defined within the stored procedure - As you know there is no length to define within MSRS - but if you enter a 5 byte field in MS RS and the resulting parameter in the stored procedure is 2 - then problems!
But nevertheless, I havfe not seen this behavior in MS RS and I have used a lot!
Best regards,
]
Joe|||What does the document map do? Does it make the creation of the report more efficient?
I am having this problem with many reports.
I am not using a stored procedure, but can you expand more on the thought of parameter size? why would it change?
What can you do to prevent it?