• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 771
  • Last Modified:

How do I make a SQL Wildcard search run faster?

I'm doing an OnKeyUp search that looks like this:

SELECT (8 Rows from all three tables) FROM ORDERS o JOIN CLIENTS c ON c.ClientId = o.ClientId JOIN VENDORS v on v.VendorId = o.VendorId WHERE o.ClientName like '%txtCriteria.Text%' OR o.AttentionTo like '%txtCriteria.Text%'

How can I speed up this search?

Indexes?  Filtering in C#?

Thanks,

ASP.NET 3.5, SQL2008, VS2008
0
LukeJD
Asked:
LukeJD
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in short: you cannot....
unless you use full-text indexing (and query syntax) instead of the LIKE ... % syntax
you might consider giving the use the "full-text" search: fast, but eventually not 100% accurate, and a "slow", but "accurate" mode using the LIKE
0
 
chapmandewCommented:
you can IF you are able to remove the initial %...like this:

SELECT (8 Rows from all three tables) FROM ORDERS o JOIN CLIENTS c ON c.ClientId = o.ClientId JOIN VENDORS v on v.VendorId = o.VendorId WHERE o.ClientName like 'txtCriteria.Text%' OR o.AttentionTo like 'txtCriteria.Text%'
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now