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.
| Author |
Topic |
|
tungdt
Starting Member
7 Posts |
 Posted - 2012-02-11 : 23:52:00
|
| I have a database , this database has one table which has 126 Million records (I call this table is A).Table has a lot of fields , however I just intro about relevant fields.Id : Int primary key (with indexed).Title : nvarchar(200) (with indexed ).And I have a query like : SELECT count(id) FROM A WHERE Title like '%conditional%'This query is very simple and my target is : how many record in table did them match with my conditional, after that I try to paging data at client Search !For example : count(id)=10000 Rows/ 100 Rows per page,and client will see 100 pages.However, this query is very very slow (it took 9s to complete). I want this query to take 0.03s like a normal query.How can I do that?Any body can help me with this problems ?Thank you alot |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-12 : 03:48:34
|
| No way you can make that query fast. The leading wildcard means that the only way that can be evaluated is with an index or table scan.If you're often searching within strings, have you considered full text search?--Gail ShawSQL Server MVP |
 |
|
|
tungdt
Starting Member
7 Posts |
Posted - 2012-02-12 : 06:16:42
|
| Yes , I create full catalog of text index.But I can't search query like 'A c' (with white space) haizzzz, even SQL SERVER 2008 have tutorial for this problem. but It doesn't work.Do you have a solution for white space :D or advice for me ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-12 : 13:19:45
|
| SELECT count(id) FROM A WHERE Title like '%A c%'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
tungdt
Starting Member
7 Posts |
Posted - 2012-02-13 : 00:40:10
|
| @visakh16 :Firt of all , thank you so much.But , you don't understand my question2 my query will look like: SELECT count(ID) FROM AWHERE Contains(A.titlefile,'a')! //WORK PERFECT !!!ANDSELECT count(ID) FROM AWHERE Contains(A.titlefile,'a c')!//This query throw exception : systax is error at 'a'.Exception mean that : conditional have white space (between a and c), so sql query can't exec.Consequently, My question is : how to search CONTAINS function with whitespace).Moreover, If you use like query, your query would take a lot of time! Finally! Thank for helping me. Best wish to you |
|
|
|
tungdt
Starting Member
7 Posts |
Posted - 2012-02-15 : 10:53:09
|
| Hi, I gave it!!That is very simple : whitespace=- , conditional sound like '"a-c"'Thank all for the supporting me! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
problem with select like %conditional%