Using 'OR' drastically increases execution time of query?

• Eugene Niemand

  Valued Member

  Points: 68

  More actions

  January 23, 2008 at 4:16 am

  #770821

  Hi, something you can also try that I have found that works extremly well even though in theroy and logically it sounds impossible is to run 2 exact copies of your query just with different where conditions and use a union between the two, also I have to add you have to have the right indexes. something like this:

  SELECT * FROM DBO.USERS WHERE USER_NAME = 'JOHN'

  UNION

  SELECT * FROM DBO.USERS WHERE USER_NAME = 'ZACK'

  instead of

  SELECT * FROM DBO.USERS WHERE USER_NAME = 'JOHN' OR USER_NAME = 'ZACK'

  or instead of

  SELECT * FROM DBO.USERS WHERE USER_NAME IN ('JOHN', 'ZACK')

  Like I said this does not seem like the best way but look at the total execution cost its way cheaper than using an OR.

• Keith DuAime

  Ten Centuries

  Points: 1129

  More actions

  January 23, 2008 at 9:42 am

  #770967

  I had a similar issue and included a lot of table joins. I found the or condition was causing scans not seeks. I used the following to get the seeks that I wanted. Of course all the indexes have to be there and kept up but this may alleviate some of your troubles. Make sure the variables are NULL and not = ''

  SELECT

  EventId,

  EventFriendlyDescription,

  BusinessObjectTypeFullName,

  BusinessObjectInstanceId,

  ISNULL(Detail, '') AS Detail,

  Username,

  [TimeStamp]

  FROM

  Auditing.Audit

  WHERE

  Username = COALESCE(@Username, Username) AND

  BusinessObjectTypeFullName = COALESCE(@BusinessObjectTypeFullName, BusinessObjectTypeFullName) AND

  BusinessObjectInstanceId = COALESCE(@BusinessObjectInstanceId, BusinessObjectInstanceId) AND

  [Timestamp] > COALESCE(@AuditDate, ( SELECT MIN([TimeStamp]) FROM Auditing.Audit ) )