March 12, 2014 at 3:02 am
Hi Guys,
I am new to SQL Server ,could any one help me in optimizing below SP
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[test]
@CustomerID INT ,
@LanguageID INT
--WITH EXEC AS CALLER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StartMonth AS DATETIME;
SET @startMonth = ( SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0));
DECLARE @CTEMONTH TABLE
(
Month_Name VARCHAR(10) ,
cnt SMALLINT ,
OutReachType VARCHAR(25)
);
WITH ctemonth AS
(
SELECT
@StartMonth AS Month_Name , 1 AS cnt
UNION ALL
SELECT
DATEADD(MONTH, -cnt, @StartMonth) , cnt + 1
FROM ctemonth
WHERE cnt < 6
)
INSERT INTO @CTEMONTH
(
Month_Name ,
Cnt ,
OutReachType
)
SELECT
REPLACE(RIGHT(CONVERT(VARCHAR(11), MONTH_NAME, 106), 8), ' ', '-'),
CNT ,
OUTREACHTYPE
FROM ctemonth
CROSS APPLY ( VALUES ( 'Email'), ( 'Fax'),( 'Post'), ( 'Registered and Beyond') ) TVC ( OutReachType );
WITH groupdata AS
(
SELECT
DISTINCT
RLP.RelationshipID ,
CASE WHEN SOR.UpdatedOn IS NULL
THEN REPLACE(RIGHT(CONVERT(VARCHAR(11), SOR.CreatedOn, 106), 8), ' ', '-')
ELSE REPLACE(RIGHT(CONVERT(VARCHAR(11), SOR.UpdatedOn, 106), 8), ' ', '-')
END AS OutReachedOn ,
OT.OutReachTypeName ,
RLP.ProfileStageID
FROM dbo.SupplierOutreach AS SOR
INNER JOIN dbo.OutreachStatus AS ORS ON SOR.OutreachStatusID = ORS.OutreachStatusID
INNER JOIN dbo.OutreachSetup AS OS ON SOR.OutreachSetupID = OS.OutreachSetupID
INNER JOIN dbo.OutreachType AS OT ON OS.OutreachTypeID = OT.OutreachTypeID
INNER JOIN dbo.Relationship AS RLP ON SOR.RelationshipID = RLP.RelationshipID
WHERE RLP.CustomerID = @CustomerID AND RLP.ProfileStageID != 10
UNION ALL
SELECT
DISTINCT
R.RelationshipID ,
REPLACE(RIGHT(CONVERT(VARCHAR(11), S.CreatedOn, 106), 8), ' ', '-'),
'Email' ,
ProfileStageID
FROM Supplier S
INNER JOIN Relationship R ON S.LavanteUID = R.LavanteUID AND R.CustomerID = @CustomerID
WHERE IsVMFLoad = 0 AND R.ProfileStageID != 10
UNION ALL
SELECT
DISTINCT
PSP.RelationshipID ,
REPLACE(RIGHT(CONVERT(VARCHAR(11), MIN(PSP.CreatedOn), 106), 8), ' ', '-'),
'Registered and Beyond' ,
7
FROM ProfileStageProgress PSP
INNER JOIN Relationship R ON R.RelationshipID = PSP.RelationshipID
INNER JOIN supplier S ON S.LavanteUID = R.LavanteUID
WHERE R.CustomerID = @CustomerID AND PSP.ProfileStageID IN ( 7, 8, 9 ) AND R.ProfileStageID != 10
GROUP BY PSP.RelationshipID
)
,CTEFinalData AS
(
SELECT
OutReachedOn ,
CASE
WHEN OutReachTypeName IS NULL THEN NULL
ELSE OutReachTypeName
END AS [Label] ,
CASE
WHEN OutReachTypeName IS NOT NULL
THEN COUNT(*)
ELSE COUNT(CASE WHEN ProfileStageID IN (7, 8, 9 ) THEN 1 END)
END AS [Count]
FROM GroupData
GROUP BY GROUPING SETS(( OUTREACHedOn , OutReachTypeName ), ( OutReachedOn ))
)
SELECT
TVC1.OutReachedOn,
UT.UTFString AS Label,
TVC1.[Count]
FROM
(
SELECT
TVC.Month_Name AS OutReachedOn ,
CASETVC.OutReachType
WHEN 'Email' THEN 'keyvalue.2001.Email'
WHEN 'Fax' THEN 'keyvalue.2003.Fax'
WHEN 'Post' THEN 'chart.post'
WHEN 'Registered and Beyond' THEN'chart.registeredANdBeyond'
END AS Label ,
ISNULL([COUNT], 0) AS [COUNT],
TVC.Cnt
FROM CTEFinalData C
RIGHT OUTER JOIN
(
SELECT *
FROM @ctemonth
) TVC ON c.OutReachedOn = TVC.Month_Name AND c.Label = TVC.OutreachType
)TVC1
INNER JOIN dbo.I18nkey I ON TVC1.Label = I.i18nkey
INNER JOIN dbo.UserTranslation UT ON I.i18nkey = UT.i18nkey AND UT.LanguageID = @LanguageID
ORDER BY TVC1.[CNT] DESC , UT.UTFString
END
March 12, 2014 at 3:22 am
Not without a lot more information.
Please post table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/. What are its current performance statistics? What is the required duration?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2014 at 7:31 am
At the very least please post the Actual Execution plan and give us some insight as to why you want it to optimize it in the first place - is it taking a long time to run? Generating significant high reads? Etc?
As Gail has stated, to really test and give you the best answer we need sample data and the DDL for the tables involved (so we can run it as well)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply