Wierd Stored Procedure Issue 1

[pramsam1]

I have a simple stored procedure.
-----------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_stproc1 @CurrentDay AS smalldatetime, @WhatDay AS smalldatetime AS

Just one select statment

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
---------------------------------------------

When I run the select statement only from the Query Analyzer, it takes about 4 mins to run.

When I execute the stored proc:
exec sp_stproc1 '2008-09-17', '2008-09-16'

it never comes back.

Anyone experienced similar problem ?????

Thanks.

 

[gmmastros]

[google]Parameter Sniffing[/google]

When I run the select statement only from the Query Analyzer, it takes about 4 mins to run.

If you can post the query, perhaps we can help you speed it up.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pramsam1]

I could post the query which is a bit complicated. But any reason for it never to come back when it is run from stored proc while it comes back in 4 mins when run from query analyzer ?

 

[gmmastros]

Yeah. It's probably Parameter Sniffing. Did you click the link I posted above? This article explains it pretty well.

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pramsam1]

George: Sorry, I actually did not notice the link in your earlier post. I am going to try this and will post how it went. Thanks in advance..

 

[pramsam1]

Unfortunately, even after writing code to get around parameter sniffing this still is not working. Here's the stored proc. Any help would be much appreciated.
------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE [dbo].[sp_CPM_Data_Change_Report_NEW] @CurrentDay AS smalldatetime, @WhatDay AS smalldatetime

AS

SELECT
[AS OF DATE] =
CASE
WHEN CONVERT(Varchar(10), ISNULL(CURR.[As Of Date], '9999-12-31'), 101) = '12/31/9999' THEN ''
ELSE
CONVERT(Varchar(10), ISNULL(CURR.[As Of Date], '9999-12-31'), 101)
END,
[PREV DATE] =
CASE
WHEN CONVERT(Varchar(10), ISNULL(PREV.[As Of Date], '9999-12-31'), 101) = '12/31/9999' THEN ''
ELSE
CONVERT(Varchar(10), ISNULL(PREV.[As Of Date], '9999-12-31'), 101)
END,
ISNULL(ISNULL(CURR.ANALYST, PREV.ANALYST), 'NOT FOUND') AS ANALYST,
ISNULL(CURR.[CONCENTRATION_RISK_NAME], PREV.[CONCENTRATION_RISK_NAME]) AS [CONCENTRATION_RISK_NAME],
ISNULL(CURR.[C/A NUMBER], PREV.[C/A NUMBER]) AS [C/A NUMBER],
ISNULL(CURR.[Description], PREV.[Description]) AS [Description],
ISNULL(CURR.[Facility Type], CURR.[Facility Type]) AS [Facility Type],
ISNULL(CURR.[CUSTOMER NAME], PREV.[CUSTOMER NAME]) AS [CUSTOMER NAME],
ISNULL(CURR.[BUSINESS LINE], PREV.[BUSINESS LINE]) AS [BUSINESS LINE],
ISNULL(CURR.[Service Section Number], PREV.[Service Section Number]) AS [SERVICE SECTION NUMBER],
ISNULL(CURR.[CURRENCY CODE], PREV.[CURRENCY CODE]) AS [CURRENCY CODE],

CAST(ROUND(ISNULL(CURR.[AUTHORIZED],0),0) AS MONEY) AS [GROSS AUTHORIZED CURRENT CAD],
CAST(ROUND(ISNULL(PREV.[AUTHORIZED],0),0) AS MONEY) AS [GROSS AUTHORIZED PRIOR CAD],
CAST(ROUND(ISNULL(CURR.[AUTHORIZED],0),0)-ROUND(ISNULL(PREV.[AUTHORIZED],0),0) AS MONEY) AS [GROSS CHANGE IN AUTHORIZED CAD],

