Advantage Database Forum

Question

Ads 10.10.0.49 With Arc32

Why this SQL :

SELECT *
FROM "Table"
WHERE CONTAINS(PhoneNumber, '*444*') AND (PhoneNumber LIKE '%(444)%')
ORDER BY PhoneNumber

is more slowest to this

SELECT *
FROM "Table"
ORDER BY PhoneNumber

Filter = CONTAINS(PhoneNumber, '*444*') AND ('(444)' $ PhoneNumber)

I have an index to PhoneNumber Field

Answer 1

0

The XBase expression "$" and the SQL expression LIKE are not equivalent.

LIKE can only be optimized if the leading character is not a wildcard character.

The use of the LIKE operator in a statement results in static cursor. If the field that is used in the expression is indexed, then the query can be optimized as long as the pattern does not begin with either wild card character (% or_). For example, the pattern 'Sm%' would make use of an existing index to restrict the range of records to those where the field begins with 'Sm'. The pattern '%row%' would not make use of any indexes because it requires a generic search of the field for the characters 'row'.

link

answered 22 Sep '14, 10:40

Edgar Sherman
5.4k●1●31●85
accept rate: 25%

With this SQL :

SELECT FROM "Table" WHERE CONTAINS(PhoneNumber, '444*') AND (PhoneNumber LIKE '%(444)%') ORDER BY PhoneNumber

If I exclude the ORDER BY, the result set appear after 0 Sec 502 ms.

But, if run the SQL with ORDER BY, the result set appear after 32 Sec 911 ms

My table contains 980000 records and I have an FTS index to PhoneNumber field.

If I change the FTS index to simple index on PhoneNumber Field, the result set without ORDER BY appear more faster, 7 Min 32 Sec.

(22 Sep '14, 10:48) Marius Cere

The LIKE operator forces a static cursor. With the ORDER BY the cursor needs to be fully populated and then the ORDER BY can be applied. Without the ORDER BY the server can return the result set after the first few records have been added.

You should see the same performance by doing a GO BOTTOM.

(22 Sep '14, 11:24) Edgar Sherman

I understand.

Thanks Edgar.

(22 Sep '14, 11:31) Marius Cere

Advantage Database Forum

SQL Performance - WHERE VS Filter

Follow this question

Announcements

Related questions