Counting rows ?

Hi,
Using SQL Server 2000 on Windows 2000 Server, i'm working on a 2 milion rows
database, in one table. The data are document and the column of my table are
"authors", "organisation", "title","abstract" (these four ones are fulltext
indexed) and other column with references number.
A web application provide a fulltext search on these four column (author,
affiliation, title and abstract). It provide the result in two step :
- First one : give the number of result
- Second one : give a link to the documents extracted from the database
(Because if there is too much document, the user can change his query to
have less.)
So my problem is the first step is too long : 30 to 60 seconds. I use this
query :
Code:
select count(*) from import
where (CONTAINS ( Title,'"tube" AND "heat"')
OR CONTAINS ( Abstract,'"tube" AND "heat"'))
AND (CONTAINS ( Organisation,'"CIA" OR "FNSEA"'))
and cast(PY as integer)>=2004
and cast(PY as integer)<=2006Is there a better solution ?
Thanks,
Jean-Michel
There is no real way to optimize this search. You might get some improvement
by rearranging this clause
and cast(PY as integer)>=2004
and cast(PY as integer)<=2006
I am not sure what the data type for PY is, but the cast operation will be
expensive for you as you are doing it against each row returned from the
contains results sets.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Famille Careil" <careil04@.tele2.fr> wrote in message
news:Ob6X1A0aFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Using SQL Server 2000 on Windows 2000 Server, i'm working on a 2 milion
rows
> database, in one table. The data are document and the column of my table
are
> "authors", "organisation", "title","abstract" (these four ones are
fulltext
> indexed) and other column with references number.
> A web application provide a fulltext search on these four column (author,
> affiliation, title and abstract). It provide the result in two step :
> - First one : give the number of result
> - Second one : give a link to the documents extracted from the database
> (Because if there is too much document, the user can change his query to
> have less.)
> So my problem is the first step is too long : 30 to 60 seconds. I use this
> query :
>
> Code:
> select count(*) from import
> where (CONTAINS ( Title,'"tube" AND "heat"')
> OR CONTAINS ( Abstract,'"tube" AND "heat"'))
> AND (CONTAINS ( Organisation,'"CIA" OR "FNSEA"'))
> and cast(PY as integer)>=2004
> and cast(PY as integer)<=2006Is there a better solution ?
>
> Thanks,
> Jean-Michel
>