Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
neilgilbert1234
Starting Member
9 Posts |
 Posted - 2009-09-02 : 05:32:15
|
| Hi there,We have a sql query which is part of a crm system, the user needs to enter multiple values to search. the system will then built the sql string, resulting in the sql below.When the query is ran with just a single 'like' clause the results are instant, when two are entered (again as below) the results take 4+ mins to return.SELECT count(*) as fcount FROM (SELECT DISTINCT (COMP.comp_companyid) AS key1, (COMP.comp_companyid) AS gcsv_CompanyId, (COMP.comp_name) AS gcsv_CustomerName, (COMP.comp_firstname) AS gcsv_FirstName, (COMP.comp_lastname) AS gcsv_LastName, (COMP.comp_AccountStatus) AS gcsv_AccountStatus, (ADDR.addr_address1) AS gcsv_Address1, (ADDR.addr_postcode) AS gcsv_PostCode, (GECT.gect_ForeignIdType) AS gcsv_accounttype FROM Company COMP WITH (NOLOCK) INNER JOIN [Address] ADDR ON ADDR.addr_addressid = COMP.comp_primaryaddressid LEFT JOIN vwGECustomerRecords GECT ON GECT.gect_CRMID = COMP.comp_companyid WHERE COMP.comp_AccountStatus = 0 AND COMP.comp_firstname LIKE 'zoe%' AND COMP.comp_lastname LIKE 'smith%' ) AS Results |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-02 : 06:14:13
|
| like wont make use of index and can make search slower. are you looking for wildcard search in both cases? |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-09-02 : 11:40:14
|
| visakh16,I thought that since the leading characters of the LIKE string were not wildcards that the index would still be used.Neil,Can you show us the execution plan for the two queries?=======================================Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727) |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-09-02 : 12:36:09
|
| Like will make use of an index, provided one exists. Do you have an index on both the firstname and lastname columns? |
|
|
|
|
|
|
|
|
slow query when ran with two 'like' clauses