AnsweredAssumed Answered

ExecuteSQL Problem

Question asked by johnyx2 on Dec 5, 2018
Latest reply on Dec 6, 2018 by johnyx2

Like • Show 0 Likes0
Comment • 9

Hello,

Please see this ExecuteSQL calculation

ExecuteSQL (

"SELECT

\"report\".\"amount\", \"report\".\"quantity_purchased\"

FROM \"report\"

WHERE

\"report\".\"amount_description\" IN ( ? )

AND \"report\".\"sku\" IN ( ? )

AND \"report\".\"order_id\" IN ( ? )";

""; "";

"shippingcost";

"mySku1";

"002-0647271-4534647"

)

This query takes almost 1 minute to return the result

Compare to:

ExecuteSQL (

"SELECT

\"report\".\"amount\", \"report\".\"quantity_purchased\"

FROM \"report\"

WHERE

\"report\".\"amount_description\" IN ( ? )

AND \"report\".\"sku\" IN ( ? )

AND \"report\".\"order_id\" = ?";

""; "";

"shippingcost";

"mySku1";

"002-0647271-4534647"

)

This query returns the result in a fraction of a second, immediate

The only difference is the 3rd. request in the WHERE clause.

In the first example the comparison operator is IN ( ? ), in the second is = ?

Why could it be that IN ( ? ) takes so long, in the 3rd request, but it does not affect the other two?

I tested the other 2 requests with IN ( ? ) and = ?, and there is no difference

Could it be something in the data format "002-0647271-4534647" ?

All fields are Text Indexed

Thanks

No one else has this question

Outcomes

planteg Dec 5, 2018 7:41 PM

I don’t get why you use IN in your query, you should use =.

IN is used to specify that a field contents matches one value in a list.

Actions

johnyx2 @ planteg on Dec 5, 2018 8:26 PM

Each request has multiple values.

I did the test with only 1 value, so I could to compare IN ( ? ) against = ?

This is how I found out about the difference in speed on the 3rd. request.

I still need to use IN (?, ?, ?... )

Actions

user19752 @ planteg on Dec 5, 2018 8:48 PM

I agree the example has no reason to use IN, but IN can have sub query like

IN (SELECT ...)

and it can have any number of result, so I expect same performance than using = if IN has only one value.

I haven't tested but this may be one of some issues on FM SQL optimization.

Actions

johnyx2 @ user19752 on Dec 5, 2018 9:12 PM

user19752 wrote:

I agree the example has no reason to use IN,

There is a reason the example uses IN.

It is because, it is the only way to compare performance against =, since = only takes a single value

The example is to illustrate the issue.

user19752 wrote:

I expect same performance than using = if IN has only one value.

I agree, that is why I open this case

The question still remains:

Why there is a significant degradation in performance in the report::order_id field when comparing IN against =, and no difference in performance in the report::amount_description and the report::sku fields?

Looks like IN and = with only 1 value, are not the same in some situations.

I wanted to clarify that all results are correct, there is no problem with that. The problem is performance, only on this one field.

Hope someone who has seen this before can shed some light

Actions

johnyx2 Dec 6, 2018 10:54 AM

Upon further testing, I found that if I change the order of the \"report\".\"order_id\" IN ( ? ) to the first position, as opposed to the 3rd. (as in the example), the result is immediate, no performance problem.

I take this may be a bug.

I prepared a file with only the data for the query. Unfortunately it is a 1GB file, 8,000,000 records.

Maybe Filemaker should take a look at it.

Where can I post the file or send it to?

Thanks

Actions

planteg @ johnyx2 on Dec 6, 2018 11:03 AM

I wonder if it may have to do with Index of the absence of an Index ?

ExecuteSQL SELECTs are translated to FileMaker native finds, maybe with the change of order the equivalent native find is better.

1 person found this helpful

Actions

user19752 @ planteg on Dec 6, 2018 1:42 PM

We can't expect full optimize in FMSQL now, so only first criteria in ANDs would use index.

Actions

user2148131 Dec 6, 2018 2:28 PM

Hello!
Try this:

ExecuteSQL (

"SELECT

\"amount\", \"quantity_purchased\"

FROM \"report\"

WHERE \"amount_description\" LIKE ?

AND \"sku\" LIKE ?

AND \"order_id\" LIKE ?
"
;""; "";

"shippingcost";

"mySku1";

"002-0647271-4534647"

)

Actions

johnyx2 @ user2148131 on Dec 6, 2018 9:44 PM

user2148131 wrote:

Hello!
Try this:

ExecuteSQL (
"SELECT
\"amount\", \"quantity_purchased\"
FROM \"report\"
WHERE \"amount_description\" LIKE ?
AND \"sku\" LIKE ?
AND \"order_id\" LIKE ?
"
;""; "";
"shippingcost";
"mySku1";
"002-0647271-4534647"
)

Thanks user2148131

This actually takes 2 minutes, instead of 1.

But, with a wildcard added to the values, as in "shippingcost%"; "mySku1%"; "002-0647271-4534647%" it returns the result in 0.026 seconds, magic!

However, "mySku17" and "mySku134" do exist, so this will not work

Actually I already have good workarounds as described above.

I am still hoping to get clarification, if my assumptions, that:

• IN ( ? ) should return results in the same amount of time as = ?

• the order of the WHERE requests, have no influence on the amount of time in which the result is returned

are true.

