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

How to import MS Excel data into SQL Server 2005 Express Ed.

I am using SQL Server 2005 Express Edition for testing and developping my website. How can i import MS Excel Data into a SQL Server table?

The easy way?

Open your excel spreadsheet, hit control-a (Select All), control-c (Copy).

Open Management Studio, right click the table you want to "import" to, and select "Open Table". Then hit control-v (Paste).

So long as the table has the same number of columns as your spreadsheet, you're done.

|||

Thans for this solution! :) I can use that!

I also want to know what kind of other options can be used.

How to import & convert .BAK to .SQL

I'm a complete novice to SQL Server. I am attempting to move my company's website (incl. SQL db) from one hosting company to another (less expensive). The site is written in ColdFusion. Prior to moving, the "old" host made a .BAK copy of our SQL database. Together with all the html/cf files and folders I donwloaded this .BAK file. I am now attempting to set up the website with a new host. In order for the new host to be able to import my database they are telling me I have to convert it to preferably .SQL extension format, or .CSV format.

I have downloaded the free version of MS SQL Server 2005 and MS SQL Server Management Studio Express. Can this be used to convert the .BAK file? If so, I'd like a brief "How to...." because I am not able to find anywhere to import the .BAK file.

I friend of mine was able to convert the .BAK file to .MDF and .LDF files, but my new host can't work with this either. Would there be any benefit (if possible) to use these to convert to .SQL?

Anyone's quick help would be greatly appreciated. I'm trying to do this on a "shoe-string (i.e. free) budget".

toreddie

How was this file created? If if is a SQL Server backup, which folks generally specify with a BAK extension, you can only restore to SQL Server. When you do this, the data file (MDF & LDF) will be created.

Here is an example of the T-SQL:

RESTORE DATABASE {your database} FROM DISK = 'c:\backups\northwind\nwind.bak'
WITH
MOVE 'northwind' TO 'd:\Program Files\Microsoft SQL Server\Data\nwind_new.mdf'
MOVE 'northwind_log' TO 'd:\Program Files\Microsoft SQL Server\Data\nwind_new_log.ldf'

Thanks

Peter Saddow

|||

Peter,

Thanks. I have had help getting my .BAK converted to .MDF and .LDF, but the new host (GoDaddy.com) says they can't work with these extensions. They need something in .SQL format. I'm not even sure what this means.

toreddie

|||

No sure what they mean either. Is your database server "SQL Server", "MySQL" or something else? Looks like GoDaddy.com supports "SQL Server" and "MySQL". I found this on their site: http://help.godaddy.com/article.php?article_id=1429&topic_id=&prog_id=GoDaddy&

Seems straight forward to restore a SQL backup to one of their SQL Servers. But maybe you only have access to a MYSQL database, then you likely need to import/export the database using some tool.

Thanks,

Peter Saddow

|||

Peter,

As far as I know, and what I'm paying extra for, it is a Microsoft SQL server they are installing this on. It is not even me who is doing this, they are, because the only way I can do it is to have my .BAK file converted to a .CSV. GoDaddy offers a customer friendly "wizzard" that will walk you through uploading and installing a .CSV file.

I'm getting the feeling that the people at GoDaddy aren't server savy them selves (I'm absolutely not).

Is there actually a .SQL file format?

I've already uploaded both the .MDF and .LDF files to the server so to me it should be an easy task for them to copy this to their db server. However, I tend to think in easy terms and don't necessarilly understand the whole picture. SQL is brand new to me. I just started to get a handle on web design using Dreamweaver. Though I'm a big user of computers, I still don't master the more technical programs. I use them, but thus far had the luxury of hiring someone to set things up. Can't afford that anymore. That's why I am trying to do this my self.

toreddie

|||

.SQL file format is not a common SQL Server extension. But it really does not matter what the extension is. What matters is the format of the file. The .BAK file is likely a SQL Server backup, just because that is a common extension for SQL Server backups. From the link I sent you, I would try to restore the .BAK file as they suggested. Since they are suggesting you need to covert the file to .CSV, sound like they database might be something other than SQL Server.

You should clarify with them which database product you have paid for and have access to.

Good Luck,

Peter Saddow

|||

well.. people..

God, thank u for sending CodeSmith programmers to help us, to us, other programmers, with our stupid tasks, stupid hosting tools and stupid clients.

Thank you for Code Smith templates:

trick is this. Code Smith contains template scripttabldata.cst! run it and u will see! :)

