Hans1982


Hi guys , assuming there's a table called 'transactions' which contains more than 1 million records and normally will retrieve the records for monthly/yearly summary report. There are 5 fields within the 'transaction' table which are :

- transaction_ID (PRIMARY)

- transaction_Name

- transaction_Date

- transaction_Amount

- transaction_reference

Right now I would like to add indexing feature and I was wondering which use I take it as index for the effective way. Normally those summary report will sort by date. Is it take transaction_Date as non-cluster index n transaction_ID as unique cluster index. Hope able to get any assistance here. Thx a lot.

Best Regards,

Hans




Re: Wat's the effective way to improve performance in table using indexing?

Jens K. Suessmeyer


Well, that depends. As data is continously appended to the table in a date increasing manner and old data is not deleted, you should consider using a clustered index with a high fill factor. Could you explain the pupose and the lifetime of the transaction table

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---





Re: Wat's the effective way to improve performance in table using indexing?

Hans1982

I see. What if I do index partitioning on it as well The purpose of this transaction is table is to keep track all the transaction made by the users daily and able to use for reporting. (Total for daily,monthly and yearly report). Therefore, it needs to be available in the longer lifetime. Thx.

From,

Hans






Re: Wat's the effective way to improve performance in table using indexing?

Jens K. Suessmeyer

Hi,

yes you could use horizontal partition and use the date column as the differentiator for the partition split.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---





Re: Wat's the effective way to improve performance in table using indexing?

Hans1982

I see. How about transaction_ID (primary key) Is it take transaction_Date as non-cluster index n transaction_ID as unique cluster index as the conclusion

Best Regards,

Hans

.





Re: Wat's the effective way to improve performance in table using indexing?

azz

My approach would be to make transaction_date the clustered index and transaction_id a non clustered primary key. That should give the best performance for reading records by date as the data pages will be in the order you need them in.

Depending on your queries using the Included Columns feature may give you want you want. You could create a non clustered index on the date and then include the amount in the index, this will put the amount into the index pages together with the date and remove the need to perform a second lookup from the non clustered index to the data pages to get the amount. So getting the totals by date will only need to read the index pages, which will be more compact (and so have a shallower tree) than the index pages as each row will only have the id, date and amount.

If you have some natural partitions in the data, such as users then to query data within a year then it may be handy to use a horizontal partition. But I would look at tuning the indexes before partitioning.

Make a copy of the table, change the indexes and run the same query against both tables with the "show actual execution plan" option turned on in SSMS. It will show what proportion of the total script's execution time each query takes.

hope that helps,
aaron