Optimise searching Mechanism based on date time datatype

Question

0.00/5 (No votes)

See more:

, +

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

HTML

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.

Posted 5-Aug-14 21:15pm

Kunal Ved

Updated 5-Aug-14 21:18pm

v2

Add a Solution

Comments

CPallini 6-Aug-14 3:38am

What is the 'search mechanism'? Are you using BETWEEN?

Kunal Ved 6-Aug-14 3:46am

It is a stored Procedure,yes I have. I want to impose an Index on the column

Kornfeld Eliyahu Peter 6-Aug-14 5:22am

Even you do no tell us how your where clause (search mechanism?) build, I can give you some advice:
1. Use SQL analyzer to get some suggestions about new indexes
2. Even your table is a small one (100,000 is peanuts for SQL, large is over 50,000,000) consider partitioning...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kunal Ved · Answer 1 · 2014-08-05T23:42:00

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

SQL

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