Що повертати – масиви даних або результуючі безлічі?, Інші СУБД, Бази даних, статті

Том!

Я займаюся створенням API, що відкриває нашу базу даних для доступу будь-якому клієнту. Ми нарешті починаємо використовувати базу даних як базу даних, а не просто як “бітопріемнік” з обмеженим доступом.


Щоб надати дані цим клієнтам, які використовують різні мови програмування, ми почали обговорювати, чи буде краще з точки зору гнучкості і / або продуктивності передавати результуючі безлічі і дозволяти клієнту вибирати дані в масиви для видачі, або виконувати множинну вибірку в pl / sql і повертати заповнені масиви даних. Повернення результуючих множин здається привабливішим, оскільки клієнт зможе вибирати рядки і заповнювати свої масиви даних пакетами будь-якого відповідного розміру. Але при поверненні масивів даних, схоже, кожному клієнту залишиться менше роботи.


Я бачив, що ви використовуєте в своїх прикладах обидва методи. Чи немає у вас переваги, сформованого на основі досвіду?


Відповідь Тома Кайта


Ось цитата з моєї книги “Effective Oracle by Design” (Сказати, що в мене є перевага – це нічого не сказати 🙂



Повернення даних


Ходять чутки, що Oracle не дозволяє повертати результуючі безлічі з збережених процедур. Схоже, що будь-який програміст, який звик до SQL Server і використовує Oracle, приходив до цього помилці. Мова PL / SQL може повертати результуючі безлічі, і зробити це ніяк не складніше (і чи не простіше), ніж в інших СУБД. Це просто робиться інакше. В Oracle треба використовувати курсорну змінну (ref cursor – покажчик на курсор). Ця можливість була доступна, починаючи з версії СУБД Oracle 7.2 (яка була випущена в 1995 році).


Переваги курсорних змінних


У загальному випадку, використання курсорною змінної – оптимальний метод повернення результатів клієнтам. Так можна стверджувати з наступних причин:



  • Простота програмування. В будь-якій мові можна працювати з результуючим безліччю – за допомогою курсору.
  • Гнучкість. Клієнтський додаток може вирішувати, скільки рядків вибирати за раз. Замість посилки 10000 елементів масиву, ви посилаєте результуюче безліч, з якого клієнт може вибирати по 10 рядків за раз.
  • Продуктивність. Не доведеться вибирати дані за допомогою курсору в PL / SQL, заповнювати ними масив (виділяючи пам’ять на сервері), і посилати цей масив клієнту (який теж виділяє під нього пам’ять), змушуючи його чекати завершення обробки останнього рядка, перш ніж він отримає першу. Замість цього, курсорних мінлива дозволить вам повернути дані клієнта негайно, без всіх описаних вище додаткових дій.

Отже, з причин, аналогічним обмеження розміру масиву при множинної вибірці, для повернення результуючих множин клієнтським програмам має сенс використовувати курсорні змінні, а не табличні типи PL / SQL table або набори SQL.


Використовуйте курсорні змінні для повернення результуючих множин


Як приклад вдалого використання курсорних змінних, давайте створимо на мові Java клієнта, який вибирає дані з копії подання ALL_OBJECTS. Ми реалізуємо його за допомогою табличних типів PL / SQL і за допомогою курсорних змінних.


Нижче представлена ​​специфікація пакета для нашого прикладу. Він включає процедуру INDEX_BY, Що приймає ім’я власника (OWNER) І повертає в якості результату три стовпці.

scott@ORA920> create table t
2 as
3 select * from all_objects;
Table created.
scott@ORA920> create or replace package demo_pkg
2 as
3 type varchar2_array is table of varchar2(30)
4 index by binary_integer;
5
6 type rc is ref cursor;
7
8 procedure index_by( p_owner in varchar2,
9 p_object_name out varchar2_array,
10 p_object_type out varchar2_array,
11 p_timestamp out varchar2_array );
12 procedure ref_cursor( p_owner in varchar2,
13 p_cursor in out rc );
14 end;
15 /
Package created.

Вже можна зрозуміти, наскільки громіздким швидко стає цей підхід для великих за структурою (“широких”) результуючих множин. Інтерфейс на базі курсорною змінної, з іншого боку, просто приймає значення OWNER для пошуку та повертає одну курсорну змінну, яка може вибирати скільки завгодно стовпців.


Тепер давайте розглянемо реалізацію цих процедур в тілі пакету.

scott@ORA920> create or replace package body demo_pkg
2 as
3
4 procedure index_by( p_owner in varchar2,
5 p_object_name out varchar2_array,
6 p_object_type out varchar2_array,
7 p_timestamp out varchar2_array )
8 is
9 begin
10 select object_name, object_type, timestamp
11 bulk collect into
12 p_object_name, p_object_type, p_timestamp
13 from t
14 where owner = p_owner;
15 end;
16
17 procedure ref_cursor( p_owner in varchar2,
18 p_cursor in out rc )
19 is
20 begin
21 open p_cursor for
22 select object_name, object_type, timestamp
23 from t
24 where owner = p_owner;
25 end;
26 end;
27 /
Package body created.

В даному випадку, процедура INDEX_BY використовує конструкцію BULK COLLECT для вибірки всіх даних. Процедура REF_CURSOR просто виконує оператор OPEN.


Java-клієнт для процедури INDEX_BY може мати наступний вигляд. Ми почнемо з дуже простої функції таймера, яка видаватиме в мілісекундах час, що минув між викликами.

import java.sql.*;
import java.util.Date;
import oracle.jdbc.driver.*;
import oracle.sql.*;
class indexby
{
static long start = new Date().getTime();
public static void showElapsed( String msg )
{
long end = new Date().getTime();

System.out.println( msg + ” ” + (end – start) + ” ms”);
start = end;
}


При кожному виклику цієї функції вона буде видавати час, що минув з моменту останнього її виклику, і запам’ятовувати цей момент часу в якості нового часу останнього дзвінка.


Тепер давайте розглянемо функцію main. Ми починаємо з підключення до сервера Oracle.

public static void main(String args[])throws Exception
{
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
Connection conn=DriverManager.getConnection
(“jdbc:oracle:oci8:@ora920.us.oracle.com”,”scott”, “tiger”);
showElapsed( “Connected, going to prepare” );

Потім ми готуємо (prepare) виклик процедури INDEX_BY пакета DEMO_PKG. Ми зв’яжемо значення SYS з першим параметром, потім визначимо по черзі вихідні PL / SQL-таблиці.

    OracleCallableStatement cstmt =
(OracleCallableStatement)conn.prepareCall
( “begin demo_pkg.index_by(?,?,?,?); end;” );
showElapsed( “Prepared, going to bind” );
int maxl = 15000;
int elemSqlType = OracleTypes.VARCHAR;
int elemMaxLen = 30;
cstmt.setString( 1, “SYS” );
cstmt.registerIndexTableOutParameter
( 2, maxl, elemSqlType, elemMaxLen );
cstmt.registerIndexTableOutParameter
( 3, maxl, elemSqlType, elemMaxLen );
cstmt.registerIndexTableOutParameter
( 4, maxl, elemSqlType, elemMaxLen );

Зверніть увагу, що ми встановлюємо три змінних: maxl, Задану максимальну кількість “строк”, з якими ми готові працювати; elemSqlType, Тип даних кожного з вихідних масивів; і elemMaxLen, Максимальний передбачуваний розмір елемента кожного масиву.


Потім ми виконуємо оператор. Після виконання оператора ми вибираємо три масиви даних, що представляють наше результуюче безліч.

    showElapsed( “Bound, going to execute” );
cstmt.execute();
Datum[] object_name = cstmt.getOraclePlsqlIndexTable(2);
Datum[] object_type = cstmt.getOraclePlsqlIndexTable(3);
Datum[] timestamp = cstmt.getOraclePlsqlIndexTable(4);

Потім ми просто вибираємо значення кожного масиву по черзі, щоб продемонструвати, скільки часу буде потрібно на прохід від першого до останнього рядка цього результуючого безлічі.

    showElapsed( “First Row “+object_name.length );
String data;
int i;
for( i = 0; i < object_name.length; i++ )
{
data = object_name[i].stringValue();
data = object_type[i].stringValue();
data = timestamp[i].stringValue();
}
showElapsed( “Last Row “+i );
}
}

При першому прогоні цього прикладу я використав 10000 замість 15000 в якості значення maxl (Максимальної довжини масиву). Ось що я отримав:

$ java indexby
java.sql.SQLException: ORA-06513: PL/SQL:
index for PL/SQL table out of range for host language array
ORA-06512: at line 1

Я не вгадав. Клієнт поставив дуже маленький розмір масивів, так що замість даних він отримав повідомлення про помилку. При використанні цього підходу клієнт повинен знати заздалегідь максимальну кількість рядків і максимальний розмір кожного стовпця. Такої інформації в момент компіляції у вас може не бути.


Тепер можна розглянути клас REFCUR. Перша частина його коду ідентична (за винятком імені класу) коду класу INDEXBY, До рядка коду, наступного відразу за підключенням. Ми продовжимо код з цього рядка, що встановлює розмір масиву для попередньої вибірки рядків. За замовчуванням в JDBC використовується 10, але я зазвичай використовую 100.

    showElapsed( “Connected, going to prepare” );
((OracleConnection)conn).setDefaultRowPrefetch(100);

Тепер ми підготуємо і зв’яжемо оператор так само, як це було зроблено для PL / SQL-таблиць, використовуючи курсорну змінну замість проіндексованих таблиць.

    OracleCallableStatement cstmt =
(OracleCallableStatement)conn.prepareCall
( “begin demo_pkg.ref_cursor(?,?); end;” );
showElapsed( “Prepared, going to bind” );
cstmt.setString( 1, “SYS” );
cstmt.registerOutParameter(2,OracleTypes.CURSOR);

Потім ми виконаємо оператор і отримаємо результуюче безліч. Ми знову видаємо час, необхідний для отримання першої і останньої рядки, проходячи по всіх посередником рядках.

    showElapsed( “Bound, going to execute” );
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(2);
if ( rset.next() )
showElapsed(“First Row”);
String data;
int i;
for( i = 1; rset.next(); i++ )
{
data = rset.getString(1);
data = rset.getString(2);
data = rset.getString(3);
}
showElapsed(“Last Row “+i );

У наступній таблиці представлена ​​зведення результатів виконання цих двох версій коду.























Час очікування  INDEXBY  REFCUR  Різниця 
Час до отримання першого рядка  825 ms 25 ms (800) ms
Час до отримання останнього рядка  1375 ms 860 ms (515) ms
Час вибірки всіх рядків  2200 ms 885 ms (1315) ms

Таблиця 1. Порівняння методів повернення результатів за допомогою PL / SQL-таблиць і курсорною змінної


Давайте підемо на крок далі, і додамо ще одну таблицю, в якій буде представлена ​​статистична інформація про використання пам’яті PGA і UGA на сервері.

scott@ORA920> create table stats ( which varchar2(30), uga number, pga number );
Table created.

Додамо наступний SQL-оператор після останнього дзвінка showElapsed в кожній Java-функції (замінюючи indexby словом ref_cursor в іншій функції):

Statement stmt = conn.createStatement();
stmt.execute
( “insert into stats “+
“select “indexby”, “+
“max(decode(a.name,”session uga memory max”,b.value,null)) uga, “+
“max(decode(a.name,”session pga memory max”,b.value,null)) pga “+
“from v$statname a, v$mystat b “+
“where a.name like “%memory%max” “+
“and a.statistic# = b.statistic# ” );

Ми побачимо, що підхід INDEXBY використовує набагато більше пам’яті в порівнянні з підходом на базі курсорною змінної.

scott@ORA920> select which, trunc(avg(uga)), trunc(avg(pga)), count(*)
2 from stats
3 group by which
4 /
WHICH TRUNC(AVG(UGA)) TRUNC(AVG(PGA)) COUNT(*)
————— ————— ————— ———-
indexby 76988 4266132 9
ref_cursor 76988 244793 9

Це показує, що на дев’яти прогонах підхід INDEXBY зажадав 4,2 Мбайта оперативної пам’яті на сервері. Похід на базі курсорною змінної зажадав для вирішення тієї ж завдання всього лише 244 Кбайта оперативної пам’яті.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*