Re: derby performance and 'order by'
the 'where' clause.create index IX_ORDERS_TIME on orders(time);--When I run a query against this table returning top 1,000 records, this query returns very quickly, consistently less than .010 seconds.select * from orderswhere time '10/01/2002' and time '11/30/2002'order by time;--Now run a similarly query against same table, returning the top 1,000 records.--The difference is that the results are now sorted by the primary key ('order_id') rather than 'time'.--This query returns slowly, approximately 15 seconds. Why??select * from orderswhere time '10/01/2002' and time '11/30/2002'order by order_id;--Now run a third query against the same 'orders' table, removing the where clause--This query returns quickly, around .010 seconds.select * from ordersorder by order_id;- If you run with derby.language.logQueryPlan=true, the actual query plans used for the following queries will be written to derby.log. This will show what indexes was used by the optimizer. Also see http://db.apache.org/derby/docs/10.1/tuning/rtunproper43414.html .Query with 'order by' will require sorting. Usually, sorting requires an extra step to put the data into the right order. This extra step can be avoided for data that are already in the right order. For example, if a single-table query has an ORDER BY on a single column, and there is an index on that column, sorting can be avoided if Derby uses the index as the access path.I think in case of your first and third query the optimizer will pick the available index thus probably avoiding requiring the sort step.Your second query involves more work than the first query, since it has a search condition on time, and an order by order_id. Thus if the optimizer picks the index on time, that will involve a sort step on order_id.Thanks,Sunitha. Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!
smime.p7s
Description: S/MIME cryptographic signature
Re: derby performance and 'order by'
Hi Ali,On Sep 19, 2005, at 10:26 AM, Suavi Ali Demir wrote:Actually, it sounds like the problem of finding top 1000 rows out of 166333 rows is different than sorting 166333 rows and maybe it could be optimized. Indeed. There is no need to sort all 166333 but the information that we are only looking 1000 rows would have to be passed all the way down to the point where Derby decides to sort. I have not thought through the details of an algorithm but when nRows we want is substantially smaller than TotalRows then i just feel there should be a better way to pick those nRows. For example, if nRows were 1, then all we had to do would be 1 single pass on 166333 rows to find the max. That is quite different than sorting all and this idea should be possible to generalize on 1=nRowsTotalRows.I agree that this would be a useful improvement. Now, how do we tell the back end that we want only the first 1000 rows?Or more generally (as found in competitive products):How do we tell the back end that we want to skip the first N and return the next M rows?Craig Ali Craig Russell [EMAIL PROTECTED] wrote: Hi Scott, From the query plan it appears that your filter selects 166,333 rows, of which you want the first 1000 according to the ordering of the order_id column. You can see that this is an effective strategy because Number of rows qualified=166333 Number of rows visited=166333. There's no time lost visiting rows that don't qualify. The database has to sort the 166,333 rows because the results are ordered according to the index scan column "time" not according to the order_id column. All of the rows need to be sorted even though you only want the first 1000 rows. I'd guess that the sorting of the 166,333 rows is what accounts for the 15 second delay you are experiencing. The index on order_id doesn't do you any good because you have a result that isn't indexed on order_id. If this isn't obvious, try to think of an algorithm that would use the order_id index on the result set. Craig On Sep 19, 2005, at 9:29 AM, scotto wrote: So for the second query: select * from orders where time '10/01/2002' and time '11/30/2002' order by order_id; the query plan shows that the index IX_ORDERS_TIME is used to filter the result set by time. The order by step does not use the primary key index to sort the results after the filter step. My questions: --Is it correct that the sort step not use the primary key index in this case? --Why is it not possible to use the index on order_id to sort after the filter has happened? Here is the query plan: Statement Name: null Statement Text: select * from orders where time '10/01/2002' and time '11/30/2002' order by order_id Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 14329 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : 2005-09-19 09:20:06.171 End Execution Timestamp : 2005-09-19 09:20:20.5 Statement Execution Plan Text: Sort ResultSet: Number of opens = 1 Rows input = 166333 Rows returned = 1000 Eliminate duplicates = false In sorted order = false Sort information: Number of merge runs=1 Number of rows input=166333 Number of rows output=166333 Size of merge runs=[93695] Sort type=external constructor time (milliseconds) = 0 open time (milliseconds) = 14297 next time (milliseconds) = 32 close time (milliseconds) = 0 optimizer estimated row count: 78377.51 optimizer estimated cost: 166745.12 Source result set: Index Row to Base Row ResultSet for ORDERS: Number of opens = 1 Rows seen = 166333 Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 10488 close time (milliseconds) = 0 optimizer estimated row count: 78377.51 optimizer estimated cost: 166745.12 Index Scan ResultSet for ORDERS using index IX_ORDERS_TIME at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 166333 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 3438 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=887 Number of rows qualified=166333 Number of rows visited=166333 Scan type=btree Tree height=3 start position: on first 1 column(s). Ordered null semantics on the following columns: stop position: = on first 1 column(s).
Re: derby performance and 'order by'
Hi Scott,How have you set up the test? Are you using ij and displaying all of the data or using jdbc to access the data?What do you do in 0.010 seconds? Do you read all of the rows into memory, or just record the time until you get the first row? Are you measuring the time taken to return all the rows or just the first row?Another reader has already commented on the fact that the second query is doing a lot more work than the first. The second query must sort the results after filtering the data, whereas the first and third queries can simply use the indexes and filter on the fly.I'm a little suspicious of the third query returning 720,000 results in 0.010 seconds.CraigOn Sep 16, 2005, at 4:42 PM, Scott Ogden wrote:I have observed some interesting query performance behavior and am hoping someone here can explain. In my scenario, it appears that an existing index is not being used for the ‘order by’ part of the operation and as a result the performance of certain queries is suffering. Can someone explain if this is supposed to be what is happening and why? Please see below for the specific queries and their performance characteristics.Here are the particulars:- create table orders(order_id varchar(50) NOT NULLCONSTRAINT ORDERS_PK PRIMARY KEY,amount numeric(31,2),time date,inv_num varchar(50),line_num varchar(50),phone varchar(50),prod_num varchar(50)); --Load a large amount of data (720,000 records) into the ‘orders’ table --Create an index on the time column as that will be used in the ‘where’ clause. create index IX_ORDERS_TIME on orders(time); --When I run a query against this table returning top 1,000 records, this query returns very quickly, consistently less than .010 seconds. select * from orderswhere time '10/01/2002' and time '11/30/2002'order by time; --Now run a similarly query against same table, returning the top 1,000 records.--The difference is that the results are now sorted by the primary key (‘order_id’) rather than ‘time’. --This query returns slowly, approximately 15 seconds. Why?? select * from orderswhere time '10/01/2002' and time '11/30/2002'order by order_id; --Now run a third query against the same ‘orders’ table, removing the where clause--This query returns quickly, around .010 seconds. select * from ordersorder by order_id; - Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
Re: BLOB : java.lang.OutOfMemoryError
Of course, since we use DRDA, we would have to see if that protocol supports blob streaming...CraigOn Aug 31, 2005, at 11:47 AM, David W. Van Couvering wrote:Again, yes, your help would be very much appreciated! Check out the code, get on derby-dev, ask questions, we'll be glad to help.DavidGrégoire Dubois wrote: Perhaps it could help : the DBMS Mckoi ( http://mckoi.com/database/ )has got a JDBC driver that supports blob streaming over network. Code isopen.I tried with IBM DB2, and same as Derby, streaming isn't supported withtheir JDBC driver. For a so big DBMS, I found it odd.I'm aiming to try Oracle, but I have some difficulties to use it. Doesanyone knows if the Oracle JDBC supports blob streaming ?It would be difficult to implement in Derby ? I'm familiar with javanetwork developpment. I develop in java for 4 years.GregLe mercredi 31 août 2005 à 11:04 -0700, Satheesh Bandaram a écrit : Right... Network server currently sends whole datatype over to client.While this works for most datatypes, since they are limited in size,it may be a problem for clob/blob types. Server needs to be able tostream these large datatypes over the wire on demand, so no matterwhether we have a common client or not, server needs to be enhanced.SatheeshDaniel John Debrunner wrote: Michael J. Segel wrote: On Wednesday 31 August 2005 10:49, Satheesh Bandaram wrote: Thanks for the offer.. It would really be great to have more developpersworking on Derby. Join the derby-dev alias to participate in thedevelopment.Derby embedded driver already has the capability to stream blob/clob,without requiring reading them completely into memory. It would be great toenhance Derby network server and Derby client to support this kind ofbehavior.Satheesh [SNIP]This may be a dumb question...Maybe its hindsight, but why isn't the Server Framework either a subclass or an extension to the embedded driver? Someone had posted that the Network Driver utilized the embedded driver... (Or is that bathtub gin affecting my memory?)I guess it goes more to the point of why not have more focus on a "universal" driver? My viewhttp://mail-archives.apache.org/mod_mbox/db-derby-dev/200508.mbox/[EMAIL PROTECTED]Really the two drivers are doing different things, one has to convertrequests into messages using DRDA over tcp/ip, the other just calls javamethods.Dan. david.vancouvering.vcf Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
Re: derby jdbc load driver
Hi,Java has this concept of a shutdown hook that can be registered with Runtime.getRuntime().addShutdownHook(Thread hook). The implementation class of the hook defines a run() method that would do DriverManager.getConnection("jdbc:derby:;shutdown=true") for the database that registered the hook.It might be good to experiment with this (sorry, I can't volunteer) and see whether the Derby runtime can be made more robust in the face of usage which does not shut down Derby cleanly.We use this hook concept with JDO's FOStore implementation. When the VM is shut down without cleanly closing the FOStore connection, our hook writes dirty buffers to disk and terminates.CraigOn Aug 9, 2005, at 2:51 PM, Bernd Ruehlicke wrote:I can confirm this.In additon shut it down gracefully alla the code below which I found insome doc while fighting with the forgotten .newInstance() protected void tearDown() { try { // Now try to disconnect _connection.close(); _connection = null; System.out.println("Closed connection"); /* In embedded mode, an application should shut down Derby. If the application fails to shut down Derby explicitly, the Derby does not perform a checkpoint when the JVMshuts down, which means that the next connection will be slower. Explicitly shutting down Derby with the URL ispreferred. This style of shutdown will always throw an "exception". */ boolean gotSQLExc = false; try {DriverManager.getConnection("jdbc:derby:;shutdown=true"); } catch(SQLException se) { gotSQLExc = true; } if(!gotSQLExc) { _logger.fine("Database did not shut down normally"); } else { _logger.fine("Database shut down normally"); } } catch (Throwable e) { _logger.fine("exception thrown:"+e.getMessage()); e.printStackTrace(); } System.out.println("Derby finished"); }B-) -Original Message-From: Satheesh Bandaram [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 09, 2005 4:32 PMTo: Derby DiscussionSubject: Re: derby jdbc load driverI think Derby recommends using .newInstance() to load JDBC drivers... In fact, it is required if you plan to shutdown and reboot databases on the same thread/VM for Derby. Take a look at the javadoc for EmbeddedDriver.http://db.apache.org/derby/javadoc/engine/org/apache/derby/jdbc/EmbeddedDriver.htmlSatheesh[EMAIL PROTECTED] wrote: Bogdan Mihaila wrote: Hello! I'm trying to load the embedded derby driver, like this: Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance(); As Rajesh pointed out, the problem is most likely the CLASSPATH. If you still have problems, please send the complete exception, including stack trace, as this helps to figure out the problem. By the way, you don't need the '.newInstance()' part - simply loading the driver class will set up the Derby system. This e-mail, including any attached files, may contain confidential and privileged information for the sole use of the intended recipient. Any review, use, distribution, or disclosure by others is strictly prohibited. If you are not the intended recipient (or authorized to receive information for the intended recipient), please contact the sender by reply e-mail and delete all copies of this message. --This e-mail, including any attached files, may contain confidential and privileged information for the sole use of the intended recipient. Any review, use, distribution, or disclosure by others is strictly prohibited. If you are not the intended recipient (or authorized to receive information for the intended recipient), please contact the sender by reply e-mail and delete all copies of this message. Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!
Re: Logo contest submissions
Hi,This is obviously an issue and I agree with the proposed solution to remove the offending logo ASAP.CraigOn Jun 8, 2005, at 11:46 AM, Daniel John Debrunner wrote:Jean T. Anderson wrote: Today's submission to http://issues.apache.org/jira/browse/DERBY-297raises a good issue that needs to be discussed on the list because itincludes the IBM Cloudscape logo (the box with the clouds), which wearen't legally entitled to use. This is currently listed as attachment 1for DERBY-297. I'll remove this entry if there aren't any objections. I think any such entry should be removed asap.Imagine if there was a logo based upon some Disney or Coca-Colatrademark, I don't think the fact the Derby is in incubation would stoplawyers for the rights holder in accusing the ASF of violations.Dan. Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
Re: Derby Logo, Take Two [managing logo submissions]
Taking this one step further, could we use Jira to tally votes? That would reduce the ambiguity over who is qualified to vote, and provide for one vote per Jira account... Just thinking(?) out loud. Craig On May 12, 2005, at 1:47 AM, Craig Russell wrote: Hi Jean, +1 to both. Requiring a logo submitter to get a Jira id sounds like a reasonable idea, and granting a license to use the logo at the time of upload makes perfect sense to me. Craig On May 11, 2005, at 1:37 PM, Jean T. Anderson wrote: In http://mail-archives.apache.org/mod_mbox/db-derby-user/200505.mbox/ [EMAIL PROTECTED] Susan Cline kick started the logo contest. She included this question: How will submissions be handled? Would anybody object to attaching logo submissions to a Jira issue (which I'll volunteer to open)? Managing submissions in Jira has the following advantages: 1) Uploading the file presents the opportunity to click on the button to Grant license to ASF for inclusion in ASF works. 2) Organizing all submissions within a single Jira issue will make it easier to include on the web site for review (rather than searching through old list posts). Jira has the disadvantage that it requires an additional step to request a Jira id at http://issues.apache.org/jira/; however, anyone may request an id. thoughts? comments? agreement? disagreement? -jean Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
Re: Logo
Hi Jean, On Jan 12, 2005, at 12:42 PM, Jean T. Anderson wrote: ummm, perhaps having a bed in the logo could be misconstrued as over the top. I'd like to start proposing guidelines for the logo contest, including: - No themes that could be misconstrued as offensive (though I realize how subjective offensive is) - No themes of violence -- the apache feather is good, but hatchets and apache attack helicopters are probably out. By the way, does anyone know of any issues with using the apache feather in a logo? Any other opinions out there for what might or might not be suitable? I must confess I was taken aback by the war-themed logos, and don't believe that they are suitable. And I agree that if symbols are offensive, we should not use them. The whole point of offense is that it is subjective and personal on the part of the person taking offense. In order to avoid offense, you need to try to take the other point of view, and it's often hard to know what will offend. Tricky indeed. But I've never heard of anyone complaining about horses or derby hats or feathers, so I think we've got lots of safe choices. Craig -jean Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
