speed question

  • Thread starter Thread starter dzemo
  • Start date Start date
D

dzemo

I have table with 5 columns. ID (int) and "Name, LastName, FatherName,
Phone" as text field. In that table I have 200000 records. Also I have view
with statement

Select (Name + LastName + FatherName + Phone) as Search
From Users
Where Search like '%xxx%'

And it is very slow. When I replace xxx with anything (no matter of how much
results are) it is very slow (about 20-30 seconds) to display results.
I use VB.NET 2003 and SQL 2000. Should I use Data type "char" instead
"text", or index columns?
any ideas?
thx


--
 
The reason its slow is simple... first you are make the merging the columns
and then searching on that.

instead just do a search on individual fields.

Text datatype in SQL refers to large quantities of text. for finite it
should be char. though char uses fixed length strings so consider using
nvarchar.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
 
THe bottleneck could be a few different places but in all likelihood the
query itself is at least one of the problem. indexing the columns SHOULD
NEVER be done as a quick fix unless you have a good reason to do so. There
are many considerations but the cardinality of the data is one of the bigger
ones. I'm in total agreement with Dave BTW, but I'd look to redo the query
first. What is xxx usually? is it just one of the fields perhaps?
 
my bad... its that time of the day..i guess i should get some sleep

once again.

you are doing a string concatenation and then doing a string search.
instead do a search on individual columns.

Other way might be for you to set up correct indexes on that columns. it is
a good design practice to have all columns used in where clause to be a part
of some sort of index.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
 
You may want to change the UI so that 2 search criteria must be entered.
The first criteria should be something that will narrow the rest of the
search down considerably (and only searches one column of your table not
many of them). The second search criteria should be one that searches
through the results of the first.
 
Umm .. how strange .. that nobody sees it .. so I am worried that I might be
wrong, but I'll risk posting it anyway.

When you do a %xxx%, the query won't use an index.
When you do a concatenate and then do a like on it, the query won't use an
index.

The problem is your query !!!

And given you simplistic the case is - try changing the requirements a bit
(the UI maybe?) OR
Try redoing the table design a bit.

Also, I must argue, given 200,000 rows in a table, what if the user
specifies "a" as the input search criteria? You have to make an argument to
the end user that this requirement of "contains" must be tamed that a
malintent user is not able to bring the system down to it's knees. And if
the user says "I don't care ... do it anyway". Then do it anyway .. and let
it grind to it's knees; atleast then it isn't your fault :-). And then of
course when you can renegotiate requirements, renegotiate them right

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik
 
ok here is the deal. I have 200000 records about patient in one hospital and
I want to have smart search like iTunes or something like that. So I have to
merge all columns and then search that string with '%xxx%' (merge columns
should have "xxx" string inside somewhere) xxx-any string with min length
with 3 chars. And that work but is slow (very slow). Would help to change
data type of columns from text to char or someone have better idea haw to
perform smart search.
 
i think if you search individual columns you should normally get similar
results unless ofcourse the criteria is such that you have the last two
chars of column A and first 2 of column B.

unfortunately i dont use iTunes and so i dont know what you mean.

but considering a string column if you use

column1 like '%filtercondition%' then it will return all rows with the
filter condition.
As Ryan rightly mentioned the indexs would not come in to picture right now
as it is a string contatenation. But if you seperated the columns the
corresponding indexes if any would be used and that would help speed up the
process.
--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
 
ITunes? Ok I get it now.

Welcome to the unchartered territory of Full Text Indexing.

What you need to do is do a like on individual columns instead of a
concatenation.

In addition to that, do the following.

1. In enterprise mgr, select your d/b and then go to tools -> Full Text
Indexing.
2. Go through the wizard (it's self explanatory, or sql server books online
can help ya).
3. Then create a index population schedule. Keep it off hours.
4. The wizard will prompt you in the end that the full text catalog is not
yet populated, click OK to populate.
5. Generally this would be created under a directory called FTDATA in your
SQL Server data path. You can also start a full population thru enterprise
mgr.
6. In enterprise mgr, you wanna enable the full text search service.

Great, you're all set .. now instead of the stupid like %xxxx%, use
Contains(Lastname,'xxxx').

Few comments on practicality.

Full Text Search is tough on the d/b. Generally you don't wanna do this on a
high IO table. A good solution is to implement transaction log shipping or
even one way replication and implement this solution on another d/b on a
different machine.
Full Text Search/Indexing is however the only way you will acheive what you
are trying to (as far as I know).

Hope this helped :-)

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik





dzemo said:
ok here is the deal. I have 200000 records about patient in one hospital
and I want to have smart search like iTunes or something like that. So I
have to merge all columns and then search that string with '%xxx%' (merge
columns should have "xxx" string inside somewhere) xxx-any string with
min length with 3 chars. And that work but is slow (very slow). Would help
to change data type of columns from text to char or someone have better
idea haw to perform smart search.
Click to expand...
 
yeah but user can type for example "john Smith" where John is in
column-"Name" and Smith is in column - "LastName"
What then?
 
As I suggested in my earlier reply, you should split the UI data entry
fields up so that the search can be broken down into faster searches.
 
Back
Top