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

asked 18 Sep '14, 08:52

Marius%20Cere's gravatar image

Marius Cere
161182025
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'.

link

answered 22 Sep '14, 10:40

Edgar%20Sherman's gravatar image

Edgar Sherman
5.4k13185
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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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

Tags:

×325
×271
×93

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.