Re: Dynamic SQL vs normal SQL

In article <44i4db$cfc_at_pcnet2.pcnet.net> Paul Baumgartel <paulb_at_pcnet.com> writes:
>Path: inforamp.net!uunet.ca!news.uunet.ca!torn!howland.reston.ans.net!news.sprintlink.net!pcnet.com!usenet
>From: Paul Baumgartel <paulb_at_pcnet.com>
>Newsgroups: comp.databases.oracle
>Subject: Re: Dynamic SQL vs normal SQL
>Date: 30 Sep 1995 00:52:59 GMT
>Organization: PCNet -- Public Access Internet in Connecticut!
>Lines: 5
>Message-ID: <44i4db$cfc_at_pcnet2.pcnet.net>
>References: <448ugd$9m6_at_sdaw04.seinf.abb.se>
>NNTP-Posting-Host: ts1-pt9.pcnet.com
>Mime-Version: 1.0
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>X-Mailer: Mozilla 1.22 (Windows; I; 16bit)

>All SQL is processed the same once it reaches the kernel, whether it was built on
>the fly (dynamic) or not (static). The only performance difference as far as the
>DBMS is concerned would be that dynamic SQL would be less likely to be found in the
>SQL cache and therefore your system may spend more time parsing.

Isn't all of the parsing AND optimisation done just the once, resulting in the re-execution of the cursor with the new where clause variables being much faster? That is the basic architecture of SAP. All the cursors stay in memory (once a table has been accessed). The problem with SAP when it does that is that the cursors all stay in memory (or swaped out to disk) until the SAP instance is shut down (a side affect of the single user slave DB server).

J. Received on Sat Sep 30 1995 - 00:00:00 CET