CAST(ROUND(ISNULL(CURR.[NET AUTHORIZED],ISNULL(CURR.[AUTHORIZED],0)),0) AS MONEY) AS [NET AUTHORIZED CURRENT CAD],
CAST(ROUND(ISNULL(PREV.[NET AUTHORIZED],ISNULL(PREV.[AUTHORIZED],0)),0) AS MONEY) AS [NET AUTHORIZED PRIOR CAD],
CAST(ROUND(ISNULL(CURR.[NET AUTHORIZED],ISNULL(CURR.[AUTHORIZED],0)),0)-ROUND(ISNULL(PREV.[NET AUTHORIZED],ISNULL(PREV.[AUTHORIZED],0)),0) AS MONEY) AS [CHANGE IN NET AUTHORIZED CAD],

CAST(ROUND(ISNULL(CURR.[USED],0),0) AS MONEY) AS [USED CURRENT CAD],
CAST(ROUND(ISNULL(PREV.[USED],0),0) AS MONEY) AS [USED PRIOR CAD],
CAST(ROUND(ISNULL(CURR.[USED],0),0)-ROUND(ISNULL(PREV.[USED],0),0) AS MONEY) AS [CHANGE IN USED CAD],

CAST(ISNULL(CURR.[FRR], 0) AS INT) AS [FRR],
CAST(ISNULL(PREV.[FRR], 0) AS INT) AS [FRR PRIOR],
ROUND(ISNULL(CURR.[FRR],0),0)-ROUND(ISNULL(PREV.[FRR],0),0) AS [CHANGE IN FRR],
ISNULL(CURR.[OBLIGOR RISK RATING], 0) AS [OBLIGOR RISK RATING],
ISNULL(PREV.[OBLIGOR RISK RATING], 0) AS [OBLIGOR RISK RATING PRIOR],
ROUND(ISNULL(CURR.[OBLIGOR RISK RATING],0),0)-ROUND(ISNULL(PREV.[OBLIGOR RISK RATING],0),0) AS [CHANGE IN OBLIGOR RISK RATING],
CONVERT(Varchar(10), ISNULL(CURR.[MATURITY], '1900-01-01 00:00:00.000'), 101) AS [MATURITY],
CONVERT(Varchar(10), ISNULL(PREV.[MATURITY], '1900-01-01 00:00:00.000'), 101) AS [MATURITY PRIOR],
CASE WHEN CURR.[MATURITY] = '1900-01-01' OR CURR.[MATURITY] IS NULL
OR PREV.[MATURITY] = '1900-01-01' OR PREV.[MATURITY] IS NULL THEN 0
ELSE DATEDIFF (DAY , PREV.[MATURITY] , CURR.[MATURITY]) END AS [MATURITY CHANGE IN DAYS]
FROM

(SELECT A.[As Of Date],
ISNULL(B.[ConcentrationRiskName], A.[customer name]) CONCENTRATION_RISK_NAME,
A.[C/A NUMBER],
A.[Description],
A.[Facility Type],
A.[CUSTOMER NUMBER],
A.[CUSTOMER NAME],
A.[Service Section Number],
A.[BUSINESS LINE],
A.[CAPITAL REGION],
M.ANALYST,
A.[CURRENCY CODE],

A.AUTHORIZED /(SELECT MAX([EXCHANGE RATE]) FROM tbl_FacilitiesExtract_Hist
WHERE [As Of Date] = @CurrentDay
AND [CURRENCY CODE] = 'CAD') AS [AUTHORIZED],
net.NET_AUTH /(SELECT MAX([EXCHANGE RATE]) FROM tbl_FacilitiesExtract_Hist
WHERE [As Of Date] = @CurrentDay
AND [CURRENCY CODE] = 'CAD') AS [NET AUTHORIZED],
A.[TOTAL USED] /(SELECT MAX([EXCHANGE RATE]) FROM tbl_FacilitiesExtract_Hist
WHERE [As Of Date] = @CurrentDay
AND [CURRENCY CODE] = 'CAD') AS [USED],

ISNULL(A.[Expiry Date], '1900-01-01') AS [MATURITY],
A.[Facility Risk Rating] AS [FRR],
A.[OBLIGOR RISK RATING]

FROM

(SELECT *
FROM tbl_FacilitiesExtract_Hist
WHERE [As Of Date] = @CurrentDay) A
inner join tbl_companies_hist C
on a.[customer number] = c.cnum and a.archive_date = c.archive_date
LEFT JOIN tbl_dssData_HIST B
ON A.[c/a number] = B.[c/a number] AND A.[as of date] = B.[as of date]
left join usa_sic_to_analyst_map m
on c.SICC = M.SIC_USA_CD
left join
(
select f1.archive_date, f1.[c/a number], f1.[customer name], f1.authorized,
sum(f2.authorized) auth_hedged,
f1.authorized + sum(f2.authorized) net_auth
from (select archive_date, [c/a number], [customer name], authorized from
tbl_facilitiesextract_HIST where [as of date] = @CurrentDay) f1 inner join
(select distinct d.archive_date, d.optex_or_alice Trade_ID, d.[reference id] [Underlying_Facility_ID]
from derivatives d,
tbl_facilitiesextract_hist f
where d.[reference id] = f.[c/a number]
and d.archive_date = f.archive_date
and f.[as of date] = @CurrentDay
and [reference id] is not null) map1
on f1.[c/a number] = map1.Underlying_Facility_ID and f1.archive_date = map1.archive_date
inner join
(select archive_date, [c/a number], [customer name], authorized from tbl_facilitiesextract_HIST where [as of date] = @CurrentDay) f2
on f2.[c/a number] = map1.Trade_Id and f2.archive_date = map1.archive_date
group by F1.ARCHIVE_DATE, f1.[c/a number], f1.[customer name], f1.authorized) net
on A.[c/a number] = net.[c/a number] and A.archive_date = net.archive_date
WHERE A.[As Of Date] = @CurrentDay
AND A.[C/A NUMBER] NOT LIKE '%I1'
AND A.AUTHORIZED >=0) CURR

