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 |
|
keith_ng
Starting Member
3 Posts |
 Posted - 2010-08-02 : 00:06:13
|
| I have a Tag table structure is (id int, tag nvarchar(400))but it have near 5000000 rowsNow when I use SELECT id FROM tbl WHERE tag LIKE N'%xxx%', it is very slow, around need 20 sec to get to result. It's using clustered index scan, but still slowly. have anybody can give me solution or suggestion? Thank you very much |
|
|
harrisunderwork
Starting Member
4 Posts |
Posted - 2010-08-02 : 00:40:39
|
| I think it depends how many results it return, greater the number, more time it will take. |
 |
|
|
keith_ng
Starting Member
3 Posts |
Posted - 2010-08-02 : 03:51:26
|
| Yes, If I use Select TOP 100 id, it can return below 1 sec.But I need to get the id to join the product, and I need the total count, so I need to get all records of the condition. If I use SELECT COUNT(*) also need around 20 sec. do you have any suggestion? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-02 : 04:35:39
|
| MyColumn LIKE 'xxx%'will be fast if MyColumn is indexedMyColumn LIKE '%xxx%'will be slow because it has to check EVERY entry.Free Text index may be a workaround (if you are looking for words / "language" matches) |
|
|
|
keith_ng
Starting Member
3 Posts |
Posted - 2010-08-02 : 04:53:06
|
| Free Text index is full-text indexing, right?I have try to use full-text indexing, the performance is so good. but I have a lot of Chinese, or other language in the field, use full-text indexing always cannot get my expected result... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-02 : 06:20:39
|
"Free Text index is full-text indexing, right?"Yes, that is what I meant  |
|
|
|
|
|
|
|
|
Question about performance of LIKE N'%xxx%'