MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or two

[nydefender]

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
ID - nvarchar lenth-9
COMPANY - nvarchar lenth-30
NOTES - nvarchar length-250

Select * from database
where NOTES like '%something%'

Is there a way to get results from this query in less then 1-2 second
and how?

[Alwik]

Hello
I think that using like it would be impossible.
You will get better results when you use full text search (read about it in
books online), however I have not experience with looking for a phrase, but
with single words it works fast.
Alwik

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
ID - nvarchar lenth-9
COMPANY - nvarchar lenth-30
NOTES - nvarchar length-250

Select * from database
where NOTES like '%something%'

Is there a way to get results from this query in less then 1-2 second
and how?

[1492a2001]

On a Athon3200+ (32 bits) home computer
it takes 1692ms to search something like '%RRIDA%' on 393951 rows
table. The maximum length of a row is 3576 bytes. So you only need a
faster CPU and faster memory controler and enough memory to hold the
data pages in memory to achive subsecond time. But IMHO I think this
kind of search is a nonsense for this number of rows.

[nydefender]

So what kind of search are you recommend?

[helmut woess]

Am 14 Oct 2005 12:33:10 -0700 schrieb nydefender:

So what kind of search are you recommend?

What hardware do you use? And how long does it last to get the result? Have
you tried it with an index on NOTES? And i think, a second search should be
much faster then the first one. If you always search on NOTES maybe you can
hold a second table with only PK and field NOTES, which is redundant
(managed by triggers) but can be pinned into memory (DBCC PINTABLE() -
maybe a silly idea, only brainstorming).
Sometimes i have the same problem to find some records out of a big table
where it lasts up to 30 seconds. At first the user knows from
training/docu, that this could need a "long" time to proceed, second i show
a window with a wait-message and something blinking in it, so the user has
not the feeling that the program hangs.

bye,
Helmut

[Erland Sommarskog]

helmut woess ([e-mail]) writes:

(managed by triggers) but can be pinned into memory (DBCC PINTABLE() -
maybe a silly idea, only brainstorming).

Yes, DBCC PINTABLE was really a silly idea of Microsoft/Sybase. (Don't
really know who came up with it.) So silly, that in fact in SQL 2005, the
command DBCC PINTABLE is a no-op that performs nothing.

If a table is referenced often enough, it will be in cache anyway, so
PINTABLE has no effect. But if you pin a large table of which only portions
are referenced with some frequency, this means that you are wasting memory
that could have been used for other table, and thus degrade performance.

The only point I can see with PINTABLE is that you have table that you
query so rarely, that it will fall out of the cache. But when you need to
query it, you need the answers snap.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[1492a2001]

You want subsecond performance for your query. And the query can return
thousands of rows. How many time the clerk will spend searching for the
correct row?. subsecond querys are needed for routine operations and
they return only the necessary information to do the task, if not, the
worker is wasting his time. When you look for %something%, do you
really know what you are looking for?

In an hospitalizaton patient table, if I look for %seropositive% in the
observations field or even for %positive% I'm pretty sure its for a
report or an adhoc decission suport query and this doesn't need
subsecond response time. SQL Server is an OLTP system, designed for a
lot of small transactions, and this kind of queries is an incorrect use
of the system in my opinion.

You sould use something like Microsoft Search Service or a similar
product.

[nydefender]

Maybe this kind of query is "incorect" but is necessary. Now this query
takes for about 15-20 secs. I try to find a better way. I will try with
full text search.
Thanks to all of you.