基于日期时间数据类型优化搜索机制 [英] Optimise searching Mechanism based on date time datatype

问题描述

我有一张包含超过100,000条记录的表格，随着表格中的数据堆积，搜索机制变得越来越慢。



架构有点类似

 ENQ_ID FACULTY日期
 141 2411 2004-03 -21 
 1412 2442 2009-11-1 
 2341 2567 2010-02-17 
 4312 2669 2014-06-16 
 

我想快速搜索日期列，因为所有记录列都在2004年到2014年的年份范围内。



搜索应该基于日期列的年份部分的索引。



如何在此日期列的年份部分强加索引。

解决方案

------------------------------------------ ---

这是我的存储过程

-------------------------- -------------------

 创建  PROCEDURE  [dbo]。[USP_GET_ADMISSION_SUMMARY_REPORT] 
 （
  @ DATE_FROM   DATETIME ，
  @DATE_TO   DATETIME ，
  @ CENTRE_ID  INT = -1 
）
  AS   BEGIN  
 
  SELECT  CENTRE_NAME，[TOTAL SAIG]，[TOTAL BCA]，[TOTAL SAIG BCA]，[TOTAL REGULAR]，[TOTAL ADMISSIONS]，[SAIG BILLING]，[BCA BILLING]，[SAIG BCA] BILLING]，[常规结算]，[总结算]，[SAIG COLLECTION]，[BCA COLLECTION]，[SAIG BCA COLLECTION]，[REGULAR COLLECTI 
  ON ]，[TOTAL COLLECTION] 
  FROM  
（
  SELECT  
 
 ADMISSION_MASTER.CENTRE_ID，
 COUNT（*） AS  TOTAL_ADMISSIONS，
 COURSE = CASE 
  w ^ HEN  COURSE_TAKEN  LIKE  ' ％SAIG％'   AND  COURSE_TAKEN  NOT   LIKE  ' ％BCA％' 那么 '  TOTAL SAIG' 
  WHEN  COURSE_TAKEN  LIKE  ' ％BCA％'  AND  COURSE_TAKEN  NOT   LIKE  ' ％SAIG％' 那么 '  TOTAL BCA' 
  WHEN  CO URSE_TAKEN  LIKE  ' ％SAIG％BCA％'  THEN  '  TOTAL SAIG BCA' 
  ELSE  '  TOTAL REGULAR' 
  END  
  FROM  
 
 ADMISSION_MASTER 
 
  WHERE （ADMISSION_MASTER.ADMISSION_DATE  BETWEEN   @ DATE_FROM   AND   @ DATE_TO ）
  AND （ADMISSION_MASTER.CENTRE_ID = @ CENTRE_ID  OR  @ CENTRE_ID = -1）
 
  GROUP   BY  CENTRE_ID，COURSE_TAKEN 
 
 
  UNION   ALL  
 
  SELECT  
 
 ADMISSION_MASTER.CENTRE_ID，
 COUNT（*） AS  TOTAL_ADMISSIONS，
 COURSE = '  TOTAL ADMISSIONS' 
  FROM  
 
 ADMISSION_MASTER 
 
  WHERE （ADMISSION_MASTER.ADMISSION_DATE  BETWEEN   @ DATE_FROM   AND   @ DATE_TO ）
  AND （ADMISSION_MASTER.CENTRE_ID = @ CENTRE_ID  OR  @ CENTRE_ID = -1）
 
  GROUP   BY  CENTRE_ID，COURSE_TAKEN 
 
 
 
  UNION   ALL  
 
  SELECT  
 
 ADMISSION_MASTER.CENTRE_ID，
 SUM（TOTALFEES），
 COURSE = CASE 
  WHEN  COURSE_TAKEN  LIKE  ' ％SAIG％'  AND （COURSE_TAKEN  NOT   LIKE  ' ％BCA％'） THEN  '  SAIG BILLING' 
  WHEN  COURSE_TAKEN  LIKE  ' ％BCA％'  AND （COURSE_TAKEN 不  LIKE  ' ％SAIG ％'）那么 '  BCA BILLING'  
  WHEN  COURSE_TAKEN  LIKE  '  ％SAIG％BCA％' 那么 '   SAIG BCA BILLING' 
  ELSE  ' 常规结算' 
 结束 
  FROM  
 
 ADMISSION_MASTER 
 
  WHERE （ADMISSION_MASTER.ADMISSION_DATE  BETWEEN   @ DATE_FROM   AND   @ DATE_TO ）
  AND （ADMISSION_MASTER .CENTRE_ID = @ CENTRE_ID  OR  @ CENTRE_ID = -1）
  GROUP   BY  CENTRE_ID，COURSE_TAKEN 
 
 
  UNION   ALL  
 
  SELECT  
 
 ADMISSION_MASTER.CENTRE_ID，
 SUM（TOTALFEES），
 COURSE = '  TOTAL BILLING' 
  FROM  
 
 ADMISSION_MASTER 
 
  WHERE （ADMISSION_MASTER.ADMISSION_DATE  BETWEEN   @ DATE_FROM   AND   @ DATE_TO ）
  AND （ADMISSION_MASTER.CENTRE_ID =@CENTRE_ID  OR  @ CENTRE_ID = -1）
  GROUP   BY  CENTRE_ID 
 
  UNION   ALL  
 
  SELECT  
 ADMISSION_RECEIPTS.CENTRE_ID，
 SUM（ADMISSION_RECEIPTS.AMOUNT_PAID_FEES） AS  COLLECTION，
 COURSE = CASE 
  WHEN  COURSE_TAKEN  LIKE  ' ％SAIG％'  AND  COURSE_TAKEN  NOT   LIKE  ' ％BCA％' 那么 '  SAIG COLLECTION' 
  WHEN  COURSE_TAKEN  LIKE  ' ％BCA％'  AND  COURSE_TAKEN 不  LIKE  ' ％ SAIG％'  THEN  '  BCA COLLECTION'  
  WHEN  COURSE_TAKEN  LIKE  '  ％SAIG BCA％' 那么 '  SAIG BCA COLLECTION' 
  ELSE   ' 常规收集' 
  E ND  
  FROM  
 ADMISSION_RECEIPTS  INNER   JOIN  ADMISSION_MASTER 
  ON  ADMISSION_RECEIPTS.ADMISSION_ID = ADMISSION_MASTER.ADMISSION_ID 
  WHERE  
