Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
From: "Developer Dude" <developer.dude@comcast.net>
To: "'Derby Discussion'" <derby-user@db.apache.org>
Subject: Connection opneing/closing time - was RE: updatable selects
Date: Tue, 20 Nov 2007 14:11:59 -0800
Message-ID: <003501c82bc2$5f397530$6501a8c0@SchemaLogic>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcgroCZ0o9UMnDRuTLyj6KRwiJbMvQAAlhBwAAWw4nAAAaJ2EA==
In-Reply-To: <20071120211320.B9D6C54424@dbrack01.segel.com>

I based my assertion on the fact that I had a unit test that created then
closed 1000 of my connection objects (containing the prepared statements,
CRUD methods, etc.) and which opens the connection eventually with the
following:

	static public Connection openConnection () throws SQLException {
		Connection conn = DriverManager.getConnection("jdbc:derby:"
+ DATABASE_NAME + ";create=true");
		conn.setAutoCommit(true);
		return conn;
	}

With the object creation, opening the connection, closing it, I was able to
complete the cycle 1000 times in 1328 ms or approx. 1.3ms.
If I open a connection before cycling through the one thousand, then it only
takes 1094 ms. - telling me that the first connection took 334 ms., and
those thereafter took less - about 1.1 ms each.

My understanding is that once the first connection is made, subsequent
connections are much faster and relatively painless. My tests seem to bear
that out.

Maybe I am not understanding what is happening, but that is what I am
seeing. I am very open to someone telling me that I am not doing something
right or I am misunderstanding what is going on. I thought at first that the
connection could be a bottleneck, and maybe it still is compared to other
operations, but in my design it doesn't seem to be.


-----Original Message-----
From: Michael Segel [mailto:msegel@segel.com] On Behalf Of derby@segel.com
Sent: Tuesday, November 20, 2007 1:14 PM
To: 'Derby Discussion'
Subject: RE: updatable selects

Again, it depends on the application.

