Resource-consuming SQL statements can cause long latency, interruptions between the leader and follower replicas, or crash of instances. Such SQL statements take a long time to execute, which means that they occupy resources for a long time and reduce the concurrency of a cluster. In situations such as a sudden increase in business traffic, which may be caused by traffic surges and network jitters, the business can be affected by the accumulation of SQL statements and an excessive number of concurrent jobs. OceanBase Database has no limit on the number of joined tables. However, you can use only complex functions related to regular expressions for table join operations, such as the regexp_substr function. Such functions can affect business because the execution performance is poor.
To keep a database predictable, scalable, and controllable, the best business practice is to use simple SQL statements for data addition, deletion, modification, and query. A well-written business SQL statement must be simple enough to a reasonable extent. This means that the resource consumption of the SQL statement is not high and does not change significantly as the business and data grows. It does not consume excessive CPU resources or take too much I/O time. For example, when you execute a statement to query a table based on the primary key, the statement consumes roughly the same resource to query one row or ten million rows. In this case, it can be easy to scale out the database to support the traffic increase based on proper estimation. However, if the business involves an excessive number of resource-consuming complex statements, the business can be impacted by heaping SQL statements and concurrent instances merely because of occasional or frontend traffic fluctuations of the network or the database.
In a word, complex SQL statements can cause significant performance fluctuations in a cluster, resulting in low concurrency and uncontrollable business.