FULL OUTER JOIN
(SELECT A.[As Of Date],
ISNULL(B.[ConcentrationRiskName], A.[customer name]) CONCENTRATION_RISK_NAME,
A.[C/A NUMBER],
A.[Description],
A.[Facility Type],
A.[CUSTOMER NUMBER],
A.[CUSTOMER NAME],
A.[Service Section Number],
A.[BUSINESS LINE],
A.[CAPITAL REGION],
M.ANALYST,
A.[CURRENCY CODE],
A.AUTHORIZED/(SELECT MAX([EXCHANGE RATE]) FROM tbl_FacilitiesExtract_Hist
WHERE [As Of Date] = @WhatDay
AND [CURRENCY CODE] = 'CAD') AS [AUTHORIZED],
net.NET_AUTH /(SELECT MAX([EXCHANGE RATE]) FROM tbl_FacilitiesExtract_Hist
WHERE [As Of Date] = @WhatDay
AND [CURRENCY CODE] = 'CAD') AS [NET AUTHORIZED],
A.[TOTAL USED] /(SELECT MAX([EXCHANGE RATE]) FROM tbl_FacilitiesExtract_Hist
WHERE [As Of Date] = @WhatDay
AND [CURRENCY CODE] = 'CAD') AS [USED],
ISNULL(A.[Expiry Date], '1900-01-01') AS [MATURITY],
A.[Facility Risk Rating] AS [FRR],
A.[OBLIGOR RISK RATING]

FROM

(SELECT *
FROM tbl_FacilitiesExtract_Hist
WHERE [As Of Date] = @WhatDay ) A
inner join tbl_companies_hist C
on a.[customer number] = c.cnum and a.archive_date = c.archive_date
LEFT JOIN tbl_dssData_HIST B
ON A.[c/a number] = B.[c/a number] AND A.[as of date] = B.[as of date]
left join usa_sic_to_analyst_map m
on c.SICC = M.SIC_USA_CD
left join

(
select f1.archive_date, f1.[c/a number], f1.[customer name], f1.authorized,
sum(f2.authorized) auth_hedged,
f1.authorized + sum(f2.authorized) net_auth
from (select archive_date, [c/a number], [customer name], authorized from tbl_facilitiesextract_HIST where [as of date] = @whatday) f1 inner join
(select distinct d.archive_date, d.optex_or_alice Trade_ID, d.[reference id] [Underlying_Facility_ID] from derivatives d,
tbl_facilitiesextract_hist f
where d.[reference id] = f.[c/a number]
and d.archive_date = f.archive_date
and f.[as of date] = @whatday
and [reference id] is not null) map1
on f1.[c/a number] = map1.Underlying_Facility_ID and f1.archive_date = map1.archive_date
inner join
(select archive_date, [c/a number], [customer name], authorized from tbl_facilitiesextract_HIST where [as of date] = @whatday) f2
on f2.[c/a number] = map1.Trade_Id and f2.archive_date = map1.archive_date
group by F1.ARCHIVE_DATE, f1.[c/a number], f1.[customer name], f1.authorized) net

on A.[c/a number] = net.[c/a number] and A.archive_date = net.archive_date
WHERE A.[As Of Date] = @WhatDay
AND A.[C/A NUMBER] NOT LIKE '%I1'
AND A.AUTHORIZED >=0) PREV

ON CURR.[c/a number] = PREV.[c/a number]
ORDER BY
ROUND(ISNULL(CURR.[AUTHORIZED],0),0)-ROUND(ISNULL(PREV.[AUTHORIZED],0),0) DESC,
ROUND(ISNULL(CURR.[USED],0),0)-ROUND(ISNULL(PREV.[USED],0),0) DESC,
ROUND(ISNULL(CURR.[OBLIGOR RISK RATING],0),0)-ROUND(ISNULL(PREV.[OBLIGOR RISK RATING],0),0) DESC,
ROUND(ISNULL(CURR.[FRR],0),0)-ROUND(ISNULL(PREV.[FRR],0),0)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[gmmastros]

Oh man. That's a lot of code.

There are bound to be a dozen different ways to speed this up. Since it is so long, I'll just give you some pointers.

The first thing I noticed is this...

      A.AUTHORIZED/([!]SELECT MAX([EXCHANGE RATE]) FROM tbl_FacilitiesExtract_Hist  
              WHERE [As Of Date] = @WhatDay  
          AND [CURRENCY CODE] = 'CAD'[/!]) AS [AUTHORIZED], 
    net.NET_AUTH /([!]SELECT MAX([EXCHANGE RATE]) FROM tbl_FacilitiesExtract_Hist  
              WHERE [As Of Date] = @WhatDay  
          AND [CURRENCY CODE] = 'CAD'[/!]) AS [NET AUTHORIZED],
    A.[TOTAL USED] /([!]SELECT MAX([EXCHANGE RATE]) FROM tbl_FacilitiesExtract_Hist  
                 WHERE [As Of Date] = @WhatDay   
                 AND [CURRENCY CODE] = 'CAD'[/!]) AS [USED],

That query in red... It's exactly the same and appears in your code 6 times. Granted, 3 of the times it's using @WhatDay and the other 3 times it's using @CurrentDay. So, if the parameter values are different, then it will return different values.

Anyway, what I would do is this...

ALTER PROCEDURE [dbo].[sp_CPM_Data_Change_Report_NEW] @CurrentDay AS smalldatetime, @WhatDay AS smalldatetime 

AS
[!]SET NOCOUNT ON[/!]

Declare @CurrentDayExchangeRate [blue]Decimal(20,10)[/blue]
Declare @WhatDayExchangeRate [blue]Decimal(20,10)[/blue]

Select @CurrentDayExchangeRate = MAX([EXCHANGE RATE]) 
FROM   tbl_FacilitiesExtract_Hist  
WHERE  [As Of Date] = @CurrentDay  
       AND [CURRENCY CODE] = 'CAD'

Select @WhatDayExchangeRate = MAX([EXCHANGE RATE]) 
FROM   tbl_FacilitiesExtract_Hist  
WHERE  [As Of Date] = @WhatDay  
       AND [CURRENCY CODE] = 'CAD'

-- Then, where the query was used in the code...

      A.AUTHORIZED /@CurrentDayExchangeRate AS [AUTHORIZED], 
    net.NET_AUTH /@CurrentDayExchangeRate AS [NET AUTHORIZED],
    A.[TOTAL USED] /@CurrentDayExchangeRate AS [USED],

-- Repeat this process for the @WhatDayExchangeRate