If you're embedding derby, then you'll want to hold a connection open as
long as your application is alive. The reason being that it costs about
100ms to open a JDBC connection to a database compared with 10ms for an
execute stmt. (YMMV but the order of magnitude exists in setting up the
connection, and that's the point.)

There are other reasons why you'd want to maintain the connection. Caching
of prepared statements as an example...

So, I'd rethink your design. The cost of the connection object isn't always
that expensive vs some of the underlying objects held/referenced by the
connection object. 


> -----Original Message-----
> From: Developer Dude [mailto:developer.dude@comcast.net]
> Sent: Tuesday, November 20, 2007 12:40 PM
> To: 'Derby Discussion'
> Subject: RE: updatable selects
> 
> Or client/server based period.
> 
> My current use of Derby is as a Swing client side cache for server 
> objects that can number in the millions for any given view (multiple 
> views may be
> open) and that I don't want to hold in memory in order to show in a view.
> So
> I use the embedded version solely (the server I am hitting uses 
> SQLServer, or Oracle, depending on the clients needs/wants).
> 
> Still, I don't hold open a connection for any longer than I need to 
> run a CRUD operation (single or batch). I do all the DB/data store ops 
> in the background and notify the model when the ops change it. The 
> notify methods use invokeLater() so that I can keep the Swing thread 
> and the background threads separate. I have no problem opening and 
> closing many connections (one connection per thread, I use a 
> multi-reader/single writer lock in the methods).
> 
> I try to keep the model and the view as disconnected from the backing 
> data store as possible via a processor interface that handle all CRUD 
> operations (there are background processors to populate the data store 
> and model from the server). It is much cleaner and I can swap in a 
> different caching store as needed without changing a lot of model 
> code, or having users of the model worry about the complexities of the 
> data store. The data store can be reused for different models and 
> views.
> 
> -----Original Message-----
> From: msegel@dbrack01.segel.com [mailto:msegel@dbrack01.segel.com]
> Sent: Tuesday, November 20, 2007 9:33 AM
> To: Derby Discussion
> Subject: Re: updatable selects
> 
> Depends on the app.
> 
> Not everything is web based.
> Especially if you've embedded the engine in your app you'll want to 
> keep the connection for a long time.
> 
> Sent via BlackBerry by AT&T
> 
> -----Original Message-----
> From: "Developer Dude" <developer.dude@comcast.net>
> 
> Date: Tue, 20 Nov 2007 09:33:59
> To:"'Derby Discussion'" <derby-user@db.apache.org>
> Subject: RE: updatable selects
> 
> 
> Do you really want to hold a connection open for that long? I believe 
> connections can time out.
> 
> Who knows how long the user may let that screen sit there in edit mode?
> 
> -----Original Message-----
> From: Erik Steen Jakobsen [mailto:esj@logodata.dk]
> Sent: Tuesday, November 20, 2007 12:33 AM
> To: Derby Discussion
> Subject: RE: updatable selects
> 
> 
> I've investigated further ...
> Documentation says (as I read it! ) that type_scroll_insensitive  
> should give updatable selects (ie : resultset which is updatable).
> 
> 
> But only ResultSet.TYPE_FORWARD_ONLY (!) gives the functionality I 
> seek but without the ease.
> And even though I can use it... it is a logical pain to me Since model 
> of derby/jdbc/swing seems to be :
> 
> database -> resultset -> modeldata
> datebase <- resultset <- modeldata
> 
> then this picture gives a nice possibility ... given that I can lock 
> the rows in the database.
> 
> 1: You present a "view" to a kill  ...
> 2: the user updates freely while you are listening to the actions, you 
> move the cursor in the resultset and updates it accordingly .
> 
> 3:Use ends action , you move the cursor to the updated rows and you 
> commit.
> or in case of user regrets ... you just don't.-
> 
> 
> That would be beautiful and elegant programming ...KISS!
> But I have been away from sql programming for a very long time.
> So I guess I read the documentation wrong:-) Or seek the wrong 
> solution
> 
> Test code
> ---- CLIP ---
> //ps this code is only a slightly changed version of the documentation.
> --
> // You'll need to define a databse("testdb") with a table "tab1" with 
> id, empname, addr1, addr2,account where account is an int
> 
>    /*
>  * testit.java
>  *
>  * Created on 19. november 2007, 22:12
>  *
>  * To change this template, choose Tools | Template Manager
>  * and open the template in the editor.
>  */
> 
> package testjavadb;
> 
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> 
> /**
>  *
>  * @author esj
>  */
> public class testit
> {
> 
>     /** Creates a new instance of testit */
>     public static void main(String[] args)
>     {
>         try
>         {
> //            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
>             Class.forName("org.apache.derby.jdbc.ClientDriver");
>         } catch (ClassNotFoundException ex)
>         {
>             ex.printStackTrace();
>         }
> 
>         try
>         {
> //            Connection conn =
> DriverManager.getConnection("jdbc:derby:C:/Documents and 
> Settings/esj/.netbeans/5.5.1/derby/testdb");
> 
>             Connection conn =
> DriverManager.getConnection("jdbc:derby://localhost:1527/testdb");
> 
>             conn.setAutoCommit(false);
> 
> // Create the statement with concurrency mode CONCUR_UPDATABLE // to 
> allow result sets to be updatable //OBS OBS OBS // only forward_only 
> CAN make the code work!
> //            Statement stmt =
> conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
> 
>             Statement stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
>                         ResultSet.CONCUR_UPDATABLE,
>                         ResultSet.CLOSE_CURSORS_AT_COMMIT);
> 
> // this will NOT WORK ... ONLY ResultSet.TYPE_FORWARD_ONLY // 
> Updatable statements have some requirements // for example, select 
> must be on a single table
> 
>             ResultSet uprs = stmt.executeQuery(
>                         "SELECT * " +
>                         "FROM \"tab1\" FOR UPDATE of \"account\""); // 
> only account can be updated (according to docs)
>             String theDept="viby";
> 
> // lOOk out this is not legal with forward only.-)
>             uprs.absolute(2);
> //
> 
>             while (uprs.next())
>             {
>                 String empname = uprs.getString("empname");
>                 String dep = uprs.getString("addr2");
>                 int no = uprs.getInt("id");
>                 int account  = uprs.getInt("account");
>                 if (dep.equals(theDept))
>                 {
> // if the current row meets our criteria, // update the updatable 
> column in the row
>                     account+=(no +1);
>                     uprs.updateInt("account",account );
>                     uprs.updateRow();
>                     System.out.println("Updating account for employee:"
> +
>                                 empname +";" + dep + " to " + account);
>                 }
>             }
>             conn.commit(); // commit the transaction // close object
>             uprs.close();
>             stmt.close();
> // Close connection if the application does not need it any more
>             conn.close();
>         } catch (SQLException ex)
>         {
>             ex.printStackTrace();
>         }
>     }
> }
> 
> --- end clip ---
> -----Original Message-----
> From: Dag.Wanvik@Sun.COM [mailto:Dag.Wanvik@Sun.COM]
> Sent: 19. november 2007 16:45
> To: Derby Discussion
> Subject: Re: updatable selects
> 
> Erik Steen Jakobsen <esj@logodata.dk> writes:
> 
> > All I get from derby is resultsets that are read_only when I ask for 
> > concurrent updatable....
> 
> You can update through JDBC result sets if you specify 
> ResultSet.CONCUR_UPDATABLE. You do not even need for specify "FOR UPDATE"
> in
> that case in the underlying query (default if you use CONCUR_UPDATABLE).
> 
> But not all queries are updatable, cf. this section of the reference
> manual:
> 
> http://db.apache.org/derby/docs/dev/ref/rrefsqlj41360.html#rrefsqlj413
> 60
> __sqlj15384
> 
> Essentially, only simple, single table selects are updatable.
> 
> Hope this helps! If not, can you provide a repro?
> 
> Thanks,
> Dag
> 
> 
> 
> 
>