address search 1

[stasJohn]

Hi all.

I'm using sql server 2005 and I guess I'd call myself an sql intermediate. Though I've had more experience with mysql than mssql.

The table looks like...
name, address1, address2, city, state, zip

I want to be able to use something like "city, state" or "address, zip" as a search term.

How would I go about doing this? You can't combine fields in the WHERE clause can you?

Thanks in advance.

 

[espar]

i might be missing your point but the table has the search terms as separate columns.

wouldnt it be best to split the search term in the interface and pass thru parameters?

declare @city = null,
declare @state= null etc etc

then just use a normal where clause like
WHERE ((city = @city and state = @state)
OR (address = @address and zip = @zip))

 

[stasJohn]

I just realized that this isn't hard.

I basically want to do the search very google-esque. That is provide a single search field that will match any combination of the data in the Table.

For example, I've created a table called "Person" with 7 columns. They are: name, phonenumber, address1, address2, city, state, zip

Example searches...
John
John 555-555-5555
12 Some St, 02345
Boston, MA

My new question is. How taxing on the server is the following sql statement?

SELECT * FROM PERSON WHERE
name LIKE '%...%' OR
phonenumber LIKE '%...%' OR
address1 LIKE '%...%' OR
address2 LIKE '%...%' OR
city LIKE '%...%' OR
state LIKE '%...%' OR
zip LIKE '%...%'

Thanks!

 

[Catadmin]

