How to build lookup table

Question

Post reply

How to build lookup table

chung-227895

SSC Enthusiast

Points: 177
More actions
June 22, 2005 at 7:27 pm

#152445

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

make the tTransToDate the clustered index (and primary if possible), and see how you're query will speed up .

chung-227895 SSC Enthusiast Points: 177 More actions · Answer 2

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????

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

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/

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

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/

chung-227895 SSC Enthusiast Points: 177 More actions · Answer 5

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

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 6

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