Using SuiteQL with the Connect Service

Testing Queries

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.

Syntax and Performance Best Practices

As you create queries using SuiteQL and the Connect Service, you must follow specific SuiteQL syntax requirements and best practices. For more information, see SuiteQL Performance and Best Practices.

Multiple Connections

Running multiple concurrent sessions can slow performance and the retrieval of results. For information about running multiple connections when working with SuiteAnalytics Connect, see Multiple Concurrent Sessions.

Roles and permissions

Roles and restrictions can result in performance issues. Ensure that you apply the necessary access restrictions for human interaction and consider adapting the access restrictions for automated machine-to-machine transfers. For more information about roles and permissions, see Connect Permissions.

Oracle SQL

Due to Oracle SQL syntax working internally with NetSuite Analytics, you should use Oracle SQL syntax when creating SuiteQL queries. Although SuiteQL supports ANSI SQL-92, there is a risk of critical performance issues, such as time outs that are not operationally remediable, that may occur if a SuiteQL is converted to SQL. This approach helps avoid the worst performance scenario, regardless of other factors. The following example shows how you can change your queries to use Oracle SQL syntax.

• The following query does not follow the Oracle SQL syntax:

                    select t.id, t.period, t.amount, a.name, p.name
  from transaction t
  join account a on a.id = t.account
  left outer join accountingperiod p on p.id = t.period 
  
                  

• The following query follows the Oracle SQL syntax:

                    select t.id, t.period, t.amount, a.name, p.name
  from transaction t,
  account a,
  accountingperiod p
  where a.id = t.account
  and on p.id(+) = t.period 
  
                  

• Note that 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