CODEWITHSUNDEEP
sqlsql-server
Hristian Yordanov
Hristian Yordanov

Reputation: 668

Using NOT LIKE for multiple mutiple columns

I want to search for particular value in multiple columns. Is there any way to optimize this query?

SELECT TOP (200) Events.EventDate, AVDCAV.CAVCH1, AVDCAV.CAVCH2, AVDCAV.CAVCH3, AVDCAV.CAVCH4, AVDCAV.CAVCH5, AVDCAV.CAVCH6, AVDCAV.CAVCH7, AVDCAV.CAVCH8, AVDCAV.CAVCH9
FROM AVDCAV
   INNER JOIN Events ON AVDCAV.IDEvent=Events.EventID
WHERE CAVCH9 NOT LIKE '0.0000000000' OR CAVCH8 NOT LIKE '0.0000000000' OR CAVCH7 NOT LIKE '0.0000000000' OR CAVCH1 NOT LIKE '0.0000000000'
ORDER BY AVDCAV.IDEvent DESC

I'm getting the data from SQL Express 2008 R2.

Upvotes: 0

Views: 66

Answers (2)

Mazhar
Mazhar

Reputation: 3837

Without knowing table sizes, current indices etc, you could try adding this index

CREATE NONCLUSTERED INDEX IX_AVDCAV ON AVDCAV ( IDEvent ) 
INCLUDE ( CAVCH1, CAVCH2, CAVCH3, CAVCH4, CAVCH5, CAVCH6, CAVCH7, CAVCH8, CAVCH9 )

to see if performance improves

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Because you aren't using any wildcard you don't need LIKE.

WHERE '0.0000000000' NOT IN  (CAVCH1, CAVCH7, CAVCH8, CAVCH9)

Btw the performance is the same, but easier to read.

Upvotes: 3

Related Questions