SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Quick contains
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yoyosh
Starting Member

27 Posts

Posted - 03/08/2013 :  08:19:54  Show Profile  Reply with Quote
I have a table with ~10M of rows. One column in that table is nvarchar(200). I must perform queries against this table that return all rows in which this column containst some input parameter.

I tried:
select * from A where A.b like '%' + inputparameter + '%'

But this is dramatically slow.

Then I tried using fulltext search with CONTAINST function:
select * from A where contains(A.b, inputparameter)

It's quicker, but still not enough.

What else could I do? Does it make sense to define index on this column (even though I defined fulltextsearch index on it)? What else could be done to speed it up?

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 03/08/2013 :  11:47:57  Show Profile  Reply with Quote
Other than not using SQL, you might look into you disk system as that is usually the bottle-neck. But, first I'd identify the bottle-neck. Is the data spread over many disks? Are there multiple data files (also on differe disks)? Are you using SSD drives? How much memory do you have? 10M isn't that many rows, but using a "search" like that SQL can't really take advantage of any indexes, so that probalby won't help.
Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 03/08/2013 :  12:36:16  Show Profile  Reply with Quote
Hardware and architecture stuff is one thing. I will take it under consideration.

But now I am considering mainly sql-related techniques. Accodring to your post there is not much I could do, is it?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000