Re: Powerbuilder problems?

mike_at_steven.coyote.trw.com (Mike Curtin) writes:

>I would say that if 200 users are using the exact same SQL statements,
>those statements should be in a stored procedure, which is completely
>maintained and executed on the server, so any caching or other benefits
>should be realized without any work on the client side.
 

>This "binding" term has come up in several conversations I have had
>recently, and I still don't understand what is meant by it. Could you
>please post or e-mail me a definition, and perhaps a couple examples of
>SQL with and without binding requirements? Thanks.

Binding: (examples)

select name,address,sal from emp where deptno = :var1 and sal < :var2;

insert into emp (name,address) values (:var1, :var2);

The :var1 and :var2 are "bind variables" meaning the client will bind a value each time they are executed. Since Oracle7 stores the execution plan (parsing is done once and stored in memory on the server), a application can Open a Cursor(s), and execute or fetch from it without causing a reparse (even as the app. changes :var1's value each time).

Powerbuilder (in many situations) does not declare a cursor with the above "bind variables", instead, each user has his/her SQL statement executed with the values "hard coded":

select name, address,sal from emp where deptno = 10 and sal < 1212;

This means each user for each statement has his/her SQL reparsed (even when doing a bunch of inserts).

If you think about why you want to use a stored procedure you can list the following (some have have more or less but here is my list):

1. Avoid reparsing -> save CPU cycles
2. Avoid reoptimizing -> save CPU cycles
3. Reduce network use -> save LAN traffic

Item 3 has nothing to do with selecting data from Oracle because all users can take advantage of shared SQL in the server and Oracle sends back arrays of data (multiple rows/packet) to the client.

This can be compared to Sybase where they want you to use a stored query because they have no "shared SQL" and you can only do #1 and #2 above with a stored query/procedure.

It is recommended to use stored procedures with Oracle7 when you want to take advantage of #3 above for insert, update, delete operations where the "transaction" being performed requires multiple SQL statements and IF-THEN ... type logic as you validate complex rules. In this case, letting that all happen in the server will reduce SQL calls and results being returned to the client (network traffic).

Also, many ad-hoc query tools generate plain old SQL, not stored procedure calls. To allow these 3rd party tools (or tools like the Oracle Browser) to take advantage of #1 and #2, the developers of these tools use cursors with bind variables. If you do not have this dynamic SQL support in the server, then each SQL statement sent by an ad-hoc tool will need to be parsed, optimized, etc. It is of my opinion that this is one of the reasons people buy Oracle7 (allow OLTP and DSS in the same database and not kill the CPU doing parsing/optim., instead, use it for real work).

-=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()} David Natelson Oracle Corporation dnatelso_at_oracle.com -=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}   Received on Tue May 24 1994 - 12:32:03 CEST