RE: Do programmers tune SQL?

From: Jack C. Applewhite <japplewhite_at_inetprofit.com>
Date: Mon, 01 Apr 2002 10:23:32 -0800
Message-ID: <F001.0043811A.20020401102332@fatcity.com>

Dennis,

I've had to keep after the Java developers here to use bind variables instead of literals. Apparently, it's much easier for them to construct literal SQL. They now understand the importance and use bind variable pretty much all the time.

A few months ago they started spawning multiple threads of some Java processes and used a "thingey" called Connection Pooling to mediate connections to Oracle. They fell back to using literal SQL, until they figured out how to use bind variables with Connection Pooling. Apparently, it's even harder to use bind variables with Connection Pooling. If you make the Java developers aware of these issues up front, maybe they'll go ahead and learn how to code it "right" initially.

Sorry I can't give details, 'cause all I know about Java is how to spell it! ;-)

Jack

---

Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
japplewhite_at_inetprofit.com
(512)327-9068

-----Original Message-----
WILLIAMS
Sent: Monday, April 01, 2002 11:20 AM
To: Multiple recipients of list ORACLE-L

Oh, I guess I'm a little slow on Monday. I've enjoyed the discussion so far. Very close to my situation.

Given my situation - i.e., we haven't had many SQL statement problems, but expecting to receive more with Java, I'm wondering how I can get ahead of the game. I have worked on a set of SQL statement recommendations, simple stuff like make sure screen queries use and index. I am considering creating a checklist form for SQL statements for the developers to use. Like include the listing from EXPLAIN PLAN. Does anyone have any thoughts on this approach? I'm not sure if the developers/management would go for it, but I thought it would be worth asking as a starting point. Everybody is new to Java and a little nervous, so they are probably more open to suggestions.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: japplewhite_at_inetprofit.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Mon Apr 01 2002 - 12:23:32 CST