June 22, 2005 at 7:27 pm
Hi all,
I got the serious problem in searching data:
I have account payable transaction that store all transaction generated from ap module, there are million records in this table. I set identity in that table call ID_lTransaction.
The problem is when i need to search data in that table i often use column tTransFromDate and tTransToDate which stored date transaction occured but two that columns is not primary key so the performace is very slow.
How can i build lookup table to improve performance or set usefull index??
Thanks
June 22, 2005 at 8:08 pm
make the tTransToDate the clustered index (and primary if possible), and see how you're query will speed up .
June 23, 2005 at 1:20 am
Thanks Remi,
but the problem that if i set tTransToDate to cluster index, i have to set tTransFromDate to cluster index too. My table have many business fields and all of them can be included in search condition.
How can i solve that problem, set all of them to cluster index????
June 23, 2005 at 3:07 am
check out Kalen Delaney's books for advice. Build a secondary covered index to match your query - covered indexes will perform as well as as a clustered index.
Your other option is to build an indexed view to cover your query - this will effectively build a second clustered index on the table to match your query - the optimiser will see the index for queries against the base table.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 23, 2005 at 3:09 am
Oh sorry - horizontal table partitioning using partitioned views is another option.
The hardware option is to increase the number of spindles on the data drive - say your current drive has 4 disks, adding another 4 disks will double performance. ( in simplistic terms < grin > )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 23, 2005 at 3:18 am
thanks for all replies,
Could you mentions more details about two solutions:
1. How to build secondary covered index that match my query
2. Create indexed view to improve performance
can you give me a link please!!!
thanks
June 24, 2005 at 2:00 am
Chung,
A (secondary) covering index is an index where all columns are included which are part of your query (in the join, where and select, order by... clause)
To make your query efficient the (covering) index should start with the most selective columns of the where clause followed by all other columns of the where clause, then (if different) the columns from order by clause and the columns from the select part.
Of cours if your query is including a lot of columns then it is not quite efficient. In this case you could limit your indexed columns to the columns of the where clause.
The indexed view is an another story. your table has to be conform to ALL requirements. check the BOL for further informations about indexed views
As a general hint about cluster indexes: it mak sence to create a clustered index on the columns where you have range selections (between, <, >...) like selections not starting with a wildcard, order by clauses etc.
Also do not forget: an Index should be as narrow as possible to get it efficient
Bye
Gabor
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply