a suiteql query on transaction line is very fast when using

tl.item = 1

, but very slow when using IN (

tl.item IN (1, 2, 3)

How can I write IN queries that still use index

AI generated answer (using internal Slack threads):

Based on the conversations, it seems that you are trying to optimize the performance of your SuiteQL queries when using the

IN

clause. The

IN

clause can be slower because it has to check each value in the list you provide, which can be time-consuming if the list is large. One way to optimize this is to use a parameterized query. Instead of directly inserting the values into the

IN

clause, you can pass them as parameters. This can help the database optimize the query execution. Here's an example of how you can structure it:

sql
let idsArray = [1, 2, 3]; // your array of IDs
let placeholders = idsArray.map((_, i) => `$${i + 1}`).join(','); // creates a string like $1,$2,$3
let query = `SELECT * FROM TransactionLine WHERE CreatedFrom IN (${placeholders})`;
let params = idsArray;
let result = nlapiRunSuiteQL(query, params);

In this example,

idsArray

is your array of IDs that you want to use in the

IN

clause. The

placeholders

variable creates a string of placeholders that you can use in your query. The

params

variable is the array of values that will replace the placeholders in the query. Please note that this is just a general approach and you might need to adjust it based on your specific use case and environment. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******