|||

Hi Peter !

I read your answer to "How to import & convert .BAK to .SQL", but I am a novice to, to SQL 2005. How do you create the script( i prosume it is a script)? I hva been asked to create a db from a .bak file it was backed up.

If you could tell me how/where I can create(MSSM studio), i will be happy.

Sincerely Morten

How to import & convert .BAK to .SQL

I'm a complete novice to SQL Server. I am attempting to move my company's website (incl. SQL db) from one hosting company to another (less expensive). The site is written in ColdFusion. Prior to moving, the "old" host made a .BAK copy of our SQL database. Together with all the html/cf files and folders I donwloaded this .BAK file. I am now attempting to set up the website with a new host. In order for the new host to be able to import my database they are telling me I have to convert it to preferably .SQL extension format, or .CSV format.

I have downloaded the free version of MS SQL Server 2005 and MS SQL Server Management Studio Express. Can this be used to convert the .BAK file? If so, I'd like a brief "How to...." because I am not able to find anywhere to import the .BAK file.

I friend of mine was able to convert the .BAK file to .MDF and .LDF files, but my new host can't work with this either. Would there be any benefit (if possible) to use these to convert to .SQL?

Anyone's quick help would be greatly appreciated. I'm trying to do this on a "shoe-string (i.e. free) budget".

toreddie

How was this file created? If if is a SQL Server backup, which folks generally specify with a BAK extension, you can only restore to SQL Server. When you do this, the data file (MDF & LDF) will be created.

Here is an example of the T-SQL:

RESTORE DATABASE {your database} FROM DISK = 'c:\backups\northwind\nwind.bak'
WITH
MOVE 'northwind' TO 'd:\Program Files\Microsoft SQL Server\Data\nwind_new.mdf'
MOVE 'northwind_log' TO 'd:\Program Files\Microsoft SQL Server\Data\nwind_new_log.ldf'

Thanks

Peter Saddow

|||

Peter,

Thanks. I have had help getting my .BAK converted to .MDF and .LDF, but the new host (GoDaddy.com) says they can't work with these extensions. They need something in .SQL format. I'm not even sure what this means.

toreddie

|||

No sure what they mean either. Is your database server "SQL Server", "MySQL" or something else? Looks like GoDaddy.com supports "SQL Server" and "MySQL". I found this on their site: http://help.godaddy.com/article.php?article_id=1429&topic_id=&prog_id=GoDaddy&

Seems straight forward to restore a SQL backup to one of their SQL Servers. But maybe you only have access to a MYSQL database, then you likely need to import/export the database using some tool.

Thanks,

Peter Saddow

|||

Peter,

As far as I know, and what I'm paying extra for, it is a Microsoft SQL server they are installing this on. It is not even me who is doing this, they are, because the only way I can do it is to have my .BAK file converted to a .CSV. GoDaddy offers a customer friendly "wizzard" that will walk you through uploading and installing a .CSV file.

I'm getting the feeling that the people at GoDaddy aren't server savy them selves (I'm absolutely not).

Is there actually a .SQL file format?

I've already uploaded both the .MDF and .LDF files to the server so to me it should be an easy task for them to copy this to their db server. However, I tend to think in easy terms and don't necessarilly understand the whole picture. SQL is brand new to me. I just started to get a handle on web design using Dreamweaver. Though I'm a big user of computers, I still don't master the more technical programs. I use them, but thus far had the luxury of hiring someone to set things up. Can't afford that anymore. That's why I am trying to do this my self.

toreddie

|||

.SQL file format is not a common SQL Server extension. But it really does not matter what the extension is. What matters is the format of the file. The .BAK file is likely a SQL Server backup, just because that is a common extension for SQL Server backups. From the link I sent you, I would try to restore the .BAK file as they suggested. Since they are suggesting you need to covert the file to .CSV, sound like they database might be something other than SQL Server.

You should clarify with them which database product you have paid for and have access to.

Good Luck,

Peter Saddow

|||

well.. people..

God, thank u for sending CodeSmith programmers to help us, to us, other programmers, with our stupid tasks, stupid hosting tools and stupid clients.

Thank you for Code Smith templates:

trick is this. Code Smith contains template scripttabldata.cst! run it and u will see! :)

|||

Hi Peter !

I read your answer to "How to import & convert .BAK to .SQL", but I am a novice to, to SQL 2005. How do you create the script( i prosume it is a script)? I hva been asked to create a db from a .bak file it was backed up.

If you could tell me how/where I can create(MSSM studio), i will be happy.

Sincerely Morten

How to import & convert .BAK to .SQL

I'm a complete novice to SQL Server. I am attempting to move my company's website (incl. SQL db) from one hosting company to another (less expensive). The site is written in ColdFusion. Prior to moving, the "old" host made a .BAK copy of our SQL database. Together with all the html/cf files and folders I donwloaded this .BAK file. I am now attempting to set up the website with a new host. In order for the new host to be able to import my database they are telling me I have to convert it to preferably .SQL extension format, or .CSV format.

I have downloaded the free version of MS SQL Server 2005 and MS SQL Server Management Studio Express. Can this be used to convert the .BAK file? If so, I'd like a brief "How to...." because I am not able to find anywhere to import the .BAK file.

I friend of mine was able to convert the .BAK file to .MDF and .LDF files, but my new host can't work with this either. Would there be any benefit (if possible) to use these to convert to .SQL?

Anyone's quick help would be greatly appreciated. I'm trying to do this on a "shoe-string (i.e. free) budget".

toreddie

How was this file created? If if is a SQL Server backup, which folks generally specify with a BAK extension, you can only restore to SQL Server. When you do this, the data file (MDF & LDF) will be created.

Here is an example of the T-SQL:

RESTORE DATABASE {your database} FROM DISK = 'c:\backups\northwind\nwind.bak'
WITH
MOVE 'northwind' TO 'd:\Program Files\Microsoft SQL Server\Data\nwind_new.mdf'
MOVE 'northwind_log' TO 'd:\Program Files\Microsoft SQL Server\Data\nwind_new_log.ldf'

Thanks

Peter Saddow

|||

Peter,

Thanks. I have had help getting my .BAK converted to .MDF and .LDF, but the new host (GoDaddy.com) says they can't work with these extensions. They need something in .SQL format. I'm not even sure what this means.

toreddie

|||

No sure what they mean either. Is your database server "SQL Server", "MySQL" or something else? Looks like GoDaddy.com supports "SQL Server" and "MySQL". I found this on their site: http://help.godaddy.com/article.php?article_id=1429&topic_id=&prog_id=GoDaddy&

Seems straight forward to restore a SQL backup to one of their SQL Servers. But maybe you only have access to a MYSQL database, then you likely need to import/export the database using some tool.

Thanks,

Peter Saddow

|||

Peter,

As far as I know, and what I'm paying extra for, it is a Microsoft SQL server they are installing this on. It is not even me who is doing this, they are, because the only way I can do it is to have my .BAK file converted to a .CSV. GoDaddy offers a customer friendly "wizzard" that will walk you through uploading and installing a .CSV file.

I'm getting the feeling that the people at GoDaddy aren't server savy them selves (I'm absolutely not).

Is there actually a .SQL file format?

I've already uploaded both the .MDF and .LDF files to the server so to me it should be an easy task for them to copy this to their db server. However, I tend to think in easy terms and don't necessarilly understand the whole picture. SQL is brand new to me. I just started to get a handle on web design using Dreamweaver. Though I'm a big user of computers, I still don't master the more technical programs. I use them, but thus far had the luxury of hiring someone to set things up. Can't afford that anymore. That's why I am trying to do this my self.

toreddie

|||

.SQL file format is not a common SQL Server extension. But it really does not matter what the extension is. What matters is the format of the file. The .BAK file is likely a SQL Server backup, just because that is a common extension for SQL Server backups. From the link I sent you, I would try to restore the .BAK file as they suggested. Since they are suggesting you need to covert the file to .CSV, sound like they database might be something other than SQL Server.

You should clarify with them which database product you have paid for and have access to.

Good Luck,

Peter Saddow

|||

well.. people..

God, thank u for sending CodeSmith programmers to help us, to us, other programmers, with our stupid tasks, stupid hosting tools and stupid clients.

Thank you for Code Smith templates:

trick is this. Code Smith contains template scripttabldata.cst! run it and u will see! :)

|||

Hi Peter !

I read your answer to "How to import & convert .BAK to .SQL", but I am a novice to, to SQL 2005. How do you create the script( i prosume it is a script)? I hva been asked to create a db from a .bak file it was backed up.

If you could tell me how/where I can create(MSSM studio), i will be happy.

Sincerely Morten