Kevin,

If you want to optimize this, conside the following.

SELECT RowId, ContractNo, SystemId, ItemNumber, Description, Price
	FROM AlertData
	WHERE ContractNo LIKE @sSearchVal
UNION
SELECT RowId, ContractNo, SystemId, ItemNumber, Description, Price
	FROM AlertData
	WHERE ItemNumber LIKE  @sSearchVal 
UNION 
SELECT RowId, ContractNo, SystemId, ItemNumber, Description, Price
	FROM AlertData
	WHERE Description LIKE  @sSearchVal

The reason this might be faster is that SQL server might optimize and run each SQL select independently. SQL server then can use an index scan to search for the value which is slower than index seeks, but far faster than a table scan (because of the OR statements) your example probably is using.

In the case where the @sSearchval does not begin with a wildcard, this should be far fast. If storing this in a stored procedure, also make sure you call it with the WITH RECOMPILE option, so that it selects the best execution plan everytime you call it.

Give it a try.

Walter,



>I have a table called AlertData with 500,000 records in it. This query is taking 11 seconds, which is too long for the client.
>
>

>DECLARE @sSearchVal NVARCHAR(50)
>SET @sSearchVal = '%sun%'
>
>SELECT RowId, ContractNo, SystemId, ItemNumber, Description, Price
>	FROM AlertData
>	WHERE ContractNo LIKE @sSearchVal OR
>		   ItemNumber LIKE  @sSearchVal OR
>		   Description LIKE  @sSearchVal
>
>

>
>There are indexes on all 3 fields.
>
>Thanks
>
>Kevin