performace problem

[habesha]

I have this query

The table PHOENIX.EXPEDITED_ADVERSE_EVENTS has clustsed index defined on EAE_ID and nonclustsred index defined on EAE_NUMBER

SELECT EAE_ID FROM PHOENIX.EXPEDITED_ADVERSE_EVENTS
WHERE CAST(EAE_NUMBER AS VARCHAR(15)) LIKE ISNULL(NULL, CAST(EAE_NUMBER AS VARCHAR(15)) )

when I run it it is index scaning which costs much time, what should I do

here is it showplan

|--Index Scan(OBJECT[DaidsesDev4].[Phoenix].[EXPEDITED_ADVERSE_EVENTS].[EAE_NUMBER_UK]), WHERElike(Convert([EXPEDITED_ADVERSE_EVENTS].[EAE_NUMBER]), Convert([EXPEDITED_ADVERSE_EVENTS].[EAE_NUMBER]), NULL)))

Thanks

 

[gmmastros]

First of all, IsNull will return the second parameter if the first parameter is null. since you are hardcoding NULL for the first parameter, the second parameter will always be used, so your query can be simplified to...

SELECT EAE_ID FROM PHOENIX.EXPEDITED_ADVERSE_EVENTS
WHERE CAST(EAE_NUMBER AS VARCHAR(15)) LIKE CAST(EAE_NUMBER AS VARCHAR(15))

Next... Like without % symbol will always evaluate the same as =, so...

SELECT EAE_ID FROM PHOENIX.EXPEDITED_ADVERSE_EVENTS
WHERE CAST(EAE_NUMBER AS VARCHAR(15)) = CAST(EAE_NUMBER AS VARCHAR(15))

Next... since both sides of the = are the same, the only records that will return are those where the field is not null, so you can use that instead.

SELECT EAE_ID FROM PHOENIX.EXPEDITED_ADVERSE_EVENTS
WHERE EAE_NUMBER is Not NULL

There you have it. I'll get this query runs a lot faster.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[habesha]

Thank you for ur swift response

Actually the query is like this.There is an incoming parameter @p_In_EAE_NUMBER_Vch

SELECT EAE_ID FROM EXPEDITED_ADVERSE_EVENTS
WHERE CAST(EAE_NUMBER AS VARCHAR(15)) LIKE ISNULL(@p_In_EAE_NUMBER_Vch, CAST(EAE_NUMBER AS VARCHAR(15)) )

any suggestion

 

[gmmastros]

SELECT EAE_ID 
FROM   EXPEDITED_ADVERSE_EVENTS 
WHERE  (@p_In_EAE_NUMBER_Vch Is NULL
       Or EAE_NUMBER = @p_In_EAE_NUMBER_Vch)

Essentially, if @p_In_EAE_NUMBER_Vch is null, all records will be retured. If it's not null, then only records that match will be returned.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I encourage you to read through this page.

http://www.sql-server-performance.com/transact_sql.asp

Especially the section that starts with....

avoid WHERE clauses that are non-sargable

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[habesha]

This still goes to index scanning whta should I do to improve it

Thanks