optimize the search

[amiw]

Hi below is the structure of our database

CREATE TABLE [dbo].[dbtable] (
[DBkey] [int] IDENTITY (1, 1) NOT NULL ,
[nvarchar] (50) ,
[Name] [nvarchar] (100) ,
[Keywords] [nvarchar] (255) ,
[About] [nvarchar] (255) ,
[Url] [nvarchar] (100) ,
[Region] [int] NULL ,
[Done] [bit] NULL
) ON [PRIMARY]
GO

There are currently 2-3 million records within the database growing at a rate of 20 thousand + records per week.

Issues
Currently it runs on SQL SERVER 7 on a shared server with the search only working for a one keyword search.
We just use an SQL statement similar to something like this --- SELECT * FROM table WHERE LIKE ...

We are looking for help to optimize the search.
Would moving to 2000 and using the full text search properties be the best way to go?

hope you can help
Michael.

 

[fluteplr]

I assume your like query looks something like

where keywords like '%someword%'.

As you have discovered sql server cannot optimize a like query with the '%' at the beginning.

Personally I would do a redesign of the database.

Add a second table (keywords) with the following fields

keywordkey,
dbkey,
keyword

that is related to the original table on dbkey.

Add a separate row in keywords for each keyword in each record of dbtable.

Index keywords on keywords.
I assume there is an index dbkey in dbtable

the query then looks something like this.

select * from dbtable
where dbkey in
(select dbkey from keywords where keyword = 'someword')

or

select * from dbtable
where dbkey in
(select dbkey from keywords where keyword like 'some%')

This type of query can be optimized and should be very fast. Note that in the second example using like the '%' sign is ONLY at the end.

 

[amiw]

doing a redesign of the database with over 2 million records... how difficult would that be on a shared server without admin priveleges?

thanks
Michael

 

[fluteplr]

About as easy as adding full text search and going to a new server.

Your data your call.

 

[amiw]

flutepir..
when you say redesign would i need to get rid of all the records already there...
the reason i say this is cos... how would the separate all the keywords that are already there and put them in their own field with their own id in a new table... surely this would be extremely difficult

thanks.
mic