Slow running Sql Server query

Question

I have the following query:

DECLARE @CompanyId bigint
DECLARE @SearchText nvarchar(4000)
SET @CompanyId=160
SET @SearchText='%02863%'
SELECT j.Id FROM Job (NOLOCK) j WHERE (j.Deleted = 0 AND j.CompanyId = @CompanyId) 
    AND (j.Name LIKE @SearchText OR j.DisplayId LIKE @SearchText OR j.ClaimNumber LIKE @SearchText) 
UNION SELECT j.Id FROM Job (NOLOCK) j INNER JOIN Address (NOLOCK) a ON a.Id = j.AddressId 
    WHERE j.Deleted = 0 AND j.CompanyId = @CompanyId 
    AND a.SearchAddress LIKE @SearchText 
UNION SELECT j.Id FROM Job (NOLOCK) j INNER JOIN Contact (NOLOCK) c ON c.Id = j.CustomerId 
    WHERE j.Deleted = 0 AND j.CompanyId = @CompanyId 
    AND c.SearchName LIKE @SearchText

which is taking sometimes upwards of 10 seconds to run.

Our DB is pretty small, and I don't think it should be taking this long, and I'm wondering if I've formulated this query correctly or if I should change it?

From what I've read, I don't believe that implementing full text search would help much since these are direct comparisons, right?

Also, given this query, I'm hoping that it will only search the addresses and contacts that belong to the jobs where companyId=@companyId and deleted=0? Or will it search all addresses and contacts in the entire DB and then filter based on job criteria?

Jenn · Accepted Answer · 2014-06-16 21:09:05Z

1

The other thing you may want to consider is combining all of the selects into one statement. What is happening right now is you are doing three full index scans, then filtering out any duplicates (with the union). In this case it may be faster to go through the table once and use 'or's in your query.

To answer your last question, right now with the full index scan, Sql server works more on a row-by-row basis. So, it will look at the first data point. If j.deleted = 0, then sql will continue on to evaluate the next condition that you have included. So, it looks to see if the companyId = @companyId. If so, it continues on to check the next condition.

Changing your query to do seeks allows sql to hone in on what you are looking for faster/easier.

DECLARE @CompanyId bigint
DECLARE @SearchText nvarchar(4000)

SET @CompanyId=160
SET @SearchText='%02863%'

SELECT j.Id
FROM Job (NOLOCK) j
WHERE j.Deleted = 0 
  AND j.CompanyId = @CompanyId 
  AND ( j.Name LIKE @SearchText
    OR  j.DisplayId LIKE @SearchText
    OR  j.ClaimNumber LIKE @SearchText
    OR (exists  select 1
                From address (nolock a)
                Where a.id = j.addressid
                and     a.SearchAddress like @SearchText)
    or (exists select 1
                From contact (nolock) c
                Where c.id=j.customerid
                and     c.SearchName like @SearchText)
    )

answered Jun 16, 2014 at 21:09

Jenn

7951 gold badge4 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scottie Over a year ago

Changing to this query makes it almost instantaneous!! Wow, thank you so much!!

Mark Wojciechowicz · Accepted Answer · 2014-06-16 21:00:07Z

0

@SearchText='%02863%'

Will cause a full index scan because it is searching for this string any where in the index. If the search string were something like '02863%', it could use an index seek and be more efficient because it has something to start with.

It would be most helpful if you could post an execution plan.

answered Jun 16, 2014 at 21:00

Mark Wojciechowicz

4,4871 gold badge20 silver badges25 bronze badges

Collectives™ on Stack Overflow

Slow running Sql Server query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related