SuiteQL Performance and Best Practices

Syntax Requirements

As you create queries using SuiteQL and the Connect Service, consider the following syntax requirements to avoid errors in your queries:

• For string concatenation, you cannot use the +operator. You should use the || operator instead. This restriction applies to both field and literal concatenation.

• You cannot use more than 1000 arguments in a single IN clause.

• You cannot use WITH clauses in your queries.

• You cannot use date literals. You must encapsulate dates using the to_date() function.

• You cannot use right outer joins in Oracle syntax. You can use ANSI syntax for outer joins. For example, the following Oracle SQL is not valid in SuiteQL:

                  select a1.id from account a1, account a2 where a1.id (+) = a2.id 
  
                

• SuiteQL supports the syntax for both ANSI and non-ANSI joins. However, you cannot use both syntax types in the same query.

• Do not use quotation marks for field names in subselections. See the following examples:

  • You should not use the following syntax with quotation marks:

                        select * from ( select a.externalid "AccountId" from account a) 
    
                      

  • You should use the following syntax without quotation marks:

                        select * from ( select a.externalid AccountId from account a) 
    
                      

Performance Best Practices

Consider the following best practices:

• Avoid creating complex queries that include nested SELECT clauses. Use simple queries instead.

• Avoid creating queries that include "SELECT *". Include the specific fields that you want to query instead. See the following example:

  • Avoid using the following example, which includes "SELECT *":

                        select * from transaction 
    
                      

  • Use the following example, which includes the specific fields:

                        select id, lastmodifieddate, tranid from transaction 
    
                      

• Avoid using the following field types in your queries when possible:

  • Calculated fields -Using calculated fields has an impact on performance. You can identify calculated fields by using the oa_columns table. Calculated fields are those that include "C" in the sixth position of the oa_userdata column. An example of a calculated field is customer.oncredithold. For more information about the oa_columns table, see oa_columns.

  • WLONGVARCHAR fields -These fields include large values and this has an impact on performance. See the following examples of WLONGVARCHAR fields:

    • item.featureddescription

    • item.storedetaileddescription

    • item.metataghtml

• Use filters in your queries. Using filters helps reducing the amount of data retrieved. When adding filter, consider the following:

  • _{Using indexed fields as filters enhances the performance when running queries. The following fields show examples of indexed fields:}

    • _{Primary keys such as id}

    • _{lastmodifieddate}

  • _{Using filters is a good option when doing incremental loads. The following query shows an example of incremental loads. Filters help retrieving only those results changed during a specific period, instead of retrieving data from the whole table each time. In the following example results were retrieved for the 1st of January of 2024:}

                        select id, lastmodifieddate, tranid from transaction where lastmodifieddate >= to_date('2024-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 
    
                      

  • Use the same type of filter expressions when adding filters. If you include different types, they need to be converted to the same type and this can slow performance. For example, ensure that you are not comparing TO_TIMESTAMP against TO_DATE functions. You should change your query to compare either TO_TIMESTAMP to each other or TO_DATE functions to each other.

• Use batches in your queries. When you split a query into several batches, you avoid running queries that take long to return results. This reduces the impact of maintenance server restarts. The following examples show queries that you should avoid and that you can use.

  • When running queries that should return a large number of rows, you should split them into batches. The following examples show queries that you should avoid:

                        select id, lastmodifieddate, tranid from transaction 
    
                      

  • The following queries show how you can split your queries into several batches:

    • select id, lastmodifieddate, tranid from transaction where id < 10000000

    • select id, lastmodifieddate, tranid from transaction where id >=10000000 and id < 20000000

    • select id, lastmodifieddate, tranid from transaction where id >=20000000 and id < 30000000

    • select id, lastmodifieddate, tranid from transaction where id >=30000000 and id < 40000000

    • select id, lastmodifieddate, tranid from transaction where id >=40000000 and id < 50000000

    • select id, lastmodifieddate, tranid from transaction where id >=50000000

• Avoid using too many joins. This can result in performance issues. Also, avoid using the same join in a specific table multiple times.

• Avoid using predicates with a logical disjunction (OR) of the arguments. Run a specific query for each predicate instead.

• Avoid using unnecessary sorting when possible. Run simple queries instead. In many cases, you can use inner joins. However, test your queries before you create a complex combination on top of your query.

• Using TOP clauses might have an impact on performance, as SuiteQL queries are run on a virtual schema and not on the actual database schema. The following example shows how you can change your queries to improve performance.

  • The following query includes the TOP clause and all results are evaluated, which can slow performance.

                        select top 100 id, lastmodifieddate, tranid from transaction 
    
                      

  • The following query does not include the TOP clause and improves performance.

                        select id, lastmodifieddate, tranid from transaction where id <= 100 
    
                      

• Evaluate the performance of the query after it is written. If the performance of the query is not ideal, you should experiment with query simplification or by replacing with semantically equivalent notation with better performance.

Related Topics