Hi,


I have the following SQL:


select * from tblProducts where prod_name like '%example%' or
prod_description like '%example%'


How do I order the results such that the results found by the prod_name
clause come first, followed by those found by the prod_description clause?

Do i have to union two selects? Is that an efficient way of doing
things? As you can probably tell, this is a search query so I need it to
be as fast as possible.


Thanks!

Andrew

Am Sat, 22 Jul 2006 11:53:27 GMT schrieb Andrew:

Maybe this works:

select field_1, field_2, ... from (
select 1 as abc, * from tblProducts where prod_name like '%example%'
union
select 2, * from tblProducts where prod_description like '%example%'
) as tblX order by abc, field_1, ...

It is only an idea, i have not tested it and i don't know how bad this will
be for speed, but using 'like' is a performance killer per se - if i am
right, then when using 'like' the query cannot use index, it always makes a
table scan...

bye, Helmut

I would try something like this

select * from tblProducts where prod_name like '%example%' or

prod_description like '%example%'

ORDER BY CASE WHEN prod_name like '%example%' THEN 0 ELSE 1 END

Tim S




Show quoteHide quote

Be aware that your query will require a scan because of the preceding
wildcard.  If performance is important, you'll need to experiment with
different techniques since the optimal method will depend on actual data and
use cases.  Non-clustered indexes on prod_name and prod_description might
help but performance here largely depends on the number of rows in the table
due to the scan(s).

Here's a synopsis of the 2 techniques suggested in this thread:

SELECT *
FROM tblProducts
WHERE
    prod_name LIKE '%example%' OR
    prod_description LIKE '%example%'
ORDER BY CASE WHEN prod_name like '%example%' THEN 0 ELSE 1 END

SELECT 1 AS Seq, *
FROM tblProducts
WHERE
    prod_name LIKE '%example%'
UNION
SELECT 2 AS Seq, *
FROM tblProducts
WHERE
    prod_description LIKE '%example%'
ORDER BY 1

Show quoteHide quote

Andrew (infoREM***@THISmuonlab.com) writes:
As Dan said, LIKE with a preceding wildcard will never be anywhere near
fast.

A tip is that if you can use varchar and an SQL collation you can gain
considerable performance. In some benchmarks I was involved with recently,
the difference for varchar with SQL collation as opposed to nvarchar or
varchar with Windows collation was a factor of 7!

Erland Sommarskog wrote:
Show quoteHide quoteOk thanks,

Could you explain a bit further what that means please?


Thanks

Andrew

I believe what Erland is saying is that your query will require extensive
string processing (scan prod_name and prod_description columns all rows) so
it might perform significantly better if the prod_name and prod_description
columns have a SQL collation rather than a Windows collation.  The SQL
Server instance collation is specified during installation and the default
differs varies depending on your Windows OS locale.  In the UK,
Latin1_General_CI_AS (a Windows collation) is the default server collation
but the default collation is SQL_Latin1_General_CP1_CI_AS in the US.  You
can override the database default collation for a given column by specifying
an explicit collation on a CREATE TABLE column definition.  See the Books
Online for more information.

If you are considering changing the columns to a SQL collation, I suggest
you run performance tests to see if the change is warranted in your
environment based on your performance target.  IMHO, mixed collations are a
pain to deal with so they should be avoided unless necessary.

Show quoteHide quote

Andrew (infoREM***@THISmuonlab.com) writes:
I'll try.

A collation defines rules for processing of character data, for instance
sorting and comparison. Here is an example:

  DECLARE @a nvarchar(200),
          @b nvarchar(200),
          @c nvarchar(200)
  SELECT @a = 'Ivar',
         @b = 'Iwar',
         @c = 'ivar'

  IF @a = @b COLLATE Latin1_General_CI_AI PRINT 'a = b' ELSE PRINT 'a <> b'
  IF @a = @b COLLATE Finnish_Swedish_CI_AI PRINT 'a = b' ELSE PRINT 'a <> b'
  IF @a = @c COLLATE Latin1_General_CI_AI PRINT 'a = c' ELSE PRINT 'a <> c'
  IF @a = @c COLLATE Turkish_CI_AI PRINT 'a = c' ELSE PRINT 'a <> c'

Here I force the collation with an explcit COLLATE clause. The different
results is due to different rules in different languages. In Swedish V
and W are (actually were until this year) variants of the same letter.
In Turkish I and i and upper- and lowercase of different letters, dotless
and dotted I. So that's why we have collations at all.

There are several level on which you can define the collation. One is at
the server level when you run Setup. You can also define it for a
database when you create it. And you can define specifically for a table
column. And, finally, as above you can force the collation for an
expression.

Originally, SQL Server worked only with 8-bit data, and there were only
sort orders for 8-bit character sets, like CP1252 (Latin-1) or CP1251
(Windows Cyrillic). These sort orders were private to SQL Server. With
SQL7, SQL Server moved to Unicode and adopted the sort orders and rules
defined by Windows. Whence the name SQL collation and Windows collation.

An SQL collation works in only with 8-bit data, which means that when
you use an SQL collation for Unicode (nchar/nvarchar) you get in fact
a Windows collation. A Windows collation always work with Unicode,
which means that char/varchar gets converted to Unicode under the
covers for string operations which needs the collation information.

Since an SQL collation works a smaller set of characters, it's more
efficient. For a regular join or sort operation, the gain is maybe
only 20-30 percent. But for an operation like "col LIKE '%search%'
the gain can be as drastic as a factor 7. You can examine this for
your query by forcing the collation with the COLLATE clause, as I
did above. (If your column is nvarchar, you need to convert it to
varchar to see any effect.)

Note: normally forcing the collation can rended an index unusuable,
as the index is organised to a different collation. But since no index
is very good for this query (execpt as a covering index), this is not
an issue.

I should also add that it is also possible to get high speed with a
Windows collation, to wit if you choose one which ends in _BIN. That
is, a binary collation. However, since this is case- and accent-sensitive
it may not meet user requirements.

Andrew wrote:
Show quoteHide quoteThanks for everyones help!


Andrew

datetime filtering
Combining 2 select statements
column dataType
Case statement question
Tuning