How to create a date lookup table to speed up stored procs?
I want to reduce the time it takes for one of my stored procs that currently uses the following logic to calculate the date field, both in Select and Group portion:
left(datename(month, a.QXP_REPORT_DATE), 3) + ' ''' + right(datename(year, a.QXP_REPORT_DATE), 2)
Would a simple lookup table take less time? If so, then how would I populate for the following fields for all dates in the last 2 years?
CREATE TABLE #CALENDAR(
FULLDATE DATETIME,
MONTHNAME NVARCHAR(3),
sYEAR SMALLINT
)
INSERT INTO #CALENDAR
SELECT '4/19/2011', left(datename(month, '4/19/2011'), 3), right(datename(year, '4/19/2011'), 2)
I'm starting to think maybe a function call would be better than a lookup table. Here is all of my SQL stored proc:
DECLARE
@FirstMonthDate DATETIME,
@LastMonthDate DATETIME,
@TheLevel INT,
@ProductGroup VARCHAR(255),
@TheCategory VARCHAR(255),
@ListNumber VARCHAR(50)
--AS
-- SET NOCOUNT ON;
--ComplaintTrendingDrillDown3p '3/1/10', '3/31/11 23:59:59', 3 , 'RealTime IVD', 'Procedure Not Followed', ''
SET @FirstMonthDate = '3/1/11'
SET @LastMonthDate = '3/31/11 23:59:59'
SET @TheLevel = 3
SET @ProductGroup = 'RealTime IVD'
SET @TheCategory = 'Procedure Not followed'
--SET @ListNumber = '2G31-90'
DECLARE @SelectedLevels table (LevelId int not null primary key)
declare @OneYearAgo datetime
set @OneYearAgo = dateadd(year, -1, @FirstMonthDate)
IF @TheLevel = 3
BEGIN
INSERT INTO @SelectedLevels (LevelId) VALUES (1)
INSERT INTO @SelectedLevels (LevelId) VALUES (2)
END
ELSE if @TheLevel = 5
BEGIN
INSERT INTO @SelectedLevels (LevelId) VALUES (0)
INSERT INTO @SelectedLevels (LevelId) VALUES (1)
INSERT INTO @SelectedLevels (LevelId) VALUES (2)
END
ELSE
BEGIN
INSERT INTO @SelectedLevels (LevelId) VALUES (@TheLevel)
END
SELECT count(distinct a.QXP_EXCEPTION_NO) AS QXP_EXCEPTION_NO, PRODUCT_CODE_STD, a.qxp_short_desc,
left(datename(month, a.QXP_REPORT_DATE), 3) + ' ''' +
right(datename(year, a.QXP_REPORT_DATE), 2) AS MonthYear ,
CASE WHEN a.QXP_SHORT_DESC = @TheCategory OR ISNULL(@TheCategory, '') = '' THEN 1 ELSE 0 END AS SELECTED_CATEGORY
FROM ALL_COMPLAINTS 开发者_开发问答a
INNER JOIN @SelectedLevels F ON A.[LEVEL] = F.LevelId
LEFT OUTER JOIN MANUAL.PRODUCTS b ON a.EPA_PRD_CODE = b.LIST_NUMBER
LEFT OUTER JOIN SMARTSOLVE.V_CXP_CUSTOMER_PXP c ON a.QXP_ID = c.QXP_ID
WHERE a.QXP_REPORT_DATE >= @OneYearAgo AND
a.QXP_REPORT_DATE <= @LastMonthDate AND a.QXP_SHORT_DESC <> 'Design Control'
AND (c.QXP_EXCEPTION_TYPE <> 'Non-Diagnostic' OR c.QXP_EXCEPTION_TYPE IS NULL)
AND PRODUCT_GROUP= @ProductGroup
AND (PRODUCT_CODE_STD = @ListNumber OR ISNULL(@ListNumber, '') = '')
and left(datename(month, a.QXP_REPORT_DATE), 3) = 'may'
GROUP BY PRODUCT_CODE_STD, left(datename(month, a.QXP_REPORT_DATE), 3) + ' ''' + right(datename(year, a.QXP_REPORT_DATE), 2) , a.qxp_short_desc
order by left(datename(month, a.QXP_REPORT_DATE), 3) + ' ''' +
right(datename(year, a.QXP_REPORT_DATE), 2), product_code_std, qxp_short_desc
Execution plan recommendations:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [SMARTSOLVE].[V_CXP_CUSTOMER_PXP] ([QXP_REPORT_DATE],[QXP_UDF_STRING_8],[QXP_XRS_DESCRIPTION])
INCLUDE ([QXP_ID],[QXP_EXCEPTION_NO],[QXP_BASE_EXCEPTION],[QXP_OCCURENCE_DATE],[QXP_COORD_ID],[QXP_ROOT_CAUSE],[QXP_DESCRIPTION],[QXP_QEI_ID],[QXP_EXCEPTION_TYPE],[QXP_UDF_STRING_2],[QXP_UDF_STRING_5],[CXP_ID],[CXP_AWARE_DATE],[QXP_XSV_CODE],[QXP_COORD_NAME],[QXP_ORU_NAME],[QXP_RESOLUTION_DESC],[QXP_CLOSED_DATE],[CXP_CLIENT_CODE],[CXP_CLIENT_NAME])
精彩评论