December 3, 2008 at 1:16 pm
I have created a stored procedure for a report someone needs. However, it takes 25 minutes to run, and it returns about 2.5k rows. Can anyone point at what I can do to make this faster? I build it on top of someone elses view called ReceiptsMadeWithItemDesc which joins about 20 different tables...
So I created a couple functions:
USE [PPI]
GO
/****** Object: UserDefinedFunction [dbo].[getLastYearsQuarterAvgPrice] Script Date: 12/03/2008 15:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[getLastYearsQuarterAvgPrice]
(
-- Add the parameters for the function here
@month int,
@year int
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT CASE WHEN SUM([Recd Qty]) = 0 THEN 0 ELSE SUM([Ext Line Rel Prc]) / SUM([Recd Qty]) END AS [Avg Price Per Quarter],
[Part Number]
FROM dbo.ReceiptsMadeWithItemDesc
WHERE DATEPART(m, [Recd Date]) 6 AND @month <= 9 THEN 9 ELSE 12 END
AND DATEPART(m, [Recd Date]) >= CASE WHEN @month 6 AND @month <= 9 THEN 7 ELSE 10 END
AND DATEPART(yyyy, [Recd Date]) = CONVERT(varchar(4),@year - 1)
GROUP BY [Part Number]
)
as well as
USE [PPI]
GO
/****** Object: UserDefinedFunction [dbo].[getQtyPriceByMonthYear] Script Date: 12/03/2008 15:07:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[getQtyPriceByMonthYear]
(
-- Add the parameters for the function here
@month int,
@year int
)
RETURNS TABLE
AS
RETURN
(
SELECT SUM([Recd Qty]) AS SumQty,
SUM([Ext Line Rel Prc]) AS SumPrice,
[Part Number]
FROM dbo.ReceiptsMadeWithItemDesc
WHERE DATEPART(m, [Recd Date]) = CONVERT(varchar(2),@month)
AND DATEPART(yyyy, [Recd Date]) = CONVERT(varchar(4),@year)
GROUP BY [Part Number]
)
Then the master stored proc:
USE [PPI]
GO
/****** Object: StoredProcedure [dbo].[ppi] Script Date: 12/03/2008 15:08:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ppi]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @fyYear int
SET @fyYear = DATEPART(yyyy, DATEADD(month, -6, GETDATE()))
DECLARE @nextYear int
SET @nextYear = @fyYear + 1
SELECT
Jul.[Part Number],
avg1.[Avg Price Per Quarter] as [Avg Price Q1],
avg2.[Avg Price Per Quarter] as [Avg Price Q2],
avg3.[Avg Price Per Quarter] as [Avg Price Q3],
avg4.[Avg Price Per Quarter] as [Avg Price Q4],
Jul.SumQty as [Jul Qty], CASE WHEN Jul.SumQty = 0 THEN 0 ELSE Jul.SumPrice / Jul.SumQty END as [Jul Avg Price], Jul.SumPrice as [Jul Net Spend], CASE WHEN Jul.SumPrice = 0 THEN 0 ELSE Jul.SumPrice - (Jul.SumQty * avg1.[Avg Price Per Quarter]) END as [Jul Impact], CASE WHEN avg1.[Avg Price Per Quarter] = 0 THEN 0 ELSE (CASE WHEN Jul.SumQty = 0 THEN 0 ELSE Jul.SumPrice / Jul.SumQty END) / avg1.[Avg Price Per Quarter] END as [Jul PPI],
Aug.SumQty as [Aug Qty], CASE WHEN Aug.SumQty = 0 THEN 0 ELSE Aug.SumPrice / Aug.SumQty END as [Aug Avg Price], Aug.SumPrice as [Aug Net Spend], CASE WHEN Aug.SumPrice = 0 THEN 0 ELSE Aug.SumPrice - (Aug.SumQty * avg1.[Avg Price Per Quarter]) END as [Aug Impact], CASE WHEN avg1.[Avg Price Per Quarter] = 0 THEN 0 ELSE (CASE WHEN Aug.SumQty = 0 THEN 0 ELSE Aug.SumPrice / Aug.SumQty END) / avg1.[Avg Price Per Quarter] END as [Aug PPI],
Sep.SumQty as [Sep Qty], CASE WHEN Sep.SumQty = 0 THEN 0 ELSE Sep.SumPrice / Sep.SumQty END as [Sep Avg Price], Sep.SumPrice as [Sep Net Spend], CASE WHEN Sep.SumPrice = 0 THEN 0 ELSE Sep.SumPrice - (Sep.SumQty * avg1.[Avg Price Per Quarter]) END as [Sep Impact], CASE WHEN avg1.[Avg Price Per Quarter] = 0 THEN 0 ELSE (CASE WHEN Sep.SumQty = 0 THEN 0 ELSE Sep.SumPrice / Sep.SumQty END) / avg1.[Avg Price Per Quarter] END as [Sep PPI],
Oct.SumQty as [Oct Qty], CASE WHEN Oct.SumQty = 0 THEN 0 ELSE Oct.SumPrice / Oct.SumQty END as [Oct Avg Price], Oct.SumPrice as [Oct Net Spend], CASE WHEN Oct.SumPrice = 0 THEN 0 ELSE Oct.SumPrice - (Oct.SumQty * avg2.[Avg Price Per Quarter]) END as [Oct Impact], CASE WHEN avg2.[Avg Price Per Quarter] = 0 THEN 0 ELSE (CASE WHEN Oct.SumQty = 0 THEN 0 ELSE Oct.SumPrice / Oct.SumQty END) / avg2.[Avg Price Per Quarter] END as [Oct PPI],
Nov.SumQty as [Nov Qty], CASE WHEN Nov.SumQty = 0 THEN 0 ELSE Nov.SumPrice / Nov.SumQty END as [Nov Avg Price], Nov.SumPrice as [Nov Net Spend], CASE WHEN Nov.SumPrice = 0 THEN 0 ELSE Nov.SumPrice - (Nov.SumQty * avg2.[Avg Price Per Quarter]) END as [Nov Impact], CASE WHEN avg2.[Avg Price Per Quarter] = 0 THEN 0 ELSE (CASE WHEN Nov.SumQty = 0 THEN 0 ELSE Nov.SumPrice / Nov.SumQty END) / avg2.[Avg Price Per Quarter] END as [Nov PPI],
Dec.SumQty as [Dec Qty], CASE WHEN Dec.SumQty = 0 THEN 0 ELSE Dec.SumPrice / Dec.SumQty END as [Dec Avg Price], Dec.SumPrice as [Dec Net Spend], CASE WHEN Dec.SumPrice = 0 THEN 0 ELSE Dec.SumPrice - (Dec.SumQty * avg2.[Avg Price Per Quarter]) END as [Dec Impact], CASE WHEN avg2.[Avg Price Per Quarter] = 0 THEN 0 ELSE (CASE WHEN Dec.SumQty = 0 THEN 0 ELSE Dec.SumPrice / Dec.SumQty END) / avg2.[Avg Price Per Quarter] END as [Dec PPI],
Jan.SumQty as [Jan Qty], CASE WHEN Jan.SumQty = 0 THEN 0 ELSE Jan.SumPrice / Jan.SumQty END as [Jan Avg Price], Jan.SumPrice as [Jan Net Spend], CASE WHEN Jan.SumPrice = 0 THEN 0 ELSE Jan.SumPrice - (Jan.SumQty * avg3.[Avg Price Per Quarter]) END as [Jan Impact], CASE WHEN avg3.[Avg Price Per Quarter] = 0 THEN 0 ELSE (CASE WHEN Jan.SumQty = 0 THEN 0 ELSE Jan.SumPrice / Jan.SumQty END) / avg3.[Avg Price Per Quarter] END as [Jan PPI],
Feb.SumQty as [Feb Qty], CASE WHEN Feb.SumQty = 0 THEN 0 ELSE Feb.SumPrice / Feb.SumQty END as [Feb Avg Price], Feb.SumPrice as [Feb Net Spend], CASE WHEN Feb.SumPrice = 0 THEN 0 ELSE Feb.SumPrice - (Feb.SumQty * avg3.[Avg Price Per Quarter]) END as [Feb Impact], CASE WHEN avg3.[Avg Price Per Quarter] = 0 THEN 0 ELSE (CASE WHEN Feb.SumQty = 0 THEN 0 ELSE Feb.SumPrice / Feb.SumQty END) / avg3.[Avg Price Per Quarter] END as [Feb PPI],
Mar.SumQty as [Mar Qty], CASE WHEN Mar.SumQty = 0 THEN 0 ELSE Mar.SumPrice / Mar.SumQty END as [Mar Avg Price], Mar.SumPrice as [Mar Net Spend], CASE WHEN Mar.SumPrice = 0 THEN 0 ELSE Mar.SumPrice - (Mar.SumQty * avg3.[Avg Price Per Quarter]) END as [Mar Impact], CASE WHEN avg3.[Avg Price Per Quarter] = 0 THEN 0 ELSE (CASE WHEN Mar.SumQty = 0 THEN 0 ELSE Mar.SumPrice / Mar.SumQty END) / avg3.[Avg Price Per Quarter] END as [Mar PPI],
Apr.SumQty as [Apr Qty], CASE WHEN Apr.SumQty = 0 THEN 0 ELSE Apr.SumPrice / Apr.SumQty END as [Apr Avg Price], Apr.SumPrice as [Apr Net Spend], CASE WHEN Apr.SumPrice = 0 THEN 0 ELSE Apr.SumPrice - (Apr.SumQty * avg4.[Avg Price Per Quarter]) END as [Apr Impact], CASE WHEN avg4.[Avg Price Per Quarter] = 0 THEN 0 ELSE (CASE WHEN Apr.SumQty = 0 THEN 0 ELSE Apr.SumPrice / Apr.SumQty END) / avg4.[Avg Price Per Quarter] END as [Apr PPI],
May.SumQty as [May Qty], CASE WHEN May.SumQty = 0 THEN 0 ELSE May.SumPrice / May.SumQty END as [May Avg Price], May.SumPrice as [May Net Spend], CASE WHEN May.SumPrice = 0 THEN 0 ELSE May.SumPrice - (May.SumQty * avg4.[Avg Price Per Quarter]) END as [May Impact], CASE WHEN avg4.[Avg Price Per Quarter] = 0 THEN 0 ELSE (CASE WHEN May.SumQty = 0 THEN 0 ELSE May.SumPrice / May.SumQty END) / avg4.[Avg Price Per Quarter] END as [May PPI],
Jun.SumQty as [Jun Qty], CASE WHEN Jun.SumQty = 0 THEN 0 ELSE Jun.SumPrice / Jun.SumQty END as [Jun Avg Price], Jun.SumPrice as [Jun Net Spend], CASE WHEN Jun.SumPrice = 0 THEN 0 ELSE Jun.SumPrice - (Jun.SumQty * avg4.[Avg Price Per Quarter]) END as [Jun Impact], CASE WHEN avg4.[Avg Price Per Quarter] = 0 THEN 0 ELSE (CASE WHEN Jun.SumQty = 0 THEN 0 ELSE Jun.SumPrice / Jun.SumQty END) / avg4.[Avg Price Per Quarter] END as [Jun PPI]
FROM [PPI].[dbo].[getQtyPriceByMonthYear](7 , @fyYear) AS Jul
LEFT JOIN [PPI].[dbo].[getQtyPriceByMonthYear](8 , @fyYear) AS Aug
ON Jul.[Part Number] = Aug.[Part Number]
LEFT JOIN [PPI].[dbo].[getQtyPriceByMonthYear](9 , @fyYear) AS Sep
ON Jul.[Part Number] = Sep.[Part Number]
LEFT JOIN [PPI].[dbo].[getQtyPriceByMonthYear](10 , @fyYear) AS Oct
ON Jul.[Part Number] = Oct.[Part Number]
LEFT JOIN [PPI].[dbo].[getQtyPriceByMonthYear](11 , @fyYear) AS Nov
ON Jul.[Part Number] = Nov.[Part Number]
LEFT JOIN [PPI].[dbo].[getQtyPriceByMonthYear](12 , @fyYear) AS Dec
ON Jul.[Part Number] = Dec.[Part Number]
LEFT JOIN [PPI].[dbo].[getQtyPriceByMonthYear](1 , @nextYear) AS Jan
ON Jul.[Part Number] = Jan.[Part Number]
LEFT JOIN [PPI].[dbo].[getQtyPriceByMonthYear](2 , @nextYear) AS Feb
ON Jul.[Part Number] = Feb.[Part Number]
LEFT JOIN [PPI].[dbo].[getQtyPriceByMonthYear](3 , @nextYear) AS Mar
ON Jul.[Part Number] = Mar.[Part Number]
LEFT JOIN [PPI].[dbo].[getQtyPriceByMonthYear](4 , @nextYear) AS Apr
ON Jul.[Part Number] = Apr.[Part Number]
LEFT JOIN [PPI].[dbo].[getQtyPriceByMonthYear](5 , @nextYear) AS May
ON Jul.[Part Number] = May.[Part Number]
LEFT JOIN [PPI].[dbo].[getQtyPriceByMonthYear](6 , @nextYear) AS Jun
ON Jul.[Part Number] = Jun.[Part Number]
LEFT JOIN [PPI].[dbo].getLastYearsQuarterAvgPrice(7, @fyYear) as avg1
ON Jul.[Part Number] = avg1.[Part Number]
LEFT JOIN [PPI].[dbo].getLastYearsQuarterAvgPrice(10, @fyYear) as avg2
ON Jul.[Part Number] = avg2.[Part Number]
LEFT JOIN [PPI].[dbo].getLastYearsQuarterAvgPrice(1, @nextYear) as avg3
ON Jul.[Part Number] = avg3.[Part Number]
LEFT JOIN [PPI].[dbo].getLastYearsQuarterAvgPrice(4, @nextYear) as avg4
ON Jul.[Part Number] = avg4.[Part Number]
ORDER BY Jul.[Part Number]
END
December 3, 2008 at 1:24 pm
What I would suggest is using the TVF to load properly indexes temporary tables with the appropriate data, then use the temporary table in your master query for reporting.
December 3, 2008 at 1:59 pm
First of all I would start using the correct datatype to query.
Use a datetime variable to query your dates.
You can do that changing the functions inputparameters to a single date (e.g. convention is to provide year-month-01 for data of that month)
Then you can query using
declare @toDate datetime
Select @toDate = dateadd(mm,1,@inDate)
Select ....
from ...
Where thedatecolumn >= @inDate
and thedatecolumn <= @toDate
Because you no longer use a function in the where clause of your query, it may be able to use an available index for filtering on that column.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 3, 2008 at 3:00 pm
Whetever you were putting in front of your 6's in the first function is lost. Could you please fix it? thnx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 3, 2008 at 3:08 pm
In fact the whole WHERE clause of the first function seems to be very confused. The is definitely something wrong with it because it won't compile, even if I put a "<" in front of the 6's.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 4, 2008 at 6:31 am
I have fixed the first function... we have filtered/proxied internet, which wouldnt allow me to post, so I had to email myself everything, not sure how it got lost. That code just says group up the data into 1st/2nd/3rd/4th quarters of last year.
I have to implement this so it appears to follow the financial year (July-June), hence me sending int for month and year. I wasn't sure how many further steps I would need to complete all the calculations, so I used the ints so I could understand my programming a bit better. Have you noticed I am not a SQL guru?
The view is off a "database" but from my understanding, it is just a snapshot loaded every night. I think the suggestion to copy it into a temporary table is the way to go here... except it doesn't really need to be a temporary table.
My understanding of indexing is a little weak. I figure I am going to make it a job that runs an hour or so after the snapshot is inserted. Would I want to enable full text indexing for better searches? Unfortunately, all the data from the snapshot is pretty much text data, and I will need to use LIKE a bit further on. Or is it just CREATE INDEX you think I will need? Also, is there a way to CREATE INDEX on all columns without having to specify them, as I may change what columns are avaliable in the view.
Thanks for your help!
December 4, 2008 at 6:44 am
The structure of the where clause appears very inefficient to me. It looks like it is doing 16 table scans when it could get away with only 3.
I you repost just the corrected WHERE clause of that function, I will try to rewrite it for you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 4, 2008 at 6:53 am
Here is the where clause..
WHERE DATEPART(m, [Recd Date]) 6 AND @month <= 9 THEN 9 ELSE 12 END
AND DATEPART(m, [Recd Date]) >= CASE WHEN @month 6 AND @month <= 9 THEN 7 ELSE 10 END
AND DATEPART(yyyy, [Recd Date]) = CONVERT(varchar(4),@year - 1)
Or for simplified reading...
If the @month is between July and Sept, then I want everything with a [Recd Date] between July 1 and Sept 30 of @year - 1... etc for Oct-Dec, Jan-March, April-June.
Also, I couldn't figure out why it wouldn't allow me to DECLARE a variable in a tvf, else that would be much more readable.
December 4, 2008 at 6:58 am
What is your fiscal year for reporting? For instance Fiscal Year 2009 (our current fiscal year) is July 1, 2008 through June 30, 2009.
December 4, 2008 at 7:02 am
Andrew.Buis (12/4/2008)
Also, I couldn't figure out why it wouldn't allow me to DECLARE a variable in a tvf, else that would be much more readable.
Because you are working with an in-line TVF not a multistatement TVF.
For more information on these please read BOL (Books Online).
December 4, 2008 at 7:09 am
Same as yours Lynn. For FY 2009, it is July 08 - June 09
December 4, 2008 at 7:17 am
Well, found out partially why it takes so long. I am dumping the data from the view into a table... 15 or so minutes now, with 189k rows atm, and still going. Looks like I should also optimize what data I need to dump into my table.
Update: 216k rows, just finished. Changed my functions to point at the table instead of the view, and it now runs in 4 seconds!
December 4, 2008 at 8:52 pm
Andrew.Buis (12/4/2008)
Here is the where clause..
WHERE DATEPART(m, [Recd Date]) 6 AND @month <= 9 THEN 9 ELSE 12 END
AND DATEPART(m, [Recd Date]) >= CASE WHEN @month 6 AND @month <= 9 THEN 7 ELSE 10 END
AND DATEPART(yyyy, [Recd Date]) = CONVERT(varchar(4),@year - 1)
This still isn't right. The security filter must be eating it, try attaching it as a test file.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply