SQL Server Performance

How to increase table response time

Discussion in 'T-SQL Performance Tuning for Developers' started by ankitmathur, Oct 31, 2007.

  1. ankitmathur New Member

    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.
    Ankit Mathur
  2. anandchatterjee New Member

    1. You have not mentioned whether you are using Index or not.
    2. If you are using SQL Server 2005, then refer the table partitioning option.
    I think it will give give proper performance boosting..
  3. martins New Member

    With proper indexing it should not be a problem to query this table. Maybe posting the layout of your table, indexes and the source statement for the query might help us to give you better advice.
    One way of possibly organizing your table would be to keep only the last month's data in the table, and all other data in a history table. This is of course assuming that most of your queries/reports only runs off the last month's data.
    Hope this helps.
  4. ankitmathur New Member

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

    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.
    I'm maintaining only last six month's data in this table & all data even prior to that goes to another DB on a remote location.
    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.
    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
  5. martins New Member

    With what you have described above it seems like most queries will be based on some sort of time period. You need to have a look at the where-clauses of the most frequently used queries on this table and see if there is a pattern. If you find that one of the dates are used mostly, then create a non-clustered index on it. Same goes for any other columns frequently used to filter data in queries, or join to other tables with.
    Hope this helps.
  6. Adriaan New Member

    Before you add the index that martins suggested, change the PK so it is a clustered index. Identity columns are ideal candidates for clustered indexes.
  7. satya Moderator

    Also give us some information on the server hardware and total database size, if the resources are not good enough even having relevant indexes may not be helpful.
  8. ankitmathur New Member

    My Server Config. is
    Quadcore Dual Processor with 16GB RAM.
    DB size is 17GB & We have seperately assigned 13GB of RAM dedicated to SQL Server.
    I hope this should be sufficient info.
  9. satya Moderator

    What kind of queries you are running?
    Have you checked the execution plan for those queries?
    I believe by looking at the queries you are running we can suggest a good workaround rather than quoting the theoritical based suggstions.

Share This Page