（ADMISSION_RECEIPTS.PAYMENT_DATE  BETWEEN   @ DATE_FROM   AND   @ DATE_TO ）
  AND （ADMISSION_RECEIPTS.CENTRE_ID = @CENTRE_ID  OR  @ CENTRE_ID = -1）
 
  GROUP   BY  
 ADMISSION_RECEIPTS.CENTRE_ID，COURSE_TAKEN 
 
 
  UNION   ALL  
 
  SELECT  
 ADMISSION_RECEIPTS.CENTRE_ID，
 SUM（ADMISSION_RECEIPTS.AMOUNT_PAID_FEES） AS  COLLECTION，
 COURSE = '   TOTAL COLLECTION' 
 
  FROM  
 ADMISSION_RECEIPTS < span class =code-keyword> INNER   JOIN  ADMISSION_MASTER 
  ON  ADMISSION_RECEIPTS .ADMISSION_ID = ADMISSION_MASTER.ADMISSION_ID 
  WHERE  
（ADMISSION_RECEIPTS.PAYMENT_DATE  BETWEEN   @ DATE_FROM   AND   @ DATE_TO ）
  AND （ADMISSION_RECEIPTS.CENTRE_ID = @ CENTRE_ID  OR  @ CENTRE_ID = -1）
 
  GROUP   BY  
 ADMISSION_RECEIPTS.CENTRE_ID 
 
 
） AS   SOURCE  
 PIVOT 
（
 SUM（TOTAL_ADMISSIONS） FOR  COURSE  IN （[TOTAL REGULAR]，[TOTAL SAIG]，[TOTAL BCA]，[TOTAL SAIG BCA]，[TOTAL ADMISSIONS]，[SAIG BILLING]，[BCA BILLING]，[SAIG BCA BILLING]， [常规收费]，[总收费]，[SAIG COLLECTION]，[BCA COLLECTION]，[SAIG BCA COLLECTION]，[R 
 EGULAR COLLECTION]，[TOTAL COLLECTION] 
