how to choose composite index

Question

Post reply

how to choose composite index

kiran-670359

SSC Eights!

Points: 853
More actions
March 10, 2008 at 5:53 am

#184871

Hi All,
🙂 I'm working in an MNC.presently i'm dealing with performance issues related to queries.i'm having a table called Lookup table.i am using two fields lkup_type and lkup_value together in most of my queries in the where clause as where lkup_type='PHONE' and lkup_value='1234' .can i use these two together as a composite index.will it improve my performance
Kiran

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

If the rest of the query is correct for it, yes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 2

Hi,

Yes you can have One Compiste index. But as i understand by the definion of LookUp table, Look Up tables are generelly very small. If it is small table then no need to have index.

How many records you have it in Lookup Table?

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

I disagree... a lookup or definition table better have a Primary Key...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kiran-670359 SSC Eights! Points: 853 More actions · Answer 4

thanks jeff,

one more thing.I am using Toad to know the explain plan of my queries.The query optimizer is taking nested loop join as best amoung hash join and merge join.but if i force my query to use hash join(using hints) the cost is getting reduced.Is it a gud practise to force my queries to use hints?

Kiran

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Not normally... of course, there are always exceptions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kiran-670359 SSC Eights! Points: 853 More actions · Answer 6

Jeff,

:blink: Can u give me more details regarding when can i use hints in my query.

Kiran.

Kiran

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Best thing to do is to look them up in Books Online. Bottom line is, if they work, then it's probably a good thing... if they don't work (as shown be execution plan and testing), then don't use them. Most of the time, you won't need to use them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kiran-670359 SSC Eights! Points: 853 More actions · Answer 8

kiran-670359

SSC Eights!

Points: 853

March 11, 2008 at 12:17 am

#787795

Thanks for the info.:) i will check it.

Kiran.

Kiran

Ian Yates SSCoach Points: 19738 More actions · Answer 9

Note that the query engine, when creating a plan, takes into account things such as table statistics, etc. If you are checking your query in a small development environment where the number of rows, and often their statistical distribution, is different from a production environment, then you may be hurting yourself by forcing a join type with a hint.

Also, it sounds like you have a fairly generic lookup table so I'm assuming the number of rows could get fairly large.

Your wording "composite index" is often referred to in these circles as a "covering index". If you look up that term on this site you'll get a lot of information 🙂

kiran-670359 SSC Eights! Points: 853 More actions · Answer 10

yes Ian, my lookup table has more than 1000 rows.

By composite index i mean that i can put the two conditions in the where clause lkup_type and lkup_value together as an index which i call it composite index.covering index means what i have indexed in my lookup table i'm trying read (if and only if i use those keys for joining) using select clause.

Or should i need to index lkup_type and lkup_value as two separate non-clustered indexes?

Kiran