October 10, 2012 at 9:57 am
Hi everyone,
I ran the missing indexes DMV and I'm trying to figure out what indexes need to be applied based on the results.
ObjecttName Equality_Columns Inequality_Columns
[dbo].[APP_DEAL] [OFFR_TYPE_CD] [CRETD_DT]
[dbo].[APP_DEAL] [OFFR_TYPE_CD]
[dbo].[APP_DEAL] [LKUP_ID], [ATTR_VAL]
I understand that I still need to do a lot of digging into this before I go ahead and create indexes based on the results of this DMV but as a starting point I'd like to create these indexes on a dev box and see if this improves the performance.
So, can I create separate NC indexes on OFFR_TYPE_CD and CRETD_DT columns also can I create a composite index on [LKUP_ID] and [ATTR_VAL] columns?
October 10, 2012 at 11:29 pm
sunny.tjk (10/10/2012)
So, can I create separate NC indexes on OFFR_TYPE_CD and CRETD_DT columns
No. Single composite index is better.
also can I create a composite index on [LKUP_ID] and [ATTR_VAL] columns?
Yes.
October 11, 2012 at 6:29 am
Suresh B. (10/10/2012)
sunny.tjk (10/10/2012)
So, can I create separate NC indexes on OFFR_TYPE_CD and CRETD_DT columnsNo. Single composite index is better.
also can I create a composite index on [LKUP_ID] and [ATTR_VAL] columns?
Yes.
If i create a single composite index on [LKUP_ID] and [ATTR_VAL] columns, will the index be useful if I run a query like this:
SELECT LKUP_ID, ATTR_VAL
FROM [APP_DEAL]
WHERE ATTR_VAL=97067
My question is, will the index be useful only if I use both LKUP_ID and ATTR_VAL in the WHERE clause or can I use either one of these columns in the WHERE clause.
October 11, 2012 at 8:57 pm
Any suggestions, please?
October 11, 2012 at 9:15 pm
If i create a single composite index on [LKUP_ID] and [ATTR_VAL] columns, will the index be useful if I run a query like this:
SELECT LKUP_ID, ATTR_VAL
FROM [APP_DEAL]
WHERE ATTR_VAL=97067
My question is, will the index be useful only if I use both LKUP_ID and ATTR_VAL in the WHERE clause or can I use either one of these columns in the WHERE clause.[/quote]
The index will be ued if you filter on either LKUP_ID or LKUP_ID and ATTR_VAL. Think of a phone book and the way it stores and sorts information, and imagine LKUP_ID as last name and ATTR_VAL as first name. If you search for Jones and then look for Andrew the sort order in the book helps, but you can't use it to find Andrew and then filter to Jones.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply