Re: Ideas for optimisation needed
Do you have the same results if you exclude details field from your columns list ? 2012/12/21 John English john.fore...@gmail.com On 20/12/2012 14:12, Jean-Yves Linet wrote: Hi, May you could give more details about the structure of your table. The table looks like this: CREATE TABLE system_log ( id INTEGER GENERATED ALWAYS AS IDENTITY, timeTIMESTAMP DEFAULT NULL, usernameVARCHAR(15), facilityVARCHAR(15) NOT NULL, event VARCHAR(31) NOT NULL, module VARCHAR(15), testVARCHAR(255), details VARCHAR(32000), CONSTRAINT systemlog_pk PRIMARY KEY (id) ); I'm actually displaying a formatted view of the table: CREATE VIEW system_log_view AS SELECT TimeFormat(time) AS t_time, facility, event, details, NameFormat(username) AS name, username, module, test, id AS time FROMsystem_log; I had also suspected my formatting routines (TimeFormat, NameFormat), but I removed them and used time as t_time and username as name in place of the existing definitions of t_time and name. It made no noticeable difference. Anyway the response delay you have seams to be very slow compare with what I have with Derby. It's much slower than I would expect too! I always read my resultset as forward only. Try to make a first request with select count(*) to have the number of rows. and then a second request with an order by on time field and use only next() method. Yes, I tried that too, and it also made no noticeable difference. Thanks, -- John English
Re: what do errors like these mean?
Pavel Bortnovskiy pbortnovs...@jefferies.com writes: Once in a while, I see the following errors. What may cause them? java.sql.SQLException: Container 1,329 not found. The error means that one of the database files (table or index) cannot be found. It typically happens because some DDL operation (for example DROP INDEX, TRUNCATE TABLE or SYSCS_COMPRESS_TABLE) has removed the file, and an already compiled statement still references it. The error indicates a bug in Derby, so if you find a way to reproduce it, or some pattern that seems to increase the likelihood of the error, please file a bug report. Derby should invalidate already compiled statement referencing the table when DDL is performed on it, and that should make the statement recompile automatically the next time it is executed. There have been bugs in that area, though. (We fixed some of them in 10.9.1.0, in case you haven't already upgraded.) One possible workaround in that case is to call the SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE procedure to remove the stale query plans from the statement cache. -- Knut Anders
答复: Some questions about the Derby
Hi Knut Thank you for your reply , but I still have some problems need your help to answer. *Question one**： According to the reference manual: About divider，the scale expressions: 31 - lp + ls - rs the precision expressions: lp - ls + rp + max(ls + rp - rs + 1, 4) For example, the scale of the resulting data type of the following expression is 27: 11.0/.33 // 31 - 3 + 1 - 2 = 27 Another, the precision of the resulting data type of the following expression is 14: 11.0/.33 //3 - 1 + 6 + Max(1+6-2+1,4) = 14 Why the calculated results of the scale is less than the precision? I think that the precision (integer digits + scale digits) should be greater than the scale digits. *Question two**： In ij, I re-verify these statements and the results the same with you. But I use java code using a JDBC connection Derby ,it will appear. Can you help me explain the problem? -邮件原件 发件人: Knut Anders Hatlen [mailto:knut.hat...@oracle.com] 发送时间: 2012年12月20日 18:07 收件人: 王旭 抄送: derby-user@db.apache.org 主题: Re: Some questions about the Derby Bryan Pendleton bpendleton.de...@gmail.com writes: Forwarding to the list. On 12/19/2012 04:10 AM, 王旭 wrote: *Question one**：*** Firstly, I created a table. CREATE TABLE CV ( AGE INTEGER NOT NULL, RT DECIMAL(20,10), PRIMARY KEY (AGE) ) Then innserted data insert into CV VALUES (80，10729.50) Finally, an error occured when the SQL statement select?(95000.0*1000/1000)/1000.0?*?RT?from?CV?where?Age=80? was executed, it displayed the generated value has gone beyond the data type of DECIMAL / NUMERIC (31,25). Could you tell me how Derby do the precision control and operation on the data type of DECIMAL when it performs a variety of arithmetic operations including add, subtract, multiply and divide? The reference manual explains how numeric types are promoted in expressions here: http://db.apache.org/derby/docs/10.9/ref/rrefsqlj27767.html *Question two**：*** Firstly, I created a table. CREATE TABLE CV ( AGE SMALLINT, RT DOUBLE, PRIMARY KEY (AGE) ) Then innserted data insert into CV VALUES (20，766.3) Finally, I got results of 268204.994 after executing the SQL statement select 35.0/1000 * RT from CV the where age = 20?, but the actual result should be 268205.0. When I execute those statements in ij, I see the result you expected: ij CREATE TABLE CV (AGE SMALLINT, RT DOUBLE, PRIMARY KEY (AGE)); 0 rows inserted/updated/deleted ij insert into CV VALUES (20, 766.3); 1 row inserted/updated/deleted ij select 35.0/1000 * RT from CV where age = 20; 1 -- 268205.0 1 row selected Could you tell me whether the cause of problem is inaccurated data from a floating-point type double. That sounds likely. But it's difficult to tell without knowing exactly what you did when you ended up with that number. -- Knut Anders
RE: what do errors like these mean?
Thank you, Knut, for your prompt response. It seems that my caching of Prepared Statements is causing some problems. In some previous responses, it was indicated that Derby is caching them internally anyway, so maybe a better approach for me is not to cache them on my side and create them anew? Most of my inserts/updates are done in batches, so I could create a PrepStmt before the batch and remove a reference to it at the end of the batch's execution. If the performance penalty for compilation of PrepStmt is not that great, then such approach may be more desirable to avoid errors in the production environment. Thanks, P. -Original Message- From: Knut Anders Hatlen [mailto:knut.hat...@oracle.com] Sent: Friday, December 21, 2012 8:59 AM To: Derby Discussion Subject: Re: what do errors like these mean? Pavel Bortnovskiy pbortnovs...@jefferies.com writes: Once in a while, I see the following errors. What may cause them? java.sql.SQLException: Container 1,329 not found. The error means that one of the database files (table or index) cannot be found. It typically happens because some DDL operation (for example DROP INDEX, TRUNCATE TABLE or SYSCS_COMPRESS_TABLE) has removed the file, and an already compiled statement still references it. The error indicates a bug in Derby, so if you find a way to reproduce it, or some pattern that seems to increase the likelihood of the error, please file a bug report. Derby should invalidate already compiled statement referencing the table when DDL is performed on it, and that should make the statement recompile automatically the next time it is executed. There have been bugs in that area, though. (We fixed some of them in 10.9.1.0, in case you haven't already upgraded.) One possible workaround in that case is to call the SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE procedure to remove the stale query plans from the statement cache. -- Knut Anders Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
Re: Ideas for optimisation needed
On 21/12/2012 15:20, Jean-Yves Linet wrote: Do you have the same results if you exclude details field from your columns list ? Yes. No significant change. But: I'm trying it on my development system (127.0.0.1) since I can't play around the live system, and the time varies by what other applications I have open at the time, which suggests to me that there's a lot of swapping going on... -- John English
Re: what do errors like these mean?
On 21.12.2012 16:18, Pavel Bortnovskiy wrote: Thank you, Knut, for your prompt response. It seems that my caching of Prepared Statements is causing some problems. In some previous responses, it was indicated that Derby is caching them internally anyway, so maybe a better approach for me is not to cache them on my side and create them anew? Most of my inserts/updates are done in batches, so I could create a PrepStmt before the batch and remove a reference to it at the end of the batch's execution. If the performance penalty for compilation of PrepStmt is not that great, then such approach may be more desirable to avoid errors in the production environment. Hi Pavel, Are you using the embedded driver or the client driver? The client driver can cache statements on the client side if you use the ConnectionPoolDataSource. This may save you some round-trips, but note that this caching is in addition to the caching that happens on the server side. -- Kristian Thanks, P. -Original Message- From: Knut Anders Hatlen [mailto:knut.hat...@oracle.com] Sent: Friday, December 21, 2012 8:59 AM To: Derby Discussion Subject: Re: what do errors like these mean? Pavel Bortnovskiy pbortnovs...@jefferies.com writes: Once in a while, I see the following errors. What may cause them? java.sql.SQLException: Container 1,329 not found. The error means that one of the database files (table or index) cannot be found. It typically happens because some DDL operation (for example DROP INDEX, TRUNCATE TABLE or SYSCS_COMPRESS_TABLE) has removed the file, and an already compiled statement still references it. The error indicates a bug in Derby, so if you find a way to reproduce it, or some pattern that seems to increase the likelihood of the error, please file a bug report. Derby should invalidate already compiled statement referencing the table when DDL is performed on it, and that should make the statement recompile automatically the next time it is executed. There have been bugs in that area, though. (We fixed some of them in 10.9.1.0, in case you haven't already upgraded.) One possible workaround in that case is to call the SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE procedure to remove the stale query plans from the statement cache. -- Knut Anders Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
RE: what do errors like these mean?
I'm using an embedded driver with Derby in the in-memory only mode. My application creates in-memory tables in Derby during its run, which are then accessed by various components through JDBC. -Original Message- From: Kristian Waagan [mailto:kristian.waa...@oracle.com] Sent: Friday, December 21, 2012 11:38 AM To: derby-user@db.apache.org Subject: Re: what do errors like these mean? On 21.12.2012 16:18, Pavel Bortnovskiy wrote: Thank you, Knut, for your prompt response. It seems that my caching of Prepared Statements is causing some problems. In some previous responses, it was indicated that Derby is caching them internally anyway, so maybe a better approach for me is not to cache them on my side and create them anew? Most of my inserts/updates are done in batches, so I could create a PrepStmt before the batch and remove a reference to it at the end of the batch's execution. If the performance penalty for compilation of PrepStmt is not that great, then such approach may be more desirable to avoid errors in the production environment. Hi Pavel, Are you using the embedded driver or the client driver? The client driver can cache statements on the client side if you use the ConnectionPoolDataSource. This may save you some round-trips, but note that this caching is in addition to the caching that happens on the server side. -- Kristian Thanks, P. -Original Message- From: Knut Anders Hatlen [mailto:knut.hat...@oracle.com] Sent: Friday, December 21, 2012 8:59 AM To: Derby Discussion Subject: Re: what do errors like these mean? Pavel Bortnovskiy pbortnovs...@jefferies.com writes: Once in a while, I see the following errors. What may cause them? java.sql.SQLException: Container 1,329 not found. The error means that one of the database files (table or index) cannot be found. It typically happens because some DDL operation (for example DROP INDEX, TRUNCATE TABLE or SYSCS_COMPRESS_TABLE) has removed the file, and an already compiled statement still references it. The error indicates a bug in Derby, so if you find a way to reproduce it, or some pattern that seems to increase the likelihood of the error, please file a bug report. Derby should invalidate already compiled statement referencing the table when DDL is performed on it, and that should make the statement recompile automatically the next time it is executed. There have been bugs in that area, though. (We fixed some of them in 10.9.1.0, in case you haven't already upgraded.) One possible workaround in that case is to call the SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE procedure to remove the stale query plans from the statement cache. -- Knut Anders Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority. Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
Re: Ideas for optimisation needed
John English john.fore...@gmail.com writes: On 20/12/2012 14:12, Jean-Yves Linet wrote: Hi, May you could give more details about the structure of your table. The table looks like this: CREATE TABLE system_log ( id INTEGER GENERATED ALWAYS AS IDENTITY, timeTIMESTAMP DEFAULT NULL, usernameVARCHAR(15), facilityVARCHAR(15) NOT NULL, event VARCHAR(31) NOT NULL, module VARCHAR(15), testVARCHAR(255), details VARCHAR(32000), CONSTRAINT systemlog_pk PRIMARY KEY (id) ); I'm actually displaying a formatted view of the table: If you run the query with derby.language.logQueryPlan=true and check derby.log, does the query plan say that log_index is used? Does it change anything if you access the table directly instead of via the view? I think we've had reports that the optimizer cannot always see through the views and misses out on some opportunities for optimization. -- Knut Anders
Re: what do errors like these mean?
Pavel Bortnovskiy pbortnovs...@jefferies.com writes: Thank you, Knut, for your prompt response. It seems that my caching of Prepared Statements is causing some problems. In some previous responses, it was indicated that Derby is caching them internally anyway, so maybe a better approach for me is not to cache them on my side and create them anew? Most of my inserts/updates are done in batches, so I could create a PrepStmt before the batch and remove a reference to it at the end of the batch's execution. That sounds like a case where the internal cache could do a good job. If the exact same query (identical SQL string) has been executed before, and it's still in the cache (default cache size is 100, see [1]), preparing the statement should essentially be just a lookup in a hash table. I'd expect that cost to be negligible for most batches. [1] https://db.apache.org/derby/docs/10.9/ref/rrefproperstatementcachesize.html If the performance penalty for compilation of PrepStmt is not that great, then such approach may be more desirable to avoid errors in the production environment. Not sure if it will avoid the errors, as Derby should invalidate both the active PreparedStatements and those in the internal cache. But there may be bugs, of course, preventing one or the other from being invalidated. Relying on the internal cache for the batches might help simplifying the application code, though. By the way, do you have the full stack trace for the exception you saw? The original post only included the stack where the top-level SQLException was created. There should be a StandardException nested in the SQLException telling exactly where it fell over. And do you regularly run SYSCS_COMPRESS_TABLE on the database? That has been the most common source for these errors. Which Derby version are you running? Thanks, -- Knut Anders
