Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2004
    Posts
    19

    Unanswered: 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?

    Code:
    
    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!
    Aaron

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    Perhaps 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?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Creating an index on Saleslead.Active might help a bit. Enabling full text indexing will help a lot, but at a significant cost in disk space and INSERT/UPDATE performance.

    -PatP

  4. #4
    Join Date
    Sep 2004
    Posts
    19
    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

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    LIKE '%something'????

    That's a scan everytime
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2004
    Posts
    19
    I understand that the wildcard matches are probably the biggest problem area, but is there any way around them besides a full text search?

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I was gonna say...

    "Drop back and punt"...

    Ah the Giants of yesteryear.....

    how does this do?

    Code:
    	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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I was gonna say...

    "Drop back and punt"...

    Ah the Giants of yesteryear.....

    how does this do?

    Code:
    	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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Sep 2004
    Posts
    19
    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.
    Last edited by forgueam; 09-30-04 at 13:40. Reason: spelling

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, then punt...

    Or simplify the criteria...

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

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Sep 2004
    Posts
    19
    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)?

  12. #12
    Join Date
    Feb 2004
    Posts
    492
    ow btw: why the order by on a distinct ?

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •