It looks like this query is an example of a problematic use of OR according to SQLCAT:
However, when the conditions on each side of the OR operator are evaluating different tables, problems can arise:
WHERE a.col1 = @val1 OR b.col2 = @val2
The pattern represented by this query predicate is problematic. Breaking down the query into two steps produces a significantly “cheaper” plan. Note that in this pattern, there are two tables involved; on each side of the OR operator is a condition that applies to different tables.
In the query plan with the additional tables filtered out, SQL Server is able to filter out irrelevant rows quickly. Also the filter condition can be applied directly to the table.
In the query plan with the
OR clause, SQL Server filters out unnecessary rows only at the very end of the plan, after doing millions of unnecessary index seeks. This is the query that you reported as being slow in your question.
In the query plan with the query that I suggested (
UNION instead of
OR), SQL Server is more able to directly apply the filters and less overall work is done. There is not a filtering step at the end that eliminates hundreds of thousands of rows.
Note that this approach may not work as well as it usually does because you're querying a view and the logic behind that view may be expensive. It is also true that rewriting the
OR as a
UNION will result in duplicated code. The best way around this is to use a derived table. Unfortunately, this may not be a good option for you depending on the possible filter conditions that end users can add. If you can use a derived table, one rewrite is as follows:
SELECT DISTINCT TOP 250
,Product.Title AS 'Model'
,Brand.Title AS 'Brand'
,Customer.Title AS 'Customer'
,ISNULL(AssetIdentifier_TagNumber.Value, '') AS 'TagNumber'
,CustomerLocation.Title AS 'CustomerLocation'
,ISNULL(TagLocation.TagLocationNumber, '') AS 'TagLocationTitle'
FROM dbo.vwAsset AS Asset
WHERE Asset.Title LIKE '%E29%'
FROM dbo.vwAsset AS Asset
INNER JOIN dbo.tblAssetIdentifierSnapshot AS AssetIdentifierSnapshot ON AssetIdentifierSnapshot.AssetSnapshotId = Asset.AssetSnapshotId
INNER JOIN dbo.tblAssetIdentifierVersion AS AssetIdentifierVersion ON AssetIdentifierVersion.AssetIdentifierVersionId = AssetIdentifierSnapshot.AssetIdentifierVersionId
WHERE Asset.Title NOT LIKE '%E29%' AND AssetIdentifierVersion.Value LIKE '%E29%'
) AS Asset
INNER JOIN dbo.tblProduct AS Product ON Product.ProductId = Asset.ProductId
INNER JOIN dbo.tblBrand AS Brand ON Brand.BrandId = Product.BrandId
INNER JOIN dbo.tblCustomer AS Customer ON Customer.CustomerId = Asset.CustomerId
INNER JOIN dbo.tblCustomerLocation AS CustomerLocation ON CustomerLocation.CustomerLocationId = Asset.CustomerLocationId
INNER JOIN dbo.vwUserServiceLocation AS UserServiceLocation ON UserServiceLocation.ServiceLocationId = CustomerLocation.ServiceLocationId
LEFT JOIN dbo.vwAssetIdentifier AS AssetIdentifier_TagNumber ON AssetIdentifier_TagNumber.AssetSnapshotId = Asset.AssetSnapshotId AND AssetIdentifier_TagNumber.Title = 'Tag Number'
LEFT JOIN dbo.tblTagLocation AS TagLocation ON TagLocation.TagLocationId = Asset.TagLocationId
INNER JOIN dbo.tblAssetManufacturerTag AS AssetManufacturerTag ON AssetManufacturerTag.AssetId = Asset.AssetId
LEFT JOIN dbo.tblStatementOfWork AS SOW ON SOW.AssetId = Asset.AssetId
LEFT JOIN dbo.tblWorkOrder AS WorkOrder ON WorkOrder.WorkOrderId = SOW.WorkOrderId
LEFT JOIN dbo.tblProject AS Project ON Project.ProjectId = WorkOrder.ProjectId
1 = 1
AND UserServiceLocation.OwnerId = '1A07E201-D832-465D-A1B1-10500A20E16A';
If you can't use a derived table and need to duplicate the full query logic that's okay. To get the TOP behavior that you're looking for you mean need to define the whole query as a CTE and do the TOP filtering as a final step.
As a final note, carefully consider if
DISTINCT TOP 250 without an
ORDER BY clause is what you want. With that query you will just get an arbitrary 250 rows. If you are returning data to an end user there may be a more meaningful way to order or limit the results.