How to improve the efficiency when search data from more than 1000000 records?

Hi everyone,
My company has a website, use ms sql server. One table has more than 1000000 records.
When users search data from this table(such as search records which contain the word "school"

in NewsTile field.
And the server often occurred deadlock error.

How can I improve it?
Thanks.

P.S. The table has these fields:
NewsID
NewsTitle
NewsContent
NewsClickTimes
NewsInsertTime

1,000,000 records isn't too much... some of my clients have 90 million records and more. The most "expensive" search to do is a "contain the word" search (such asWHERE NewsContent LIKE '%school%')

But a lot has to do with the indexes:http://www.singingeels.com/Articles/SQL_Performance__Clustered_Indexes.aspx

Can you give me a sample query that takes a long time to run that you would like to see it speed up?

Thanks,

|||

First thanks for your reply.

Select NewsID, NewTitle, NewsClickTimes from News where NewsTitle like '%xxx%' order by NewsClickTimes desc, NewsInsertTime desc

The result of search must oder by ClickTimes first, if these records have same Click Times, oder by inser time.

Set the two filed, NewsClickTimes and NewsInsertTime as Clustered index. First is NewsClickTimes.

But if search records contains one word, use like '%school%', the search can't use index.

Can you give me some suggetions about improve this situation?

|||

Hi cime63,

First, based on my understanding, i think one table cannot have more than one clustered index. So, you cannot set both NewsClickTimes and NewsInsertTimes as clustered index.

Second, "But if search records contains one word, use like '%school%', the search can't use index. " --> I think in this case still we can use full-text index. For example:

Select NewsID, NewTitle, NewsClickTimes from News where contains(NewsTitle,' "*school*"') order by NewsClickTimes desc, NewsInsertTime desc

More information , you can refer to :http://technet.microsoft.com/en-us/library/ms187787.aspx (see the examples in this article)

Hope my suggestion helps

|||

HiBo Chen – MSFT,

First thanks for your suggestion.

What I say above is not exact. Now I want to correct it.

Bo Chen – MSFT:

First, based on my understanding, i think one table cannot have more than one clustered index. So, you cannot set both NewsClickTimes and NewsInsertTimes as clustered index.

I know one table can only has one clustered index. What I mean is I set the two fileds "NewsClickTimes" and "NewsInsertTimes" as complex clustered index. First is NewsClickTimes, and then is NewsInsertTimes.

Bo Chen – MSFT:

I think in this case still we can use full-text index.

About full-text index, I heard it. But at the same time, I heard that the result is not exact especially when search Chinese from string(it's a Chinese system). Is this opinion right or not?

Of course I'll have a look at the article you give me.

Thanks again.

|||

cime63:

Bo Chen – MSFT:

I think in this case still we can use full-text index.

- Full-text searches are only applicable if the field is a text field. What is the datatype of the field?

Also, is this SQL Server 2000 or 2005?

|||

Having battled with the indexes, if your search will be as ... LIKE '%something%' then the index will not be used. SQL Server will do a full scan. See if you can get your business to narrow down the search to at least first couple of letters rather than a complete wild search.

LIKE 'A%' will be much better than LIKE '%A%'.

|||

Instead of using the like statement, try using a case statement where you only compare the value if certain criteria is met. The "where NewsTitle like '%xxx%' " is your problem (or at least a big part of it).

|||

Nullable:

What is the datatype of the field?

Also, is this SQL Server 2000 or 2005?

The datatype of the field (User will search records contain one word) is nvarchar(64), not text. And we still use SQL2000 now.

|||

ndinakar:

Having battled with the indexes, if your search will be as ... LIKE '%something%' then the index will not be used. SQL Server will do a full scan. See if you can get your business to narrow down the search to at least first couple of letters rather than a complete wild search.

LIKE 'A%' will be much better than LIKE '%A%'.

Thanks you reply.

But in this system, we muse allow user search records that contain one word users input.

Like 'A%' is not enough.

Has anyone use lucence in actual project?

|||

dotnetjunkie2006:

Instead of using the like statement, try using a case statement where you only compare the value if certain criteria is met. The "where NewsTitle like '%xxx%' " is your problem (or at least a big part of it).

Thanks your reply.

But can I use case statement in this situation?

|||

OK, to clarify a few things:

1) Will SQL use your index for a LIKE searchsurrounded by wild cards?
answer) No, a search for "%abc%" will not use the index.

2) Is 1,000,000 records "a lot" to scan with this kind of a search?
answer) Not really... it should take about 1 to 2 seconds.

3) Do you have proof?
answer) Of course :)

I made a table with a VARCHAR(100) field, and populated it with1.6 million records and did the following searches:

SELECT*FROM _myTempDBWHERE TestFieldLIKE'1234%'
-- With no index : Cost = 11.184 (1 second)
-- With PLAIN OLD INDEX : Cost = 0.006 (less than 1 second)

SELECT*FROM _myTempDBWHERE TestFieldLIKE'%1234%'
-- With no index : Cost = 12.750 (2 seconds)
-- With PLAIN OLD INDEX : Cost = 12.750 (2 seconds)

Notice, like was mentioned by someone above that removing the '%' from the front of the search does speed things up... buteven with it still in there, you're query should take about 1 to 2 seconds. (like I said, I have 1.6 million records for my test).

Does that answer your questions?

|||

cime63:

case statement in this situation?

- No, you could use a "CHARINDEX" statement, but the results are the same.

|||

Thanks very much.

But I must search records which contain a word.

|||

Is there anyone can help me?

sql