In the sample code I provided, I declare scalar values for @CurrentDayExchangeRate and @WhatDayExchangeRate. I provided a data type for this, but it's probably wrong. You should look up the data type for the exchange rate column and modify the code to match.

Next....

I noticed a big block of code that appears to be duplicated. I assume you have this so that you can perform a 'self join' to show current and previous. I suspect you may get better performance by using a table variable or a temp table instead. What I am suggesting is.... create a temp table at the beginning of the stored procedure. Then, populate the temp table based on that query, and then use the temp table in the bigger query. Derived tables are usually faster than using a temp table, but, since you are using this big derived table twice, a temp table may be faster.

Also...

The full outer join scares me. This can sometimes lead to VERY inefficient code. Since I don't really understand the nature of the data, I can't really comment on that part.

More...

I see that you are using DISTINCT in parts of your derived tables. Do you really need to use distinct? Often times, programmers will use distinct simply because they are missing a join condition. Distinct (in SQL Server world) is a very expensive operation. If you can avoid it, you should.

Lastly...

When optimizing performance, it's usually best to start 'down deep'. The deeper something is, the more important it is to be fast. Specifically, you are using derived tables a lot. That, in itself, isn't necessarily bad. What's bad is when the performance of a derived table is bad. My suggestion is, check your derived tables. If you are returning a column that you don't use, then get rid of it. If the derived table is not using indexes properly, then re-write the derived table or add additional indexes.

Finally...

Realize that I don't know your data. I don't know your tables, or the relationships that occur between your tables. I don't know what indexes you have (or don't have). The advice I've given here is generalized. Some of it may apply, and some may not. Hopefully, some of this will be useful for you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pramsam1]

George: Thanks a lot. I will follow your advice on optimizing the query but now I have been able to have the query run from stored proc in about the same time as select statement from SQL query analyzer. You were right all along. It is parameter sniffing issue.

Here's how it finally worked out.

In the article you suggested, the code to get around parameter sniffing is:
----------------
CREATE PROC prSalesByCountry
@country char(3),
@template_country char(3)="USA"
AS
SET @template_country=@country
SELECT * FROM sales WHERE country=@template_country
GO
----------------
I tried this for my query but it did not work.

Then I found a similar work around using local variables instead.

http://nflanagan.blogspot.com/2006/07/parameter-sniffing.html

-----------------
Create Procedure usp_Niall2
@x int
As
Declare @y int
Set @y = @x
Select * from something where id = @y
GO
Exec usp_Niall2 1
------------
I tried this and it worked like a charm.. Thank you so very much for taking time to help...

 

[gmmastros]

You said earlier that it takes 4 minutes to run.

Can you please make this change and tell me how long it takes? It's not terribly important, but I am really curious to know.

ALTER PROCEDURE [dbo].[sp_CPM_Data_Change_Report_NEW] @CurrentDay AS smalldatetime, @WhatDay AS smalldatetime 

AS
SET NOCOUNT ON

Declare @CurrentDayExchangeRate Decimal(20,10)
Declare @WhatDayExchangeRate Decimal(20,10)

Select @CurrentDayExchangeRate = MAX([EXCHANGE RATE]) 
FROM   tbl_FacilitiesExtract_Hist  
WHERE  [As Of Date] = @CurrentDay  
       AND [CURRENCY CODE] = 'CAD'

Select @WhatDayExchangeRate = MAX([EXCHANGE RATE]) 
FROM   tbl_FacilitiesExtract_Hist  
WHERE  [As Of Date] = @WhatDay  
       AND [CURRENCY CODE] = 'CAD'

-- Then, where the query was used in the code...

      A.AUTHORIZED /@CurrentDayExchangeRate AS [AUTHORIZED], 
    net.NET_AUTH /@CurrentDayExchangeRate AS [NET AUTHORIZED],
    A.[TOTAL USED] /@CurrentDayExchangeRate AS [USED],

-- Repeat this process for the @WhatDayExchangeRate

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pramsam1]

George: With just above code change for the exchange rate, the time reduced from 4min 18sec to 2min 29sec. Thanks.

 

[gmmastros]

Good to know. Thanks.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom