Can This Query Be Improved?

The query displayed below currently takes approximately 5-6 seconds to run in SQL Query Analyzer. It returns 685 rows. In my opinion, 5 seconds seems way to long, so I am wondering if there is a way to optimize this query. Does anyone have suggestions on what I could do to improve the performance of this query?

DECLARE @.SearchTerm varchar(200)
SET @.SearchTerm = 'john'

SET NOCOUNT ON

SELECT DISTINCT
SalesLead.SalesLeadID,
SalesLead.Prefix,
SalesLead.FirstName,
SalesLead.LastName,
SalesLead.Email,
SalesLead.Phone,
SalesLead.LastContact,
Schools.SchoolID,
Schools.SchoolName,
Schools.City AS 'SchoolCity'

FROM SalesLead
INNER JOIN jnSalesLeadSchool
ON SalesLead.SalesLeadID = jnSalesLeadSchool.SalesLeadID
INNER JOIN Schools
ON jnSalesLeadSchool.SchoolID = Schools.SchoolID
LEFT OUTER JOIN jnSalesLeadDepartment
ON SalesLead.SalesLeadID = jnSalesLeadDepartment.SalesLeadID
LEFT OUTER JOIN Department
ON jnSalesLeadDepartment.DepartmentID = Department.DepartmentID
LEFT OUTER JOIN jnSalesLeadOpportunity
ON SalesLead.SalesLeadID = jnSalesLeadOpportunity.SalesLeadID
LEFT OUTER JOIN AdoptionOpportunity
ON jnSalesLeadOpportunity.OpportunityID = AdoptionOpportunity.AdoptionOpportunityID
LEFT OUTER JOIN CourseNames
ON AdoptionOpportunity.CourseNameID = CourseNames.CourseNameID
LEFT OUTER JOIN SalesLeadNotes
ON SalesLead.SalesLeadID = SalesLeadNotes.SalesLeadID

WHERE
SalesLead.Active = 1
AND (
SalesLead.FirstName + ' ' + SalesLead.LastName LIKE '%' + @.SearchTerm + '%'
OR SalesLead.Address1 LIKE '%' + @.SearchTerm + '%'
OR SalesLead.City LIKE '%' + @.SearchTerm + '%'
OR SalesLead.Email LIKE '%' + @.SearchTerm + '%'
OR SalesLeadNotes.Note LIKE '%' + @.SearchTerm + '%'
OR Schools.SchoolName + ' - ' + Schools.City LIKE '%' + @.SearchTerm + '%'
OR Department.Name LIKE '%' + @.SearchTerm + '%'
OR CourseNames.CourseName LIKE '%' + @.SearchTerm + '%'
OR AdoptionOpportunity.Term LIKE '%' + @.SearchTerm + '%'
OR AdoptionOpportunity.Chances LIKE '%' + @.SearchTerm + '%'
)

ORDER BY SalesLead.LastName

Thanks in advance!
AaronPerhaps the execution plan has a hint. How much has the distinct? Don't know how expensive it really is. It might be an option to rewrite the whole thing to match the exact search-type so there's no OR-left.

Whats the rowcount without the where-clause?|||Creating an index on Saleslead.Active might help a bit. Enabling full text indexing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_74oj.asp) will help a lot, but at a significant cost in disk space and INSERT/UPDATE performance.

-PatP|||According to the execution plan, the DISTINCT is costing 2%, but I need that to filter out the duplicate information that the OUTER joins cause.

The rowcount without the where clause is 15,617.

According to the execution plan, the two biggest costs are:
38% - SORT(Sorting the Input) - ARGUMENT: ORDER BY:(jnSalesLeadSchool.SalesLeadID ASC)

19% - INDEX SEEK(Scanning a particular range of rows from a non-clusted index) - OBJECT: SalesLeadNotes.IX_SalesLeadNotes.SalesLeadID, SEEK: SalesLeadNotes.SalesLeadID = SalesLead.SalesLeadID|||LIKE '%something'??

That's a scan everytime|||I understand that the wildcard matches are probably the biggest problem area, but is there any way around them besides a full text search?|||I was gonna say...

"Drop back and punt"...

Ah the Giants of yesteryear....

how does this do?

AND (
SalesLead.FirstName + ' ' + SalesLead.LastName
+ SalesLead.Address1
+ SalesLead.City
+ SalesLead.Email
+ SalesLeadNotes.Note
+ Schools.SchoolName + ' - ' + Schools.City
+ Department.Name
+ CourseNames.CourseName
+ AdoptionOpportunity.Term
+ AdoptionOpportunity.Chances
LIKE '%' + @.SearchTerm + '%'
)|||I was gonna say...