If they are true, then there is something for Filemaker to fix

If not, I would certainly like to have confirmation that this is to be expected, and learn more about any other possible ExecuteSQL unexpected (for me) behavior, so I can adjust my development accordingly.

If this is how it actually works, who knows how many unoptimized ExecuteSQL statements have I written, that will show their teeth as the data grows.

If someone knows if this is, or is not, how it is supposed to work, a comment would be greatly appreciated

Thanks

Actions

9 Replies

planteg Dec 5, 2018 7:41 PM
Mark Correct
Correct Answer
I don’t get why you use IN in your query, you should use =.

IN is used to specify that a field contents matches one value in a list.
Like • Show 1 Like1
Actions
johnyx2 @ planteg on Dec 5, 2018 8:26 PM
Mark Correct
Correct Answer
Each request has multiple values.
I did the test with only 1 value, so I could to compare IN ( ? ) against = ?
This is how I found out about the difference in speed on the 3rd. request.
I still need to use IN (?, ?, ?... )
Like • Show 0 Likes0
Actions
user19752 @ planteg on Dec 5, 2018 8:48 PM
Mark Correct
Correct Answer
I agree the example has no reason to use IN, but IN can have sub query like
IN (SELECT ...)
and it can have any number of result, so I expect same performance than using = if IN has only one value.
I haven't tested but this may be one of some issues on FM SQL optimization.
Like • Show 0 Likes0
Actions
johnyx2 @ user19752 on Dec 5, 2018 9:12 PM
Mark Correct
Correct Answer
user19752 wrote:

I agree the example has no reason to use IN,
There is a reason the example uses IN.
It is because, it is the only way to compare performance against =, since = only takes a single value
The example is to illustrate the issue.

user19752 wrote:

I expect same performance than using = if IN has only one value.
I agree, that is why I open this case

The question still remains:
Why there is a significant degradation in performance in the report::order_id field when comparing IN against =, and no difference in performance in the report::amount_description and the report::sku fields?
Looks like IN and = with only 1 value, are not the same in some situations.
I wanted to clarify that all results are correct, there is no problem with that. The problem is performance, only on this one field.

Hope someone who has seen this before can shed some light
Like • Show 0 Likes0
Actions
johnyx2 Dec 6, 2018 10:54 AM
Mark Correct
Correct Answer
Upon further testing, I found that if I change the order of the \"report\".\"order_id\" IN ( ? ) to the first position, as opposed to the 3rd. (as in the example), the result is immediate, no performance problem.
I take this may be a bug.

I prepared a file with only the data for the query. Unfortunately it is a 1GB file, 8,000,000 records.
Maybe Filemaker should take a look at it.
Where can I post the file or send it to?

Thanks
Like • Show 0 Likes0
Actions
planteg @ johnyx2 on Dec 6, 2018 11:03 AM
Mark Correct
Correct Answer
I wonder if it may have to do with Index of the absence of an Index ?

ExecuteSQL SELECTs are translated to FileMaker native finds, maybe with the change of order the equivalent native find is better.
1 person found this helpful
Like • Show 0 Likes0
Actions
user19752 @ planteg on Dec 6, 2018 1:42 PM
Mark Correct
Correct Answer
+1

We can't expect full optimize in FMSQL now, so only first criteria in ANDs would use index.
Like • Show 2 Likes2
Actions
user2148131 Dec 6, 2018 2:28 PM
Mark Correct
Correct Answer
Hello!
Try this:

ExecuteSQL (
"SELECT
\"amount\", \"quantity_purchased\"
FROM \"report\"
WHERE \"amount_description\" LIKE ?
AND \"sku\" LIKE ?
AND \"order_id\" LIKE ?
"
;""; "";
"shippingcost";
"mySku1";
"002-0647271-4534647"
)
Like • Show 0 Likes0
Actions
johnyx2 @ user2148131 on Dec 6, 2018 9:44 PM
Mark Correct
Correct Answer
user2148131 wrote:

Hello!
Try this:

ExecuteSQL (
"SELECT
\"amount\", \"quantity_purchased\"
FROM \"report\"
WHERE \"amount_description\" LIKE ?
AND \"sku\" LIKE ?
AND \"order_id\" LIKE ?
"
;""; "";
"shippingcost";
"mySku1";
"002-0647271-4534647"
)
Thanks user2148131

This actually takes 2 minutes, instead of 1.
But, with a wildcard added to the values, as in "shippingcost%"; "mySku1%"; "002-0647271-4534647%" it returns the result in 0.026 seconds, magic!
However, "mySku17" and "mySku134" do exist, so this will not work

Actually I already have good workarounds as described above.

I am still hoping to get clarification, if my assumptions, that:
• IN ( ? ) should return results in the same amount of time as = ?
• the order of the WHERE requests, have no influence on the amount of time in which the result is returned
are true.

If they are true, then there is something for Filemaker to fix
If not, I would certainly like to have confirmation that this is to be expected, and learn more about any other possible ExecuteSQL unexpected (for me) behavior, so I can adjust my development accordingly.
If this is how it actually works, who knows how many unoptimized ExecuteSQL statements have I written, that will show their teeth as the data grows.

If someone knows if this is, or is not, how it is supposed to work, a comment would be greatly appreciated

Thanks
Like • Show 0 Likes0
Actions

ExecuteSQL Problem

Outcomes

Related Content

Recommended Content