Ads With Arc32

Why this SQL :

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

is more slowest to this

FROM "Table"
ORDER BY PhoneNumber

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

I have an index to PhoneNumber Field

asked 18 Sep '14, 08:52

Marius%20Cere's gravatar image

Marius Cere
accept rate: 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'.


answered 22 Sep '14, 10:40

Edgar%20Sherman's gravatar image

Edgar Sherman
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
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 18 Sep '14, 08:52

Seen: 1,810 times

Last updated: 22 Sep '14, 11:31

Advantage Developer Zone Contact Us Privacy Policy Copyright Info

Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.