Re: SQL*Net performance - why this way?

From: Kevin Mallory <kmallory_at_oracle.com>
Date: 12 Sep 92 00:00:50 GMT
Message-ID: <KMALLORY.92Sep11160050_at_hqsun4.oracle.com>

I posted the following abstract from the 3.0.16.10 SQL*Forms release notes which describe the changes to forms. These changes are not RDBMS version specific.

SQL*Forms 3.0.16.10 Performance Enhancements

---

SQL Statement Execution

             SQL*Forms now uses a different interface to the RDBMS
        that allows the bundling of bind variable addresses and WHERE
        clause conditions. This substantially reduces the number of
        client/server interactions required to execute some SQL
        statements.

             The table below details the results of the measurement
        of the number of packets exchanged between client and server,
        for a number of different types of SQL statements.

             Note Statements 1 and 2 demonstrate NO improvement in
        performance as there is only 1 bind variable and/or one WHERE
        clause condition, and thus no bundling of RDBMS calls takes
        place.
  
      SQL            Number of Packets Exchanged
        Statement                                      Percent
        Number         3.0.16.7          3.0.16.10     Improvement

          1                 14               14            0%
          2                 10               10            0%
          3                 28               14           50%
          4                 40               14           65%

        SQL Statements:

        1) select dname into :dname frm drept where deptno = :deptno;

        2) select ename into :ename from emp;

        3) select empno, ename, job, mgr, hiredate, sal, comm, deptno
           into  :empno, :ename, :job, :mgr, :hiredate, :sal, :comm, :deptno
           from emp
           where empno = :empno;

        4) select empno, ename, job, mgr, hiredate, sal, comm, deptno
           into  :empno, :ename, :job, :mgr, :hiredate, :sal, :comm,
     :deptno
           from emp
           where empno = :empno and ename= :ename and
                 job= :job and MGR= :mgr and hiredate= :hiredate
                 and SAL= :sal and deptno= :deptno;

       When Will SQL*Forms Use UPIALL?

             A significant amount of discussion has arisen about the
        use of UPIALL in Oracle Tools.  This section describes the
        results of our study of the performance of UPIALL vs. the
        current methods being used.

            The execution of SQL statements is divided into a 5
        distinct operations: parse, bind, define, execute, and fetch.
        UPIALL is a programmatic interface designed to minimize the
        number of client/server interactions by "bundling" groups of
        these calls into one client/server interaction. That is,
        rather  than one client server interaction for each
        operation, it is possible to bundle them into two or even one
        large client/server interaction, thus reducing the number of
        packets exchanged between client and server.

             SQL*Forms development has studied in great depth the
        performance of SQL statements in SQL*Forms V3.0.  After
        careful study of the results we decided NOT to implement
        UPIALL support in SQL*Forms for several reasons:

             1) Prior to RDBMS 6.0.32.4 there were bugs in the RDBMS
             that precluded the use of UPIALL in SQL*Forms.

             2) A substantial performance improvement (reduction in
             packets) is achieved by using the bundled bind varible
             and where clause define RDBMS interface.

             3) The minimum performance improvement that UPIALL
             provides over and above (2) above does not warrant a
             strict RDBMS version requirement by SQL*Forms.

             4) RDBMS V7 provides changes to the UPI interface, which
             further reduces client/server interaction which will
             require no further changes in SQL*Forms code.

--

---

Kevin Mallory Oracle Corporation - Redwood Shores, California SQL*Forms Development
I don't speak for Oracle, nor they for me. Remember: If you can find someplace you laik better 'n' Tuna... **MOVE**! Received on Sat Sep 12 1992 - 02:00:50 CEST