）
）  AS  PVT 
 
  RIGHT   OUTER   JOIN  CENTRE_MASTER 
  ON  CENTRE_MASTER.CENTRE_ID = PVT.CENTRE_ID 
  WHERE  
 CENTRE_MASTER.CENTRE_NAME  NOT   LIKE  ' ％TEST％' 
  AND  CENTRE_MASTER.ISACTIVE = 1 
  AND （CENTRE_MASTER.CENTRE_ID = @CENTRE_ID 或 @CENTRE_ID = -1）
 
  ORDER   BY  [TOTAL COLLECTION]  DESC ，[TOTAL BILLING]  DESC  
  END  

I have a table with above 100,000 records,The searching mechanism has grown slower as the data in the table is piling on.

the schema is some what like

ENQ_ID         FACULTY             Date
 141               2411               2004-03-21
 1412          2442               2009-11-1
 2341          2567               2010-02-17
 4312          2669               2014-06-16

I want to make search fast making Date column as all the records column fall in the year range of 2004 to 2014.

The search should work based on the indexing on the year part of the date column.

How can I impose index on the year part of this date column.

解决方案

---------------------------------------------
This is my Stored Proc
---------------------------------------------

CREATE  PROCEDURE [dbo].[USP_GET_ADMISSION_SUMMARY_REPORT]  
(  
@DATE_FROM DATETIME,  
@DATE_TO DATETIME,  
@CENTRE_ID INT=-1  
)  
AS BEGIN  
  
SELECT CENTRE_NAME, [TOTAL SAIG],[TOTAL BCA],[TOTAL SAIG BCA],[TOTAL REGULAR],[TOTAL ADMISSIONS],[SAIG BILLING],[BCA BILLING],[SAIG BCA BILLING], [REGULAR BILLING], [TOTAL BILLING],[SAIG COLLECTION],[BCA COLLECTION],[SAIG BCA COLLECTION],[REGULAR COLLECTI
ON],[TOTAL COLLECTION]  
 FROM   
