[DB-SIG] Python and static SQL

Dave Cole djc@object-craft.com.au
27 Feb 2001 20:42:37 +1100

>>>>> "Magnus" == Magnus Lycka <magnus@thinkware.se> writes:

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

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

How do stored procedures stack up against static SQL?

I am in the process of re-implementing my sorry excuse for a Sybase
module and have been reading that the only penalty that dynamic SQL
had over stored procedures is the initial compile stage.

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

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

I agree that it should be pretty simple.

How much faster than

        insert blah values (?,?,?,?,?)

is it going to be when the SQL is compiled once, then you just stream
values for the placeholders?

Are you able to tell us the measurement results?

- Dave