How to increase table response time

Hi,

I've a table with about 15,379,759 records in the past six months.

I only maintain a data of about last six months in this table & move earlier part to another table.

Now with a growing database this table too increasing manifold & its getting very time-consuming to retrieve a few set of records say a week-wise consolidated data for last one month as the query has to go all the way up & down several times.

So I wanted to know a better way to organize this table. I don't want to break the table into too many parts.

Any suggestions would be welcome either to somehow improve table response time or, ways to improve query or, design modification or, any other method.



Thanks

Ankit Mathur

Do you use any indexes?

Hi,

As of now NO I don't use any index on this table & I intend to make a index based on Datetime field

However, I know for sure an index only by itself won't be able to satisfy my requirements.

So I wanted some other modes also.

What is your table structure? What fields do you use for your select?

I have a nonclustered, unique, primary key located on PRIMARY ID

Mostly the parameters in where clause would call for AccountId, StartDatetime & ToDatetime.

May I also add that this is already an archive table. whereby everday last day's entry from the main table is inserted so as to enable us to maintain a decent performance for the live data from main table. Any data that has to be accessed later than a day would require me to come to this table.

Another thing I'd like to add is that I don't only require this table for fetching month wise reports.

Even single record may have to be taken out based on classification parameters. Its actually maintaining all the call records for a user.

So user specific call record on a date are also used.


So I wanted to know a better way to organize this table. I don't want to break the table into too many parts.

Any suggestions would be welcome either to somehow improve table response time or, ways to improve query or, design modification or, any other method.


I hope I've provided sufficent information and am hoping that somebody could guide me towards a proper way to increase the table's response time.

Ankit Mathur

My bet would be that AccountId, StartDatetime & ToDatetime all need indexes; and preferably be kept out of the way of large amounts of data.

Is it an option to create a new table with AccountId, StartDatetime & ToDatetime and a second one with AccountId and the rest of your columns?

All your selects would be on a smaller table, and you could use a join to fetch the rest of the data.

Since it's an archive table, a couple of extra indexes won't hurt too much; inserts might be slower, but that's probably a price you're willing to pay.

If you , compare some strings in your query ,
You can check for fulltextsearch ........