I have a customer search query that runs 2.5 seconds longer when the WHERE clause contains a condition on one of the joined tables. The query is executed from a .NET app.
This query will run in .5 seconds:
SELECT DISTINCT C.customerID,C.firstName,C.lastName,C.DOB,C.SSN,C.middleName,C.email,C.phone,C.address,C.city,C.state,C.postal,C.country,C.companyID,C.scanID,C.mobile,
C.mobileProviderID,C.status,C.internalCustomerID,C.extData, (C.firstName + ' ' + C.lastName) as customerName, ISNULL(CO.companyName, '') as companyName,
ISNULL(A.accountNumber, '') as accountNumber
FROM tblCustomer AS C
LEFT JOIN tblCustomerAccount AS CA ON CA.customerID = C.customerID
LEFT JOIN tblAccount AS A ON A.accountID = CA.accountID
LEFT JOIN tblCompany AS CO ON CO.companyID=C.companyID
WHERE (C.firstname LIKE 'test%'
OR C.lastName LIKE 'test%'
OR C.email = @searchString) AND (C.status=@status OR @status=-1)
Adding this condition to the WHERE clause makes the query run in 3 seconds
OR A.accountNumber = 'test'
All tables have a primary key index.
Things I’ve tried:
- adding a non-clustered index to tblCustomer with “firstName,lastName,phone,mobile,email,status”.
- created a non-clustered index on tblAccount with “accountNumber”.
- rebuild indexes
Table stats
- tblCustomer contains 1,022,799 rows
- tblAccount contains 2091 rows
- tblCustomerAccount contains 2091 rows
- tblCompany contains 0 rows
Any optimization tips or an explanation on why it runs slower are appreciated. I’ve tried most of the ones that I found on a Google search.
1