"Drop back and punt"...

Ah the Giants of yesteryear....

how does this do?

AND (
SalesLead.FirstName + ' ' + SalesLead.LastName
+ SalesLead.Address1
+ SalesLead.City
+ SalesLead.Email
+ SalesLeadNotes.Note
+ Schools.SchoolName + ' - ' + Schools.City
+ Department.Name
+ CourseNames.CourseName
+ AdoptionOpportunity.Term
+ AdoptionOpportunity.Chances
LIKE '%' + @.SearchTerm + '%'
)|||Brett,
I actually tried exactly that yesterday before going home. Here is the problem: if any of those fields are null (which some are), the entire string will default to NULL - which means rows are not matched. To combat this, I tried using an ISNULL(field, '') on each field while concatenating, but the overhead of the ISNULL cancelled out the gain.|||OK, then punt...

Or simplify the criteria...

or...and this is my last shot before FULLTEXT

DECLARE @.SearchTerm varchar(200)
SET @.SearchTerm = 'john'

SET NOCOUNT ON
SELECT DISTINCT * FROM (
SELECT
x.SalesLeadID,
x.Prefix,
x.FirstName,
x.LastName,
x.Email,
x.Phone,
x.LastContact,
x.SchoolID,
x.SchoolName,
x.City AS 'SchoolCity'

FROM ( SELECT * FROM SalesLead a
INNER JOIN jnSalesLeadSchool b
ON a.SalesLeadID = b.SalesLeadID
INNER JOIN Schools c
ON b.SchoolID = c.SchoolID

WHERE SalesLead.Active = 1
AND ( REPLACE(a.FirstName, @.SearchTerm, '') <> a.FirstName
OR REPLACE(a.LastName, @.SearchTerm, '') <> a.LastName
OR REPLACE(a.Address1, @.SearchTerm, '') <> a.Address1
OR REPLACE(a.City, @.SearchTerm, '') <> a.City
OR REPLACE(a.Email, @.SearchTerm, '') <> a.Email
OR REPLACE(c.SchoolName, @.SearchTerm, '') <> c.SchoolName
OR REPLACE(c.City, @.SearchTerm, '') <> c.City
) AS X
LEFT OUTER JOIN jnSalesLeadDepartment d
ON X.SalesLeadID = d.SalesLeadID
LEFT OUTER JOIN jnSalesLeadOpportunity f
ON X.SalesLeadID = f.SalesLeadID
LEFT OUTER JOIN SalesLeadNotes i
ON X.SalesLeadID = i.SalesLeadID
LEFT OUTER JOIN Department e
ON X.DepartmentID = e.DepartmentID
LEFT OUTER JOIN AdoptionOpportunity g
ON X.OpportunityID = g.AdoptionOpportunityID
LEFT OUTER JOIN CourseNames h
ON g.CourseNameID = h.CourseNameID

WHERE
OR REPLACE(i.Note, @.SearchTerm, '') <> i.Note
OR REPLACE(e.[Name], @.SearchTerm, '') <> e.[Name]
OR REPLACE(h.CourseName, @.SearchTerm, '') <> h.CourseName
OR REPLACE(g.Term, @.SearchTerm, '') <> g.Term
OR REPLACE(g.Chances, @.SearchTerm, '') <> g.Chances
) AS XXX
ORDER BY LastName|||Thanks Brett, but that query takes exactly the same amount of time. I like your idea of using Replace rather than the wildcard comparison, that was clever.

It looks like fulltext is my last resort - are there any rules agains using fulltext searching on columns that contain such a small amount of text (usually less than 100 characters)?|||ow btw: why the order by on a distinct ?|||What you want is really a full-text search. We are beating this mouse bloody when you really want an ox. Byte the bullet and move on!

-PatP

Can sleeping connections hold locks

To clarify a little more - I am looking at the results of sp_who2 and it is
the connections that are coming from query analyzer I am curious about.
The user in this case had executed and completed their query and had not
specified then transaction isolation level, yet there was an update from
another connection that would just not complete - when I killed the
connection of the user running the query the update completed. It could
just be a coincidence and the sleeping connection may have had nothing to do
with it but it just got me wondering if it is normal SQL behavior to hold a
lock if a query (where the isolation level is not specified) has completed
running.
thanks
Meenal
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>
|||Better yet, that was too explicit. What I typically find is that the client
API makes calls that the end-user is unaware of that can block resrouces.
Most notibably the use of COM objects on web servers.
By default COM uses the SERIALIZABLE transaction isolation level. Then, if
they use IMPLICIT_TRANSACTIONS, you can be in a world of hurt. Add to that,
connection pooling, and you can bring a web site to its knees in just
minutes of light activity. All the while, the DBMS sit peacefully silent
AWAITING COMMAND.
Try this simulation, which occurs, implicitly in many scenarios.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
SELECT *
FROM SomeBigTable
GO
--Leave the connection open.
In another connection run this:
SELECT *
FROM master.dbo.sysprocesses WITH(NOLOCK)
SELECT *
FROM master.dbo.syslockinfo WITH(NOLOCK)
You could narrow down both of these queries by specifying the particular
spid you ran the first under; regardless, you sould see that that SELECT
statement is holding all kinds of locks. And, if this is a busy system,
will be blocking quite a few other users.
I would recommend doing this on test.
Sincerely,
Anthony Thomas
"Barry McAuslin" <barry@.nospam.com> wrote in message
news:%236Q%233DAOFHA.2252@.TK2MSFTNGP15.phx.gbl...
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>

Can sleeping connections hold locks

To clarify a little more - I am looking at the results of sp_who2 and it is
the connections that are coming from query analyzer I am curious about.
The user in this case had executed and completed their query and had not
specified then transaction isolation level, yet there was an update from
another connection that would just not complete - when I killed the
connection of the user running the query the update completed. It could
just be a coincidence and the sleeping connection may have had nothing to do
with it but it just got me wondering if it is normal SQL behavior to hold a
lock if a query (where the isolation level is not specified) has completed
running.
thanks
MeenalMeenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
--
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>|||Better yet, that was too explicit. What I typically find is that the client
API makes calls that the end-user is unaware of that can block resrouces.
Most notibably the use of COM objects on web servers.
By default COM uses the SERIALIZABLE transaction isolation level. Then, if
they use IMPLICIT_TRANSACTIONS, you can be in a world of hurt. Add to that,
connection pooling, and you can bring a web site to its knees in just
minutes of light activity. All the while, the DBMS sit peacefully silent
AWAITING COMMAND.
Try this simulation, which occurs, implicitly in many scenarios.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
SELECT *
FROM SomeBigTable
GO
--Leave the connection open.
In another connection run this:
SELECT *
FROM master.dbo.sysprocesses WITH(NOLOCK)
SELECT *
FROM master.dbo.syslockinfo WITH(NOLOCK)
You could narrow down both of these queries by specifying the particular
spid you ran the first under; regardless, you sould see that that SELECT
statement is holding all kinds of locks. And, if this is a busy system,
will be blocking quite a few other users.
I would recommend doing this on test.
Sincerely,
Anthony Thomas
--
"Barry McAuslin" <barry@.nospam.com> wrote in message
news:%236Q%233DAOFHA.2252@.TK2MSFTNGP15.phx.gbl...
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
--
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>

Can sleeping connections hold locks

To clarify a little more - I am looking at the results of sp_who2 and it is
the connections that are coming from query analyzer I am curious about.
The user in this case had executed and completed their query and had not
specified then transaction isolation level, yet there was an update from
another connection that would just not complete - when I killed the
connection of the user running the query the update completed. It could
just be a coincidence and the sleeping connection may have had nothing to do
with it but it just got me wondering if it is normal SQL behavior to hold a
lock if a query (where the isolation level is not specified) has completed
running.
thanks
MeenalMeenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>|||Better yet, that was too explicit. What I typically find is that the client
API makes calls that the end-user is unaware of that can block resrouces.
Most notibably the use of COM objects on web servers.
By default COM uses the SERIALIZABLE transaction isolation level. Then, if
they use IMPLICIT_TRANSACTIONS, you can be in a world of hurt. Add to that,
connection pooling, and you can bring a web site to its knees in just
minutes of light activity. All the while, the DBMS sit peacefully silent
AWAITING COMMAND.
Try this simulation, which occurs, implicitly in many scenarios.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
SELECT *
FROM SomeBigTable
GO
--Leave the connection open.
In another connection run this:
SELECT *
FROM master.dbo.sysprocesses WITH(NOLOCK)
SELECT *
FROM master.dbo.syslockinfo WITH(NOLOCK)
You could narrow down both of these queries by specifying the particular
spid you ran the first under; regardless, you sould see that that SELECT
statement is holding all kinds of locks. And, if this is a busy system,
will be blocking quite a few other users.
I would recommend doing this on test.
Sincerely,
Anthony Thomas
"Barry McAuslin" <barry@.nospam.com> wrote in message
news:%236Q%233DAOFHA.2252@.TK2MSFTNGP15.phx.gbl...
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>