Performance help

[Crystalguru]

MS SQL Server 2000

I need help optimizing this join.

INNER JOIN CV3Order
           on  CV3BasicObservation.OrderGUID = CV3Order.GUID
		And CV3BasicObservation.ClientGUID = CV3Order.ClientGUID 
		And CV3BasicObservation.ChartGUID = CV3Order.ChartGUID
		And CV3Order.GUID not in (SELECT BO.OrderGUID
						FROM CV3BasicObservation BO
						WHERE ltrim(BO.ItemName) like ('HIV%')
						OR ltrim(BO.ItemName) like ('Toxicology%'))

When I look at the execution plan it is doing a bookmark lookup, filter and index scan. I know it is not using any indexes. The indexes on the CV3BasicObservation are ClientGUID, ChartGUID, OrderGUID. Indexes on CV3Order are ClientGUID, ChartGUID and OrderGUID.

Is there anyway to do the following to help it?

DECLARE @ResultName varchar(40)
SET @ResultName like 'HIV%' 
Declare @ResultName_1 varchar(40)
SET @ResultName_1  like 'Toxicology%'

DECLARE @ResultGUID numeric(16,0)
SELECT @ResultGUID = GUID
  FROM CV3BasicObservation
 WHERE ItemName = @ResultName

DECLARE @ResultGUID_1 numeric(16,0)
SELECT @ResultGUID_1 = GUID
  FROM CV3BasicObservation
 WHERE ItemName = @ResultName_1

I get an error with this, at the Like statement. I am assuming I cannot use the like statement. Unfortunately, to create a listing of test starting with HIV or Toxicology would be lengthy and dynamic.

Any ideas?

 

[SQLDenis]

ltrim(BO.ItemName) like ('HIV%') will cause a scan instead of a seek, get rid of the ltrim in fact never do any formatting on the left side of the operator

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/