hi,

  i am new member of the site.

     In my project archive table have lakh  of records in last  5 years , i am using full index for keyword search, but date wise search ( 1 year )  without keyword taking more time . my application in .net technology time out expire problem i am facing .

     any solution , kindly give to me

What means 'huge table'?

Has the table a primary key?

What's your search query?

hi jAuer,

              thanks for reply. i  mean    more than 8 lakh or lac records available in that table , no primary key ,  but have unique key field

  like , 

        select column1 , column2 from table_archives where  convert(nvarchar(10),  dateon ,112)>= '20021002'  and convert(nvarchar(10),  dateon ,112)<= '20030402'

dateon  datatype is datetime

retrieve  2lac records. it take more time  so  front end (asp.net) connection time out error occurs.

  Regards,

  Karthikeyan

Hi Karthikeyan,

Remove your CONVERTs, the one on the left-hand side will cause your index to be scanned. Bear in mind that if you return closer to 10% of the rows, the optimizer will probably opt for a scan anyway.

Use the following SET statements to investigate the query plan and the number of IOs (assuming you are not on a production server, use DBCC FREEPROCCACHE to clear the procedure cache between your runs).

 SET STATISTICS PROFILE ON

SET STATISTCIS IO ON

 -- your query goes here

DBCC FREEPROCCACHE

HTH

/Elisabeth

Add a primary key. The PK is used in indexes.

Change your query: Put the date into a variable and use something like

Where dateon Between @startdate And @enddate

I hope there is an index on the column dateon.