Re: Powerbuilder problems?

dbmoore_at_us.oracle.com (Dennis Moore) writes:

>In article <CpwJMG.AtI_at_stortek.com> v045101_at_procyon.stortek.com (Sean Stasica) writes:
>>deflenniken_at_biivax.dp.beckman.com wrote:
 

>[stuff deleted]
 

>>> 2. ORACLE binds variables in the Database server;
>>> Powerbuilder binds them on the client.
>>
>>If this was the cause of slowdown with concurrent users, wouldn't it be just
>>as evident with one user? The benefit of client/server is that most of the
>>work is done at the client.
 

>Not right. Repeated execution of a similar statement with/without bind
>variables shows the performance advantages to using bind variables (done on
>the client, not the server, to avoid work being done on the server) even
>when running as the only user on the server -- more so with multiple users
>running different applications, and most when multiple users are running
>the same application.
 

>When a SQL statement is submitted to the server, it needs to be parsed,
>optimized, bound (qualifications like WHERE EMPID=20), and then executed.
>By using bind variables, you can avoid the first two steps (VERY expensive
>from a performance standpoint -- often the vast majority of the CPU cycles
>and perceived/user time) most of the time (as long as the statement exists
>in the cache. If you don't use bind variables (in other words, if you
>submit the statement with literal values instead of bind variable "place
>holders"), if you scrolled to the next row and reexecuted the query, you
>must go through the entire process again -- wasting time and wasting
>server resources. The more times a single user submits similar queries,
>the more evident and costly this will become; when you have 200 users
>executing the same order entry/customer service application, the difference
>is enormous -- not only saving all that work, but also leaving statements
>in cache longer and avoiding swapping effects!

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.

--MDC Received on Tue May 17 1994 - 18:35:45 CEST