indexing question

[habesha]

I have created a nonclustsred index on a column of a table that is refered in on stataement of a inner join statement;
but it is still going clustsred index scan,
what is the reason behind this and how can I solve this

Thanks

 

[ptheriault]

did you update statistics after you created the index?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Flofy]

Check the Execution plan to see what index it is using. If you can show us that it will help

 

[habesha]

yes but it still scans the clustered index

 

[ptheriault]

can you post the query and execution plan?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[habesha]

---code


SELECT 'Study Agent' AS StudyProductType,
esp.ESP_PRODUCT_NAME AS StudyIntervention,
esp.ESP_RELATIONSHIP_TYPE AS RelationshipToPrimaryAE,
esp.ESP_EXPECTED_TYPE AS StudyProductExpectedness
FROM phoenix.EAE_STUDY_PRODUCTS esp INNER JOIN phoenix.EAE_STUDY_AGENTS esa
ON esa.ESA_ESP_ID = esp.ESP_ID
WHERE esp.ESP_EVE_ID = 38
AND esp.ESP_RECORD_STATUS_FLAG = 'Active'
AND esa.ESA_RECORD_STATUS_FLAG = 'Active'


---showplan

|--Nested Loops(Inner Join, OUTER REFERENCES[esa].[ESA_ESP_ID]))
|--Clustered Index Scan(OBJECT[DaidsesDev4].[Phoenix].[EAE_STUDY_AGENTS].[ESA_ID_PK] AS [esa]), WHERE[esa].[ESA_RECORD_STATUS_FLAG]='Active'))
|--Clustered Index Seek(OBJECT[DaidsesDev4].[Phoenix].[EAE_STUDY_PRODUCTS].[ESP_ID_PK] AS [esp]), SEEK[esp].[ESP_ID]=[esa].[ESA_ESP_ID]), WHERE[esp].[ESP_EVE_ID]=38 AND [esp].[ESP_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)

 

[ptheriault]

You would have to look at the cost of having this index. If this is not a really popular query then it may not be worth it.

I would create two nonclustered indexs
on EAE_STUDY_PRODUCT I would have this.
ESP_PRODUCT_NAME
ESP_RELATIONSHIP_TYPE
ESP_EXPECTED_TYPE
ESP_EVE_ID = 38
ESP_RECORD_STATUS_FLAG

On EAE_STUDY_AGENTS I would create this.
ESA_ESP_ID
ESA_RECORD_STATUS_FLAG

- Paul
- If at first you don't succeed, find out if the loser gets anything.