CODEWITHSUNDEEP
sqlsql-servert-sqlindexing
Berrepoot
Berrepoot

Reputation: 141

which sql query is the best

I need a "search" function in my application, what is the best way to do this in SQL Server?

  1. Create a query with 1 large where-clause containing all the columns using an OR statement and creating 1 index containing all the columns
SELECT *
FROM Customer
WHERE CustomerNumber LIKE '%' + @SearchValue +'%'
OR LastName LIKE '%' + @SearchValue +'%'
OR FirstName LIKE '%' + @SearchValue +'%'
OR
  1. Create seperate queries for each column using UNION and create an index for each column
SELECT * 
FROM Customer 
WHERE CustomerNumber LIKE '%' + @SearchValue +'%'
UNION 
SELECT * 
FROM Customer
WHERE LastName LIKE '%' + @SearchValue +'%'
UNION
SELECT * 
FROM Customer
WHERE FirstName LIKE '%' + @SearchValue +'%'

I've read that indexes don't support the OR statement? So solution 2 is the only correct one?

This may seem a very easy question for some of you, but I'm trying to understand "why" I need to do this, so any background info is always welcome.

Upvotes: 0

Views: 127

Answers (4)

The Impaler
The Impaler

Reputation: 48780

Don't worry about indexes.

None of those queries will use any index. They'll perform Full Table Scans anyway since the search patterns start with %.

The first one will perform a single Full Table Scan, while the second one will perform three. Therefore the first one will be faster.

Also, keep in mind the second query will return slightly different result set since it removes duplicates.

Upvotes: -1

Dinu
Dinu

Reputation: 1524

It is not generally true for any rdb that OR conditions cannot be optimized; AFAIK skipping indexes are implemented by most RDBs, which means multiple-range conditions can be optimized. In your case however, because your LIKE conditions start with % and are thus not prefix matches, they cannot be optimized by indexes, because they are not range conditions. Even so, you should use the OR composition: it is easier to read, true to the intent of the query and it is very likely that the UNION query will do multiple table scans, one for every subquery while the OR query will likely do a single scan, which should be faster. I say "likely" because with an ideal query optimizer, all equivalent queries should be resolved to the best execution plan - in this case, a single scan. But optimizers are far from "ideal". So while with different rdb's YMMV, go with the OR query.

With a query like this though, if you are searching for words, you should consider using full-text indexes if available, and they are available in most rdbs but with different query syntaxes. https://www.mssqltips.com/sqlservertutorial/9136/sql-server-full-text-indexes/

Upvotes: 1

Krunal Barot
Krunal Barot

Reputation: 932

Both the queries are in a way for not optimum performance.

for option 1 - the or is scanning many possibilities and it is not better way to do.. you should actually have string indexing to combine all options together.

options 2 - this is slower then first option as it is doing multiple select options.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Neither is optimal, but the first is a bit better. It only scans the data once and the evaluation of like is short-circuited for matches to the first or second condition. In addition, union is going to incur overhead for removing duplicates.

If you really want performance, then investigate the full text indexing options provided by your database.

Upvotes: 3

Related Questions