CONVERT vs LIKE -> Who's the winner?

[JeanNiBee]

Hi

I was wondering about the internal mechanics of the CONVERT function and the LIKE condition.

Here's an example:

Let's say I have data in SomeTable called 'desc'of the following format.

------------------------------
| ID  | Desc                  |  
------------------------------
|  1  | BLOOMBERG - X - 0045  |
|  3  | BLOOMBERG - X - 0046  |
|  4  | BLOOMBERG - W - 0047  |
|  5  | BLOOMBERG - X - 0048  |
|  6  | BLOOMBERG - Y - 0045  |
|  7  | BLOOMBERG - Y - 0045  |
|  8  | BLOOMBERG - Z - 0041  |
------------------------------

Assume this list is 100,000 rows long, all unique all
following the same pattern. I.E. The first 13 chars of a 'Bloomberg' entry determines it's type.

I could query this information in one of two diffrent ways..

-

SELECT id FROM SomeTable where desc like "BLOOMBERG - X%"

Returns 1, 3, 5

or
-

SELECT id FROM SomeTable WHERE convert(varchar(13, desc)) = "BLOOMBERG - X"

Returns 1, 3, 5

I assumed (at the time this question came up at work, wrongfully) that the CONVERT would be faster as it wouldn't have to do regular expression checking. Plus a performane tuning tip is to use substrings of fields instead of using the full field. So running the convert should use the lowest common denominator to determine equality.

A point brought up by a colleague is that the convert is slower because it has to convert ALL the entries in the table before testing for equality. The LIKE condition can throw a row away the minute a char in the field doesn't match the pattern, thus, saving time not having to 'scan' the first 13 chars of EVERY SINGLE ROW.

Is there any reading material on this and how each of these methodologies performs 'under the hood'.

I'll be checking out the Sybase site to see what I can find but I would love to hear what some of you have to say about this.

Thanks.
-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

TGML reference:

http://www.tipmaster.com/includes/tgmlinfo.cfm?w=450&h=450"

]

 

[onealm]

Using AColumn LIKE 'SOME TEXT%' is interpreted by the parser into the following operation which can be nicely optimized if the column you use is indexed:

AColumn >= 'SOME TEXT' AND AColumn <= 'SOME TEXT' + &quot;CHAR&quot;(255)

The query parser is smart enough to recognize that anything preceding the first % symbol in a LIKE pattern is a candidate for an index-based search.