index optimization question

[habesha]

what is a substitute for covering index in sql server 2000

There are many columns which are causing clustsred index scan that degrades my query performance

here is part of the showplan in my query

|--Clustered Index Scan(OBJECT[DaidsesDev4].[Phoenix].[EAE_DIAGNOSTICS].[EDI_ID_PK]), WHERE((((((((([@p_In_EDI_ID_Num]=NULL OR [EAE_DIAGNOSTICS].[EDI_ID]=[@p_In_EDI_ID_Num]) AND ([@p_In_EDI_EVE_ID_Num]=NULL OR [EAE_DIAGNOSTICS].[EDI_EVE_ID]=[@p_In_EDI_EVE_ID_Num])) AND ([@p_In_EDI_LOI_ID_Num]=NULL OR [EAE_DIAGNOSTICS].[EDI_LOI_ID]=[@p_In_EDI_LOI_ID_Num])) AND ([@p_In_EDI_DIAGNOSTIC_TEST_NAME_Vch]=NULL OR [EAE_DIAGNOSTICS].[EDI_DIAGNOSTIC_TEST_NAME]=[@p_In_EDI_DIAGNOSTIC_TEST_NAME_Vch])) AND ([@p_In_EDI_TEST_SPECIFY_OTHER_TEXT_Vch]=NULL OR [EAE_DIAGNOSTICS].[EDI_TEST_SPECIFY_OTHER_TEXT]=[@p_In_EDI_TEST_SPECIFY_OTHER_TEXT_Vch])) AND ([@p_In_EDI_RESULT_PENDING_FLAG_Vch]=NULL OR [EAE_DIAGNOSTICS].[EDI_RESULT_PENDING_FLAG]=[@p_In_EDI_RESULT_PENDING_FLAG_Vch])) AND ([@p_In_EDI_BODY_AREA_TYPE_Vch]=NULL OR [EAE_DIAGNOSTICS].[EDI_BODY_AREA_TYPE]=[@p_In_EDI_BODY_AREA_TYPE_Vch])) AND ([@p_In_EDI_BODY_AREA_SPECIFY_OTHER_TEXT_Vch]=NULL OR [EAE_DIAGNOSTICS].[EDI_BODY_AREA_SPECIFY_OTHER_TEXT]=[@p_In_EDI_BODY_AREA_SPECIFY_OTHER_TEXT_Vch])) AND ([@p_In_EDI_TEST_DATE_Dtm]=NULL OR [EAE_DIAGNOSTICS].[EDI_TEST_DATE]=[@p_In_EDI_TEST_DATE_Dtm])) AND [EAE_DIAGNOSTICS].[EDI_RECORD_STATUS_FLAG]=isnull([@p_In_EDI_RECORD_STATUS_FLAG_Vch], 'Active')))


Thanks

 

[habesha]

to make the que easy
I have a table over 14 columns
a clustsred index is defined on the PK.

I am wrting a query to select 10 of the xolumns from the table, which leads to a clustsred index scan( that makes my query very slow)
I want to optimize the query by adding some indexes.
do I have to add 10 nonclustsred index one on each column or what should I do to optimize it. My database is sql server 2000

Thanks

 

[thekl0wn]

What's in your WHERE clause? That's what needs to be in your non-clustered index.

 

[AlexCuse]

If you want to make this easy on yourself, just select the query in query analyzer and hit ctrl+I

This will bring up a wizard, all you will need to do is follow those steps and it will make index tuning recommendations. (make sure you check all tables involved in the query).

GOod Luck,

Alex

Ignorance of certain subjects is a great part of wisdom