Re: JDBC and Oracle

Matthias Kampmann <matthias.kampmann_at_uni-bielefeld.de> said:
>I am on a project with Oracle and JDBC. A Servlet should process about
>serveral hundret SQL-Queries per sec with Oracle DB, with small data
>sets as return.

As others have said, the amount you present is high.

If possible, write a small program to connect to the database, and time f.ex. 1000 or 10000 executions of 'select * from sys.dual', and disconnect. As far as I know, that is the smallest query you can run in Oracle; there's nothing to fetch from disk. Oh, remember to include the network delays: the program should be run on the machine where your final program is intended to be run.

With that data you should at least get an upper limit on the number of queries/sec you can run on your intended target database.

As for using stored procedures: if you need to get the results into the web-world, you're going to need some web-to-database translation anyway (servlets+JDBC is one). For simple queries PL/SQL isn't going to help, as Oracle will cache your simple queries just as well.

Btw, make sure you use prepared statements for your queries, if the queries include data items that vary between queries. If you do identical "full" queries with several different data items, the DBAs will not like you - you'll trash the SQL cache.

(so NOT 'select foo from bar where zap=1;', 'select foo from bar where zap=2;', etc, but just prepare once 'select foo from bar where zap=:1', then just bind your desired value when performing a new query)

-- 
Wolf  a.k.a.  Juha Laiho     Espoo, Finland
(GC 3.0) GIT d- s+: a C++ ULSH++++$ P++_at_ L+++ E- W+$@ N++ !K w !O !M V
         PS(+) PE Y+ PGP(+) t- 5 !X R !tv b+ !DI D G e+ h---- r+++ y++++
"...cancel my subscription to the resurrection!" (Jim Morrison)