>As a simple example, let's say we implement the idea >above and build PREPARE and EXECUTE into the syntax
We are definitely talking past each other. I don't think PREPARE should be an extension of the syntax (in SQL). I think that PreparedStatement (in java.sql.* ) interface should be supported. >I believe you must be talking about a special, > HSQL-native, non-SQL, > non-JDBC protocol/interface. In that case, there Read the link. Please. This is part of java.sql from Sun. It is standard. Here is a better link http://java.sun.com/j2se/1.4/docs/api/java/sql/PreparedStatement.html > - if prepared statments are created on a database > instance across the > network, In terms of network overhead on a distributed system, it should be no different then a normal Statement object. Maybe less. > - there must still be a mechanism to submit a SQL > statement to be prepared, Sent a query as a string to the connection. Look this is how you could use it in a Java app to bulk load some records //---------------------------------------------- PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?"); // no more HSQL parsing from this point on //.....get some data..... for( i=0; i<1000000; ++i) { // a 60% speed up is huge pstmt.setBigDecimal(1, red_salary( i ) ); pstmt.setInt(2, read_id( i ) ); pstmt.execute(); } > precompiled statements that is shorter than > retransmitting their body, once Prepared statements should be a list of atomic operation objects. The can stored locally or in an indexed registry in the server. Server side storage may be faster but more complicated to implement. > - once we have a handle to a precompiled statement, > we must have a mechanism > to indicate to the engine to (re)execute the > precompiled statement to which > the handle refers. As I see it, we have two > choices: use CALL > EXECUTE(handle) syntax, or to build EXECUTE directly > into the syntax: > EXECUTE handle. Well this part is the interesting part :-) The output of the parse should be a list of objects. Each object represents a single atomic operation on the db. In a Statement they are executed immediately. In a prepared statement they are stored. > Every SQL statmement is atomic w.r.t. every other > statements in the broadest > sense, so I assume you really mean "low-level > atomic" here. > > The only things that I can think of that are low > level atomic operations in > hsqldb are insertNoCheck and deleteNoCheck in that Also the Expression objects > each represents witing a > single line to the log. All other actions are > composed and are typically > tree (rather than list) oriented. Any tree recursion can be converted to a list with a call stack. But that is not too important. The details of the data structure used, whether list or tree, should only matter to the internals of the Parse and the Statment. > If one captures the resulting > low level atomic > operations that processing such a tree outputs, it > is highly unlikely that > re-executing that exact list in absense of > consulting the tree and tables > used to gerenrate it would have the same result, > since the low level > operations may be required to refer to rows that are > themselves were deleted I think we are talking past each other again. Envision bytecode with a stack. The bytecode are atomic operations. The stack holds what ever partial result sets are generated as operation are processed. The operations for a particular query should not change, only the inputs, data and stack. >Also, if new parameters for a precompiled >statement are substituted, then the previously >generated list >definitely >becomes invalid. The list of operation is Not invalid. The stack of partial results would be discarded each execution. >Well, I think this is more difficult than one might expect. Ok. It is not easy. >To provide new values to a precompiled parameterized >statement, one must provide a language for soing so, >and thus that No no no. please read the Sun API and all will be clear. >Unfortunately, the actual number of cases where this >is applicable in the >context of processing client (as opposed to internal) >work is not as high as one might like. I can't speak for what other developer do, but on applications that I have implemented, like web pages backed by JSP backed by a DB, there are a few (under 10 ) queries that account for 80% to 90% of the activity on the DB. Typically the form of the query stays the same but the input values changes. example SELECT userid FROM login_tlb WHERE passwd='blah' Take 60% of the load off these queries would be huge. >I have found using precompiled Oracle cursors and >stored procedures to be most useful >when providing a structured and garanteed interface >to an applciation, by turning off ad-hoc SQL access >to a schema, forcing everyone to go I agree 100%. I just think that is a very common, useful situation. But maybe not for your app. *shrug* >I do not see how providing any fine-grain control >over the log process can avoid logging > these. Plus, regardless of what one might do to > improve the speed of the > log, I have determined that ~80% of the time > is spent in the lowest level > (inside the actual low-level calls to write out > to disk) and has very little > to do with the volume of data written in each > call, depending more Exactly. Which why we need better control over logging. There are many applications where speed is more important then data recovery in the event of a crash. For those applications a 'lazy write back' would be much more efficient. Only write dirty pages on cache flush or when the sytem is idle. I admit you would not do bank transactions this way but it would work great for a video game that kept game objects in a db. >In the larger scheme, this represents only a 10% >reduction to taken in >logging (since ~80% is beyond our control), or around >a 2% savings overall. Not if we can drive parsing to zero ;-) >. Which would you rather work on? Well I like data stuctures a lot... Karl ===== Karl Meissner ........................................................... : Meissner Software Development, LLC : : "Software development and project management services." : : http://meissner.v0.net/resume.htm : ........................................................... __________________________________________________ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ _______________________________________________ hsqldb-developers mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hsqldb-developers Sponsored by http://www.ThinkGeek.com/
