[DB-SIG] Python and static SQL

Mon, 26 Feb 2001 11:01:57 -0700

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C0A01E.35730F90
Content-Type: text/plain;
	charset="iso-8859-1"

I have written code against the OCI (Oracle Call Interface) a fair amount
and also written a Python DB-API 2.0 compliant module for Oracle (more on
that later when I have a better description and a Web page) and I think you
are being confused by what Oracle is telling you.

Any piece of SQL that is executed _OUTSIDE_ the Oracle database (ie by some
client or other) is executed by the OCI which requires a SQL string and bind
variables. SQL that is executed from within the database itself (ie views
and stored procedures) are optimized by Oracle itself. Dynamic SQL within
Oracle is slower than views and stored procedures (although the difference
has become quite slight in recent versions of Oracle) but only because at
that point the Oracle database must execute OCI calls rather than whatever
it happens to do internally.

So I believe the only performance improvement you are going to notice is
that if the strings passed to the database are known at run-time, you do not
have to incur the overhead of generating those strings in the first place.
Depending on what the database is actually doing this can be significant or
not.

Anthony

-----Original Message-----
From: Magnus Lycka [mailto:magnus@thinkware.se]
Sent: Monday, February 26, 2001 10:41 AM
To: db-sig@python.org
Subject: [DB-SIG] Python and static SQL

This ODBC discussion made me think of something completely
different...

I haven't actually done a lot of measurements, but I've
often read that databases like Oracle and DB2 have much,
much better performance using static SQL, than they have
with dynamic SQL.

Is anyone using static SQL "with" Python?

What I mean is: Has anyone here built systems where they
do their database calls in C or C++ with embedded SQL,
basically placing their database IO in a C or C++ module?

If the performance claims are true this should be able to
boost performance a lot for standard calls. Lately I've
mainly been using SQL databases from Object-Oriented programs,
and there I think 90% of the accesses are simple CRUD (Create,
Retrieve, Update, Delete) operations, and it seems to me
that it would be fairly simple to write a little program that
would auto-generate the C or C++ code needed to make these
CRUD operations.

/Magnus

------_=_NextPart_001_01C0A01E.35730F90
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2653.12">
<TITLE>RE: [DB-SIG] Python and static SQL</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>I have written code against the OCI (Oracle Call =
Interface) a fair amount and also written a Python DB-API 2.0 compliant =
module for Oracle (more on that later when I have a better description =
and a Web page) and I think you are being confused by what Oracle is =
telling you.</FONT></P>

Any piece of SQL that is executed _OUTSIDE_ the =
Oracle database (ie by some client or other) is executed by the OCI =
which requires a SQL string and bind variables. SQL that is executed =
from within the database itself (ie views and stored procedures) are =
optimized by Oracle itself. Dynamic SQL within Oracle is slower than =
views and stored procedures (although the difference has become quite =
slight in recent versions of Oracle) but only because at that point the =
Oracle database must execute OCI calls rather than whatever it happens =
to do internally.

So I believe the only performance improvement you are =
going to notice is that if the strings passed to the database are known =
at run-time, you do not have to incur the overhead of generating those =
strings in the first place. Depending on what the database is actually =
doing this can be significant or not.

<P><FONT SIZE=3D2>Anthony</FONT>
</P>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Magnus Lycka [<A =
HREF=3D"mailto:magnus@thinkware.se">mailto:magnus@thinkware.se</A>]</FON=
T>
<BR><FONT SIZE=3D2>Sent: Monday, February 26, 2001 10:41 AM</FONT>
<BR><FONT SIZE=3D2>To: db-sig@python.org</FONT>
<BR><FONT SIZE=3D2>Subject: [DB-SIG] Python and static SQL</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>This ODBC discussion made me think of something =
completely</FONT>
<BR><FONT SIZE=3D2>different...</FONT>
</P>

<P><FONT SIZE=3D2>I haven't actually done a lot of measurements, but =
I've</FONT>
<BR><FONT SIZE=3D2>often read that databases like Oracle and DB2 have =
much,</FONT>
<BR><FONT SIZE=3D2>much better performance using static SQL, than they =
have</FONT>
<BR><FONT SIZE=3D2>with dynamic SQL.</FONT>
</P>

<P><FONT SIZE=3D2>Is anyone using static SQL &quot;with&quot; =
Python?</FONT>
</P>

<P><FONT SIZE=3D2>What I mean is: Has anyone here built systems where =
they</FONT>
<BR><FONT SIZE=3D2>do their database calls in C or C++ with embedded =
SQL,</FONT>
<BR><FONT SIZE=3D2>basically placing their database IO in a C or C++ =
module?</FONT>
</P>

If the performance claims are true this should be =
able to
 boost performance a lot for standard calls. Lately =
I've
 mainly been using SQL databases from Object-Oriented =
programs,
 and there I think 90% of the accesses are simple =
CRUD (Create,
 Retrieve, Update, Delete) operations, and it seems =
to me
 that it would be fairly simple to write a little =
program that
 would auto-generate the C or C++ code needed to make =
these
 CRUD operations.

<P><FONT SIZE=3D2>/Magnus</FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C0A01E.35730F90--