GAH! My boss would kill me if I used that query in production... (and he's a nice, peaceful type of guy! @=)

LIKE is VERY taxing on SQL Server. The more of them you have, and the smaller the string you put between the percent signs, the more draining it is on performance. Try not to use LIKE unless you absolutely have to.

It's always better to use exact values when and where you can. Such as Zip = 12345.

For more information, try looking at the Estimated Execution Plan or run the query against a Dev box and look at the real Execution Plan. Or, if you want, run Profiler against the box while running the query. Both will show you just how bad a performance hit you're getting.





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[stasJohn]

hehe. Thanks for the input Catadmin. Any suggestions on how I would go about performing such a search? Is Full Text searching the way to go? I've never used it so I'm not exactly sure how it works.

thanks again

 

[SQLBill]

Also, putting the wildcard % before the value just stopped your query from using any indexes.

You could use a CASE in the WHERE. First DECLARE all the variables and default them to NULL.

WHERE [name] = CASE WHEN @name IS NULL
THEN [name]
ELSE @name
AND phonenumber = CASE WHEN @phonenbr IS NULL
THEN phonenumber
ELSE @phonenbr

<etc>

What that does is change the WHERE clause based on the variables entered.

Lets say @name is SQLBill and @phonenbr is NULL. The WHERE then becomes:

WHERE [name] = 'SQLBill'
AND phonenumber = phonenumber

So, it will grab all rows that have a name of SQLBill and ANY phonenumber.

-SQLBill



Posting advice: FAQ481-4875

 

[gmmastros]

Bill,

That's true unless the phone number is NULL. In which case the record won't be returned.

Ex:

Declare @Temp Table(Name VarChar(20), PhoneNumber VarChar(20))

Insert Into @Temp Values('SQLBill', NULL)

Select * 
From   @Temp
WHERE [name] = 'SQLBill'
  AND phonenumber =  phonenumber

[green]-- This method works.[/green]

Declare @PhoneNumber VarChar(20)
Set @PhoneNumber = NULL

Select * 
From   @Temp
WHERE [name] = 'SQLBill'
      AND (@PhoneNumber is NULL OR phonenumber =  phonenumber)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

That last query should have been...

[tt][blue]Select *
From @Temp
WHERE [name] = 'SQLBill'
AND (@PhoneNumber is NULL OR phonenumber = [!]@[/!]phonenumber)
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

stasJohn,

If I understand correctly, your users can essentially type whatever they want in to a text field, and you want to search various fields to see if there is a match. No matter how you look at it, your query will generate a table scan, so performance will likely suffer. I've played around with this a little and have come up with a potential solution to your problem. I say this is a potential solution because I don't know if the performance will be fast enough to suit your application.

Select Person.*, A.MatchCount
From   (
       Select PersonId, 
              Sign(PatIndex('%' + IsNull(Name, '') + '%', @Search))
              + Sign(PatIndex('%' + IsNull(PhoneNumber, '') + '%', @Search))
              + Sign(PatIndex('%' + IsNull(Address1, '') + '%', @Search))
              + Sign(PatIndex('%' + IsNull(Address2, '') + '%', @Search))
              + Sign(PatIndex('%' + IsNull(City, '') + '%', @Search))
              + Sign(PatIndex('%' + IsNull(State, '') + '%', @Search))
              + Sign(PatIndex('%' + IsNull(Zip, '') + '%', @Search)) As MatchCount
       From  Person
       ) As A
       Inner Join Person
         On A.PersonId = Person.PersonId
Where  A.MatchCount > 0
Order By A.MatchCount DESC

I made a couple assumptions here. First, I assume you have a unique identifier for each record, and that it column name is PersonId. I also assume that the user's input is stored in a t-sql variable called @Search.

Just to explain a little...

PatIndex performs searches similar to LIKE. PatIndex returns an integer representing the character position where the string is found. In this case, we only care whether the match was found or not, so, I wrapped the PatIndex function inside the Sign function. Sign will return -1 for values that are negative (which can't happen with the PatIndex function). Sign returns 0 for an input of 0, and 1 for any positive value. We add the results together for each field. Essentially, this query ranks the relevance of the search criteria such that the more matches that are found will appear at the top of the output and those with less matches will appear at the bottom. If there are no matches at all, then the person will not show in the output at all.

I urge you to try this query. See how it performs. Then, let us know if it works for you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[stasJohn]

Thanks for all the suggestions everyone. I will try them out soon.

I'm very new to sql server so I'm not sure how to measure performance.

Catadmin said...
For more information, try looking at the Estimated Execution Plan or run the query against a Dev box and look at the real Execution Plan. Or, if you want, run Profiler against the box while running the query. Both will show you just how bad a performance hit you're getting.

Where do I find this? I'm using SQL Server Management Studio 2005 Express. Does that have and features to measure performance.

Thanks again!

 

[gmmastros]

There is no better judge of performance than execution time. What I usually do is...

Declare @Start DateTime
Set @Start = GetDate()

[green]-- query goes here[/green]

Select DateDiff(Millisecond, @Start, GetDate())

Try running the query for a couple different searches. If the execution time is acceptable, then you're done. If not, then you need to make it faster.

Please realize that this is a very simplistic view of performance, especially if you are working against a production database that has far fewer records than the live database.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

I'm not sure if Express has access to Profiler, but if it does, look in the Start -> Programs -> Microsoft SQL Server 2005 -> Performance Tools.

For the Estimated Execution Plan or the regular one, check in SSMS under the Query menu.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[espar]

stasJohn's method is very interesting - nice job.

However i would guess the sql server would have a fit executing that query !

ie. doesnt it do (in english terms)
for each row in db - patindex every column against the search string.
= manual table scan

sqlBill's answer is nice and would work well (with the null fix) - but being a dynamic sql query the server cannot cache the execution plan and it MAY be a resource hog of a query (indexing and tuning will fix most slow points here)

You wondered about what is the fastest method - execution wise?
A: static sql queries - unfortunately you have too many search criteria to implement.

It is ugly but from what i have seen the fastest method if the system is going to get a lot of hits on this code is to:

declare @search1
declare @search2

if not @search1 is null and not @search2 is null
sql where search1 = @search1 and search2 = @search2
else if not @search1 is null
sql where search1 = @search1
else if not @search2 is null
sql where search2 = @search2

being all static queries the server gets to cache execution plans and indexes and it is the fastest solution.

NOTE: used this method to get a 200%+ speed increase on text searches in a 500,000 catalog of music using sqlserver 2003.