Advantages?

I would welcome opinions on whether FTS would help in our scenario.
The table has about 28 million rows and has about 10,000 rows added every
morning in an intensive batch process. Then another batch query process is
run that looks for partial name matches.
Eight of the fields we query against are char or varchar, all with single
words.
Sample query: SELECT casenumber FROM table WHERE fname LIKE 'j%' AND lname
LIKE 'smi%'. Rreturns 55,874 rows in 7 seconds in QA.
I am also concerned about time to populate FT index every day.
Machine is dual Xeon 3.2 with 4GB ram, OS on RAID1 & SQL data on RAID5. Win
2003 Server and SQL2000, with SQL2005 later this year.
Thanks much!
Wow! That's fantastic speed for a like. I'd try change tracking. IIRC on a
similar machine I was able to hit 2000 rows per minute with SQL 2000.
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
"Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
news:FFF888F6-8310-4B1F-83B0-399830C17F53@.microsoft.com...
>I would welcome opinions on whether FTS would help in our scenario.
> The table has about 28 million rows and has about 10,000 rows added every
> morning in an intensive batch process. Then another batch query process is
> run that looks for partial name matches.
> Eight of the fields we query against are char or varchar, all with single
> words.
> Sample query: SELECT casenumber FROM table WHERE fname LIKE 'j%' AND lname
> LIKE 'smi%'. Rreturns 55,874 rows in 7 seconds in QA.
> I am also concerned about time to populate FT index every day.
> Machine is dual Xeon 3.2 with 4GB ram, OS on RAID1 & SQL data on RAID5.
> Win
> 2003 Server and SQL2000, with SQL2005 later this year.
> Thanks much!
|||Thanks Hilary, but do you think a Full Text search would be faster searching
on single words?
"Hilary Cotter" wrote:

> Wow! That's fantastic speed for a like. I'd try change tracking. IIRC on a
> similar machine I was able to hit 2000 rows per minute with SQL 2000.
> --
> 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
> "Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
> news:FFF888F6-8310-4B1F-83B0-399830C17F53@.microsoft.com...
>
>
|||It will be for larger tables. I am not sure where the cut off it - i.e.
20,000 rows or 2 million.
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
"Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
news:FDFBA954-00ED-4444-A7EF-2B5547510343@.microsoft.com...[vbcol=seagreen]
> Thanks Hilary, but do you think a Full Text search would be faster
> searching
> on single words?
> "Hilary Cotter" wrote: