-----------------------------
Performance Tuning
-----------------------------
Since stored procedures and stored functions are written by users, performance tuning may be necessary.
This chapter provides various guides to optimize the performance of stored procedures and stored functions.
.. contents::
SQL Query Optimization
==============================
The most common performance overhead in stored procedures and stored functions can occur from inefficient SQL statement execution.
You should sufficiently tune the SQL statements used in internal routines to prevent the following inefficiencies:
* **Table Full Scan**: Performance can degrade if the entire table is scanned without using an index.
* **Communication Overhead**: To reduce communication overhead between the CUBRID database server and the CUBRID PL server, you should write queries to return the minimum number of records.
* **Inefficient Query Calls within Loops**: When using loops within stored procedures and stored functions, it is recommended to fetch data with a single query instead of calling multiple queries within the loop.
To tune inefficient queries used in stored procedures and stored functions, refer to the :doc:`/sql/tuning` document for query optimization.
Optimization of Stored Function Calls in Queries
==================================================
Unnecessary repeated calls to stored functions executed in queries can degrade performance. Therefore, consider the following methods to optimize stored function calls:
* **Minimize Unnecessary Repeated Calls**: Reducing the number of calls to stored functions is the best way to improve performance.
* Use indexes to reduce the number of records for which the stored function is called.
* Group duplicate data to handle it with a single call for avoiding repeated calls for the same arguments.
* If the function logic is deterministic, use :ref:`pl-deterministic`\ to improve performance by caching the results of correlated subqueries.
* **Minimize the Size of Function Arguments and Return Values**: Design the function to return only the necessary values, avoiding the return of unnecessarily large data.
.. _pl-use-builtin:
Use Built-in Functions
===============================
Built-in functions provided by CUBRID (:doc:`/sql/function/index`) are optimized to work efficiently with CUBRID's query execution behavior.
On the other hand, user-written stored functions are **black boxes** whose internal routines are unknown, and thus may perform worse than built-in functions.
Therefore, it is recommended to use built-in functions rather than implementing stored functions that can be achieved with simple combinations of built-in functions.
Below is an example of a stored function that performs the same function as the built-in CONCAT function.
Using the built-in function concat() has performance advantages over the user-defined stored function my_concat().
.. code-block:: sql
CREATE OR REPLACE FUNCTION my_concat (a STRING, b STRING) RETURN STRING AS
BEGIN
RETURN a || b;
END;
SELECT COUNT(*) FROM (SELECT /*+ NO_MERGE */ concat (name, event) FROM athlete);
SELECT COUNT(*) FROM (SELECT /*+ NO_MERGE */ my_concat (name, event) FROM athlete);
::
-- Using concat function
count(*)
======================
6677
1 row selected. (0.019853 sec) Committed. (0.000000 sec)
-- Using my_concat function
count(*)
======================
6677
1 row selected. (0.302333 sec) Committed. (0.000000 sec)
.. _pl-deterministic:
Use Deterministic Functions
==============================
A deterministic function is a function that always returns the same result for the same arguments during a transaction. If a stored function is deterministic, its results can be reused to improve performance.
* To make a stored function deterministic, you can specify the **DETERMINISTIC** option in the **CREATE FUNCTION** statement. For more details, refer to :ref:`create-function`.
* When the **DETERMINISTIC** attribute is specified, the stored function can be used for correlated subquery result caching optimization and will always return the same result for the same arguments. For more details on correlated subquery cache behavior, refer to :ref:`correlated-subquery-cache`.
Below is an example of a stored function created as a deterministic function with **DETERMINISTIC**. This example shows the process of optimizing performance by caching results when using correlated subqueries.
.. code-block:: sql
CREATE TABLE dummy_tbl (col1 INTEGER);
INSERT INTO dummy_tbl VALUES (1), (2), (1), (2);
CREATE OR REPLACE FUNCTION pl_csql_not_deterministic (n INTEGER) RETURN INTEGER AS
BEGIN
return n + 1;
END;
CREATE OR REPLACE FUNCTION pl_csql_deterministic (n INTEGER) RETURN INTEGER DETERMINISTIC AS
BEGIN
return n + 1;
END;
SELECT sp_name, owner, sp_type, is_deterministic from db_stored_procedure;
::
sp_name owner sp_type is_deterministic
========================================================================================
'pl_csql_not_deterministic' 'DBA' 'FUNCTION' 'NO'
'pl_csql_deterministic' 'DBA' 'FUNCTION' 'YES'
In the above example, the pl_csql_not_deterministic function does not use query caching in correlated subqueries because it is **NOT DETERMINISTIC**.
On the other hand, the pl_csql_deterministic function uses query caching in correlated subqueries because it is specified with the **DETERMINISTIC** keyword, optimizing performance.
.. code-block:: sql
;trace on
SELECT (SELECT pl_csql_not_deterministic (t1.col1) FROM dual) AS results FROM dummy_tbl t1;
::
results
=============
2
3
2
3
=== Auto Trace ===
...
Trace Statistics:
SELECT (time: 4, fetch: 11, fetch_time: 0, ioread: 0)
FUNC (time: 4, fetch: 2, ioread: 0, calls: 4)
SCAN (table: dba.dummy_tbl), (heap time: 0, fetch: 5, ioread: 0, readrows: 4, rows: 4)
SUBQUERY (correlated)
SELECT (time: 4, fetch: 6, fetch_time: 0, ioread: 0)
FUNC (time: 4, fetch: 2, ioread: 0, calls: 4)
SCAN (table: dual), (heap time: 0, fetch: 4, ioread: 0, readrows: 4, rows: 4)
The pl_csql_not_deterministic function does not cache the results of correlated subqueries because it is **NOT DETERMINISTIC**.
.. code-block:: sql
;trace on
SELECT (SELECT pl_csql_deterministic (t1.col1) FROM dual) AS results FROM dummy_tbl t1;
::
results
=============
2
3
2
3
=== Auto Trace ===
...
Trace Statistics:
SELECT (time: 2, fetch: 9, fetch_time: 0, ioread: 0)
FUNC (time: 2, fetch: 2, ioread: 0, calls: 2)
SCAN (table: dba.dummy_tbl), (heap time: 0, fetch: 5, ioread: 0, readrows: 4, rows: 4)
SUBQUERY (correlated)
SELECT (time: 2, fetch: 4, fetch_time: 0, ioread: 0)
FUNC (time: 2, fetch: 2, ioread: 0, calls: 2)
SCAN (table: dual), (heap time: 0, fetch: 2, ioread: 0, readrows: 2, rows: 2)
SUBQUERY_CACHE (hit: 2, miss: 2, size: 150808, status: enabled)
In the trace results of the pl_csql_deterministic function, the **SUBQUERY_CACHE** item is displayed (hit: 2, miss: 2, size: 150808, status: enabled), and the number of records read (**readrows**) in the **SCAN (table: dual)** at the top is reduced compared to the **NOT DETERMINISTIC** example.
.. warning::
* The **DETERMINISTIC** attribute is not supported in stored procedures.
* If a function that returns non-deterministic results is used the **DETERMINISTIC** option, it may not return the expected results.
.. code-block:: sql
CREATE TABLE test_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO test_table (name) VALUES
('Alice'),
('Bob'),
('Charlie'),
('Alice'),
('Bob');
CREATE SERIAL my_serial;
CREATE OR REPLACE FUNCTION cnt_name (name VARCHAR) RETURN VARCHAR DETERMINISTIC AS BEGIN RETURN name || my_serial.NEXT_VALUE; END;
SELECT
id,
name,
(SELECT cnt_name(name) FROM DUAL) AS result
FROM test_table;
::
id name result
=========================================================
1 'Alice' 'Alice1'
2 'Bob' 'Bob2'
3 'Charlie' 'Charlie3'
4 'Alice' 'Alice1'
5 'Bob' 'Bob2
In the above example, the my_serial.NEXT_VALUE inside the cnt_name function returns non-deterministic results, so it returns unexpected results due to the correlated subquery cache.
It is recommended to specify the **DETERMINISTIC** option considering the implementation of the stored function.
Day widened into its first perfection as we moved down the highroad toward a near fork whose right was to lead Harry and his solemn cortége southward, while the left should be our eastward course. Camille and I rode horseback, side by side, with no one near enough to smile at my sentimental laudations of the morning's splendors, or at her for repaying my eloquence with looks so full of tender worship, personal acceptance and self-bestowal, that to tell of them here would make as poor a show as to lift a sea-flower out of the sea; they call for piccolo notes and I am no musician. The little man elected to have a cab. When Bow Street was reached Prout had the satisfaction of finding that all his birds had been netted. He received the warm congratulations of his inspector modestly. Caloric or air engines. I did not feel very comfortable after what had happened to those soldiers who lost their lives so cruelly sudden, or in any case had been seriously wounded, while the officers took little notice of them. But it was desirable to behave as discreetly as possible, and so to get a permit to Maastricht. "Louvain, “It will check up, that way, too,” smiled Larry. "Me run away," thought Shorty, as they walked along. "Hosses couldn't drag me away. I only hope that house is 10 miles off." Reuben began to take off his coat—young Realf drew back almost in disgust. "Well, would Robert have stolen money, or Albert disgraced your name, to get free, if you and your farm hadn't made them slaves? If you hadn't been a heartless slave-driver would George have died the other night alone on the Moor?—or would Richard have taken advantage of a neighbour's charity to escape from you? Don't you see that your ambition has driven you to make slaves of your children?" "D?an't tell me," said Coalbran in the bar, "as it wurn't his fault. Foot-and-mouth can't just drop from heaven. He must have bought some furriners, and they've carried it wud 'em, surelye." But meantime a strange restlessness consumed her, tinctured by a horrible boldness. There were moments when she no longer was afraid of Handshut, when she felt herself impelled to seek him out, and make the most of the short time they had together. There could be no danger, for he was going so soon ... so few more words, so few more glances.... Thus her mind worked. "What mean you, woman?" quickly returned De Boteler; "do you accuse the keeper of my chase as having plotted against your son, or whom do you suspect?" Her face was shrivelled and yellow, and the dark full eyes that now, as it were, stood forth from the sunken cheeks, looked with a strange brightness on the scene, and seemed well adapted to stamp the character of witch on so withered a form. And perhaps there were few of those entirely uninterested in the matter who now gazed upon her, who would not have sworn that she merited the stake. "Thou art set over the people, and to the Lord's anointed I come to seek for justice." HoME欧美一级毛片免费高清日韩
ENTER NUMBET 0018www.lfszj.com.cn
xhkt.com.cn
www.hnfsk.com.cn
www.cnjinding.com.cn
sja6nys0.com.cn
www.hnfangshui.com.cn
www.boaisuxin.net.cn
transg.com.cn
anynode.com.cn
szwdkj.com.cn