(  
SELECT  
   
 ADMISSION_MASTER.CENTRE_ID,  
 COUNT(*) AS TOTAL_ADMISSIONS,  
 COURSE=CASE   
 WHEN COURSE_TAKEN LIKE '%SAIG%' AND COURSE_TAKEN NOT LIKE '%BCA%' THEN 'TOTAL SAIG'  
 WHEN COURSE_TAKEN LIKE '%BCA%' AND COURSE_TAKEN NOT LIKE '%SAIG%' THEN 'TOTAL BCA'  
 WHEN COURSE_TAKEN LIKE '%SAIG%BCA%' THEN 'TOTAL SAIG BCA'  
 ELSE 'TOTAL REGULAR'  
 END  
FROM  
  
 ADMISSION_MASTER  
   
 WHERE (ADMISSION_MASTER.ADMISSION_DATE BETWEEN @DATE_FROM AND @DATE_TO)  
 AND (ADMISSION_MASTER.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)  
   
 GROUP BY CENTRE_ID, COURSE_TAKEN  
   
  
UNION ALL  
  
SELECT  
   
 ADMISSION_MASTER.CENTRE_ID,  
 COUNT(*) AS TOTAL_ADMISSIONS,  
 COURSE='TOTAL ADMISSIONS'  
FROM  
  
 ADMISSION_MASTER  
   
 WHERE (ADMISSION_MASTER.ADMISSION_DATE BETWEEN @DATE_FROM AND @DATE_TO)  
 AND (ADMISSION_MASTER.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)  
   
 GROUP BY CENTRE_ID, COURSE_TAKEN  
   
   
  
UNION ALL  
  
SELECT  
   
 ADMISSION_MASTER.CENTRE_ID,  
 SUM(TOTALFEES),  
 COURSE=CASE   
 WHEN COURSE_TAKEN LIKE '%SAIG%' AND (COURSE_TAKEN NOT LIKE '%BCA%') THEN 'SAIG BILLING'  
 WHEN COURSE_TAKEN LIKE '%BCA%' AND (COURSE_TAKEN NOT LIKE '%SAIG%') THEN 'BCA BILLING'  
 WHEN COURSE_TAKEN LIKE '%SAIG%BCA%' THEN 'SAIG BCA BILLING'  
 ELSE 'REGULAR BILLING'  
 END  
FROM  
  
 ADMISSION_MASTER  
   
 WHERE (ADMISSION_MASTER.ADMISSION_DATE BETWEEN @DATE_FROM AND @DATE_TO)  
 AND (ADMISSION_MASTER.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)  
 GROUP BY CENTRE_ID, COURSE_TAKEN   
  
   
UNION ALL  
  
SELECT  
   
 ADMISSION_MASTER.CENTRE_ID,  
 SUM(TOTALFEES),  
 COURSE='TOTAL BILLING'  
FROM  
  
 ADMISSION_MASTER  
   
 WHERE (ADMISSION_MASTER.ADMISSION_DATE BETWEEN @DATE_FROM AND @DATE_TO)  
 AND (ADMISSION_MASTER.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)  
 GROUP BY CENTRE_ID  
  
UNION ALL  
  
SELECT   
 ADMISSION_RECEIPTS.CENTRE_ID,  
 SUM(ADMISSION_RECEIPTS.AMOUNT_PAID_FEES) AS COLLECTION,  
 COURSE=CASE   
 WHEN COURSE_TAKEN LIKE '%SAIG%' AND COURSE_TAKEN NOT LIKE '%BCA%' THEN 'SAIG COLLECTION'  
 WHEN COURSE_TAKEN LIKE '%BCA%' AND COURSE_TAKEN NOT LIKE '%SAIG%' THEN 'BCA COLLECTION'  
 WHEN COURSE_TAKEN LIKE '%SAIG BCA%' THEN 'SAIG BCA COLLECTION'  
 ELSE 'REGULAR COLLECTION'  
 END  
FROM   
 ADMISSION_RECEIPTS INNER JOIN ADMISSION_MASTER  
 ON ADMISSION_RECEIPTS.ADMISSION_ID = ADMISSION_MASTER.ADMISSION_ID  
WHERE   
 (ADMISSION_RECEIPTS.PAYMENT_DATE BETWEEN @DATE_FROM AND @DATE_TO)  
 AND (ADMISSION_RECEIPTS.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)  
   
GROUP BY   
 ADMISSION_RECEIPTS.CENTRE_ID, COURSE_TAKEN  
   
  
UNION ALL  
  
SELECT   
 ADMISSION_RECEIPTS.CENTRE_ID,  
 SUM(ADMISSION_RECEIPTS.AMOUNT_PAID_FEES) AS COLLECTION,  
 COURSE='TOTAL COLLECTION'  
  
FROM   
 ADMISSION_RECEIPTS INNER JOIN ADMISSION_MASTER  
 ON ADMISSION_RECEIPTS.ADMISSION_ID = ADMISSION_MASTER.ADMISSION_ID  
WHERE   
 (ADMISSION_RECEIPTS.PAYMENT_DATE BETWEEN @DATE_FROM AND @DATE_TO)  
 AND (ADMISSION_RECEIPTS.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)  
   
GROUP BY   
 ADMISSION_RECEIPTS.CENTRE_ID  
  
   
) AS SOURCE  
PIVOT  
(  
SUM(TOTAL_ADMISSIONS) FOR COURSE IN ([TOTAL REGULAR],[TOTAL SAIG],[TOTAL BCA],[TOTAL SAIG BCA],[TOTAL ADMISSIONS],[SAIG BILLING],[BCA BILLING],[SAIG BCA BILLING],[REGULAR BILLING],[TOTAL BILLING],[SAIG COLLECTION],[BCA COLLECTION],[SAIG BCA COLLECTION],[R
EGULAR COLLECTION],[TOTAL COLLECTION]  
)  
) AS PVT  
  
RIGHT OUTER JOIN CENTRE_MASTER  
ON CENTRE_MASTER.CENTRE_ID=PVT.CENTRE_ID   
WHERE  
CENTRE_MASTER.CENTRE_NAME NOT LIKE '%TEST%'  
AND CENTRE_MASTER.ISACTIVE=1  
AND (CENTRE_MASTER.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)  
  
ORDER BY [TOTAL COLLECTION] DESC ,[TOTAL BILLING] DESC   
END  

这篇关于基于日期时间数据类型优化搜索机制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！