On Jan 7, 7:36 pm, Rick Mouritzen <rickm...@gmail.com> wrote: > A prepared statement is something inside a database. The true object > isn't part of JDBC or a JDBC driver.
Some JDBC drivers and JEE Datasources (all Oracle and IBM products) do cache stmt per connection (i.e. stmt.close() doesn't really close it, just put it back to cache). The cache is just a ConcurrentHashMap of SQL text to stmt object , with some MRU or MFU algorithm. > > So you open a connection, then create your SQL using JDBC's > PreparedStatement to tell the DB to use a prepared statement. Next > time that same SQL is used with another JDBC PreparedStatement, the DB > will do it's magic. In Oracle, this causes a soft parsing hitting db server library cache, lot less overhead compare to hard parsing. But, in extremely high concurrent (100s threads) pushing high frequent short transactions. Latch contention on db library cache can be scalability killer. JDBC client side stmt cache reduces the contention significantly in same cases. The situation is rare, but I did see > 50% throughput increase by tuning stmt caching in two systems I worked on. Agreed ClojureQL should not be bothered with this. Just to clarify the point. > > Re-using a single PreparedStatement within a Connection is something > you can only do with one Connection on one thread. Generally it's not > worth over-thinking. Using batching is where you really see > performance gains. > > This is all separate from parsing from ClojureQL to SQL/JDBC, of course. > > > > > > > > On Fri, Jan 7, 2011 at 10:22 AM, Feng <hou...@gmail.com> wrote: > > I'd suggest ClojureQL only optimize lisp form to SQL text > > transformation, and provide options whether to use PreparedStatement > > or not. Leave other kind of optimizations to databases/drivers. > > > Some thoughts below: > > > - PreparedStatement avoids high cost of frequent query planning > > (oracle call it hard parsing) and SQL injection, which are critical > > for OLTP workload. However, it's not always good practice for > > datawarehousing queries. In some high end databases, query planner > > need to see join and filter conditions as constant for the best query > > planning (e.g. leveraging histogram, partition statistics). ClojureQL > > can provide controls to evaluate clojure variables right before > > creating SQL string. Not sure about whether at variable level, or > > statement level, or connection level though. I can see various trade- > > offs (simplicity vs flexibility etc). > In previous version a while ago, ClojureQL seems always use Statement with var values inlined into SQL text. This is actually good for data warehousing queries. Maybe just introduce a flag :use-prepared (default true) in db config object, bring back old implementation when set it to false? It's rarely seen mixed OLTP and ad hoc analytical queries to a single database, so this will cover both use cases well. Stmt or var level control may destroy elegancy of ClojureQL API, which is less desirable. > > - Client side JDBC Statement caching should leave to db Driver, or > > DataSource implementations. Most of high end database and JEE > > appserver do, with various trade-offs and complexities. I think > > ClojureQL should stay away from them. > > > BTW, great library and fun to use! > > > On Jan 7, 6:23 am, LauJensen <lau.jen...@bestinclass.dk> wrote: > >> Hi Sean, > > >> Half right - It will compile everytime, but not necessary create the > >> AST everytime: > > >> (let [my-table (-> (table :user) > >> (select (where (= :id user-id))) > >> (project [:dateofbirth :gender :zipcode]))] > >> (repeatedly @my-table)) > > >> However I see there are some good thoughts on optimization in this > >> thread already. > >> Perhaps is the AST always kept the SQL representation in a field, we > >> could totally > >> avoid re-compilation for repeat queries. That would still created the > >> preparedStatement, > >> but Im not sure thats a real time-stealer. I'll have to check. > > >> On Jan 7, 1:33 am, Sean Corfield <seancorfi...@gmail.com> wrote: > > >> > On Thu, Jan 6, 2011 at 2:33 AM, LauJensen <lau.jen...@bestinclass.dk> > >> > wrote: > >> > > Yes the two statements are equivalent. ClojureQL compiles everything > >> > > to prepared > >> > > statements, with every argument automatically paramterized. > > >> > Cool, that's what I'd hoped. But just to clarify... > > >> > If I have code that repeatedly calls this: > > >> > @(-> (table :user) > >> > (select (where (= :id user-id))) > >> > (project [:dateofbirth :gender :zipcode])) > > >> > it is going to construct that AST and compile it to SQL every time it > >> > hits it, yes? > >> > -- > >> > Sean A Corfield -- (904) 302-SEAN > >> > Railo Technologies, Inc. --http://getrailo.com/ > >> > An Architect's View --http://corfield.org/ > > >> > "If you're not annoying somebody, you're not really alive." > >> > -- Margaret Atwood > > > -- > > You received this message because you are subscribed to the Google > > Groups "Clojure" group. > > To post to this group, send email to clojure@googlegroups.com > > Note that posts from new members are moderated - please be patient with > > your first post. > > To unsubscribe from this group, send email to > > clojure+unsubscr...@googlegroups.com > > For more options, visit this group at > >http://groups.google.com/group/clojure?hl=en -- You received this message because you are subscribed to the Google Groups "Clojure" group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
