A query with "Like Clause" To run faster

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

A query with "Like Clause" To run faster

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-11-16 : 10:19:39

Hi All,

I have a table & a view
I need to find the count of the records of the table, which has records matching (Like Clause) with a field in the view.
What I have is the following and it takes a long time to execute.

SELECT (SELECT COUNT(ClientID)
        FROM   Clients
        WHERE  ClientName LIKE '%' + NameForSearch + '%'
                OR Address LIKE AddressForSearch + '%')
FROM   ImportedCompanyData_View

-- NameForSearch & AddressForSearch are fields of the view ImportedCompanyData_View

Please help me to run that efficiently.

Thanks

Srinika

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-16 : 10:38:57

you desired results are not very clear, so this may not be what you are after. without knowing more about the data or indexation, here is a best guess for an alternative...

SELECT
COUNT(*) AS TotalMatchedClients
FROM
ImportedCompanyData_View
JOIN
Clients
ON Clients.ClientName LIKE '%' + ImportedCompanyData_View.NameForSearch + '%'
OR Clients.Address LIKE ImportedCompanyData_View.AddressForSearch + '%'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-16 : 10:40:15

SELECT COUNT(ClientID)
FROM
(
SELECT ClientID
FROM Clients
WHERE patindex('%' + lower(NameForSearch) + '%',lower(ClientName COLLATE Latin1_General_BIN)) > 0
UNION ALL
SELECT ClientID
FROM Clients
WHERE patindex(lower(Address) + '%',lower(AddressForSearch COLLATE Latin1_General_BIN)) > 0
) t1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-11-16 : 10:44:21

Yes anonymous1, I tried that way as well (with slight variation). That also takes a relatively long time.


SELECT COUNT(*)  FROM   Clients c
inner join ImportedCompanyData_View v on c.ClientName LIKE '%' + NameForSearch + '%'
			OR c.Address LIKE v.AddressForSearch + '%'

Any other method ?

Srinika

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-11-16 : 10:48:58

Hey Spirit1,

your answer doesn't have the View ImportedCompanyData_View, and I'm uncertain on how to incorporate that.

To clarify my requirement further:

I need the # of records in the client table,
which has Clientname like the data in NameForSearch field of View
or Address like the data in AddressForSearch field of View

Srinika

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-16 : 11:06:36

[code]
SELECT
(
SELECT COUNT(ClientID)
FROM
(
SELECT ClientID
FROM Clients
WHERE patindex('%' + lower(NameForSearch) + '%',lower(ClientName COLLATE Latin1_General_BIN)) > 0
UNION ALL
SELECT ClientID
FROM Clients
WHERE patindex(lower(Address) + '%',lower(AddressForSearch COLLATE Latin1_General_BIN)) > 0
) t1
)
FROM ImportedCompanyData_View
[/code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-11-16 : 13:31:45

Hi,
The Query takes for ever to run, which ever the way I used

Srinika

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-16 : 13:47:30

LIKE '%'+pat+'%' always require a full table scan...
And your query is even worse, it is actually not a INNER JOIN
It is almost like a CROSS JOIN

So you need to check count(ImportedCompanyData_View) * count(clients) combinations

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-16 : 14:13:21

Mladen -- I think the UNION will be worse, won't it? It will require two table-scans through the table, as opposed to just one. Either way no indexes are being used.

- Jeff
http://weblogs.sqlteam.com/JeffS

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-16 : 14:16:42

you can never be too sure with OR's

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-11-16 : 14:26:56

quote:
So you need to check count(ImportedCompanyData_View) * count(clients) combinations

I have a Table (Client) which has (ClientID, Clientname, Address) and lot of other columns
I have a View (ImportedCompanyData_View), which has (NameForSearch, AddressForSearch) and lot of other columns

I need to find the # of records in client table satisfying the following criteria.
Either the ImportedCompanyData_View.NameForSearch should be part of the text in the Client.Clientname field or
the ImportedCompanyData_View.AddressForSearch should be part of the text in the Client.Address field.

If I were to write the pseudo code for this:

Declare @recCount int

C: Get record from Client
Loop thru view to find a match
If found
Increment @recCount & go to C:
Else (after looking for all records in view)
go to C:

This doesn't mean that I need a looping function!! This is just to show my requirement.

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources