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 the "money" datatype be constrained to two decimal places?

I have a column that is a "money" datatype, which returns a four decimal
place value. It is a bit of a hassle to format this for display. Is there
anyway to default this to two decimal places?No, the datatype cannot be changed.
Look at CAST and CONVERT and you will see that there is a style that you can
use with CONVERT to return the value with 2 decimal places. (Or you could
stop using money and use DECIMAL if that meets your need.)
Russell Fields
"Top Gun" <nfr@.nospam.com> wrote in message
news:OyqMHnk8DHA.2540@.TK2MSFTNGP11.phx.gbl...
> I have a column that is a "money" datatype, which returns a four decimal
> place value. It is a bit of a hassle to format this for display. Is there
> anyway to default this to two decimal places?
>|||No, change to decimal as Russell suggests... But the benefit of using money
is that the front-end application should be able to recognize and format the
data to 2 decimals using localization... Many folks need the 4 decimals for
increased accuracy, then round for display...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Top Gun" <nfr@.nospam.com> wrote in message
news:OyqMHnk8DHA.2540@.TK2MSFTNGP11.phx.gbl...
> I have a column that is a "money" datatype, which returns a four decimal
> place value. It is a bit of a hassle to format this for display. Is there
> anyway to default this to two decimal places?
>

Can the "money" datatype be constrained to two decimal places?

I have a column that is a "money" datatype, which returns a four decimal
place value. It is a bit of a hassle to format this for display. Is there
anyway to default this to two decimal places?No, the datatype cannot be changed.
Look at CAST and CONVERT and you will see that there is a style that you can
use with CONVERT to return the value with 2 decimal places. (Or you could
stop using money and use DECIMAL if that meets your need.)
Russell Fields
"Top Gun" <nfr@.nospam.com> wrote in message
news:OyqMHnk8DHA.2540@.TK2MSFTNGP11.phx.gbl...
> I have a column that is a "money" datatype, which returns a four decimal
> place value. It is a bit of a hassle to format this for display. Is there
> anyway to default this to two decimal places?
>|||No, change to decimal as Russell suggests... But the benefit of using money
is that the front-end application should be able to recognize and format the
data to 2 decimals using localization... Many folks need the 4 decimals for
increased accuracy, then round for display...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Top Gun" <nfr@.nospam.com> wrote in message
news:OyqMHnk8DHA.2540@.TK2MSFTNGP11.phx.gbl...
> I have a column that is a "money" datatype, which returns a four decimal
> place value. It is a bit of a hassle to format this for display. Is there
> anyway to default this to two decimal places?
>

Can start new database from Web Matrix

I am running Web Matrix version 0.5, .Net version 1.1 on a computer with XP Pro.

Web Matrix works fine and returns forms properly.

I installed MSDE from the website (SQL2KDeskSP3a.exe) Ibelieve that SQL Server IS running, because I see the tower icon with agreen arrow; when I double-click that I get a message that it isrunning SQL Server.

When I click the "data" tab in Web Matrix I get the blankworkspace. Then I click the New Connection icon at the top left,which opens up a dialog box. I change "Windows Authentication" to"SQL Authentication." That opens up the Username/Passwordprompt. I am entering "sa" for the username (I AM NOT SURE IFTHAT IS CORRECT) and "**secret**" for the password (that's what Ientered in the command prompt when I setup the MSDE). Then Iclick "Create a New Database." I am asked to enter a name.

After a pause, I get an error message: "Unable to connect tothe database server. SQL Server does not exist or access denied.Connection Open (Connect ( )). OK

Do you have any ideas?

I installed the newer (version 0.6) Matrix and now I can find the database.

Can replacing nic in server cause null fields?

Our IBM motherboard died and had to be replaced today.
Now our imaging software returns null fields in some data
fields. We run SQL 2000 on NT 4.0 server on this box.
Could somehow the replacement of the motherboard that uses
a different mac burned onto the integrated NIC controller
have caused this?
aspirin,
Unlikely unless you are using WMI to query hardware.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
aspirin wrote:
> Our IBM motherboard died and had to be replaced today.
> Now our imaging software returns null fields in some data
> fields. We run SQL 2000 on NT 4.0 server on this box.
> Could somehow the replacement of the motherboard that uses
> a different mac burned onto the integrated NIC controller
> have caused this?

Can replacing nic in server cause null fields?

Our IBM motherboard died and had to be replaced today.
Now our imaging software returns null fields in some data
fields. We run SQL 2000 on NT 4.0 server on this box.
Could somehow the replacement of the motherboard that uses
a different mac burned onto the integrated NIC controller
have caused this?aspirin,
Unlikely unless you are using WMI to query hardware.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
aspirin wrote:
> Our IBM motherboard died and had to be replaced today.
> Now our imaging software returns null fields in some data
> fields. We run SQL 2000 on NT 4.0 server on this box.
> Could somehow the replacement of the motherboard that uses
> a different mac burned onto the integrated NIC controller
> have caused this?

Can replacing nic in server cause null fields?

Our IBM motherboard died and had to be replaced today.
Now our imaging software returns null fields in some data
fields. We run SQL 2000 on NT 4.0 server on this box.
Could somehow the replacement of the motherboard that uses
a different mac burned onto the integrated NIC controller
have caused this?aspirin,
Unlikely unless you are using WMI to query hardware.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
aspirin wrote:
> Our IBM motherboard died and had to be replaced today.
> Now our imaging software returns null fields in some data
> fields. We run SQL 2000 on NT 4.0 server on this box.
> Could somehow the replacement of the motherboard that uses
> a different mac burned onto the integrated NIC controller
> have caused this?