开发者

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])
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