AnsweredAssumed Answered

ExecuteSQL Problem

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

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

Outcomes