Accessing 2 millions records using full text searc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Accessing 2 millions records using full text searc

Author

Topic

saravan
Starting Member

1 Post

Posted - 2008-04-25 : 03:05:28

Hi,
My full text search on 2 millions records is taking time to show the result.
I have created full text catalog in RAM drive to make the retrival process faster.
But still its taking more than 1 minute to get the matching pattern.
I am using SQL server 2005. I have 2 columns (id,text) in my table..

This is my unique index script

USE [SAMPLE]
GO
CREATE UNIQUE NONCLUSTERED INDEX [ui_productid] ON [dbo].[Products]
(
[id] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]..

This is my primary key index script..
USE [SAMPLE]
GO
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]..

This is my query..

SELECT D.[id], D.productname
FROM dbo.Products AS D
WHERE CONTAINS(productname, 'ford')

What should i do to show the result in 3-4 seconds.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-04-25 : 07:28:43

For the volumes quoted I think you're optimistic about the projected performance. Regardless you haven't detailed the equipment you are using - it could be an old dos 286 PC for all we know.

It's a bit like saying you're disappointed youe car won't win a "Formula1" race.....without telling us you're driving a tractor.

What's your client, your server, your network speeds, hardwards, memoery, CPU utilisation, etc?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources