The canonical way to use databases with JDBC is to have connection- pooled DataSource (instead of Connection) instances. That will make sure connections are not created unnecessarily are are re-used across operations. This may help create those:
https://bitbucket.org/kumarshantanu/clj-dbcp/src/tip/README Even when you are using DataSource, executing SQL involves creating Statement or PreparedStatement instances and ResultSet instances which is expensive. When you are performing batch operations (as it sounds like you are doing) you may need to create lazy sequences and keep fetching data from them so that you don't exhaust all of the heap. `resultset-seq` creates a lazy sequence that you can keep reading from. (sql/with-connection source-conn (with-query-results source ["SELECT id FROM emp WHERE salary > ?" 1000] ... ;; here `source` contains a lazy seq (from ResultSet) that won't blow the heap ;; read from `source` sequentially and write results to target ... )) Hope that helps. Regards, Shantanu On Jun 21, 3:18 pm, Matthias Cords <[email protected]> wrote: > thank you, this sounds very reasonable. very often there > is more than one piece of data to copy back and forth. so > typically i do a sql select, then put that to destination > data store, do another sql select and put to destination, > ... and so forth. from my understanding > (sql/with-connection ...) opens a connection and closes it > when finished. this would mean that either i load the entire > source data set into memory at once (impossible), or the > connection to the target database is being repeatedly > opened/closed - right? these two situations are what i am > looking to avoid. > > On 20 June 2011 19:07, Shantanu Kumar <[email protected]> wrote: > > > > > > > > > Write different functions for source and target? > > > (declare source-conn) > > (declare target-conn) > > > (defn get-source-data > > [] > > (sql/with-connection source-conn > > ...)) > > > (defn put-target-data > > [data] > > (sql/with-connection target-conn > > ...)) > > > (defn data-transfer > > [] > > (let [source (get-source-data)] > > (put-target-data source) > > ...)) > > > This approach may also save against concurrency issues just in case > > (`binding` isolates on a ThreadLocal basis and isn't propagated across > > threads.) > > > Regards, > > Shantanu > > > On Jun 20, 5:48 pm, MattC <[email protected]> wrote: > >> Hi, > > >> I am writing a lot of programs shuffling data between databases and > >> would like to use Clojure for some of it. > > >> While investigating the sql contrib library I was wondering whether > >> there is a supported way to have more than one database connection > >> open at any one time. > > >> My initial approach was > > >> (use '[clojure.contrib.sql :as sql]) > > >> (sql/with-connection {...} > >> (let [source-connection (sql/connection)] > >> (sql/with-connection {...} > >> (let [target-connection (sql/connection)] > >> (put-some-data (binding [sql/***** source-connection] (get- > >> some-data)))) > > >> well, roughly. but i am basically having a hard time switching > >> between two open connections. > > >> is there a way ? > > > -- > > You received this message because you are subscribed to the Google > > Groups "Clojure" group. > > To post to this group, send email to [email protected] > > Note that posts from new members are moderated - please be patient with > > your first post. > > To unsubscribe from this group, send email to > > [email protected] > > 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 [email protected] Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/clojure?hl=en
