Re: Difference between having no default and having DEFAULT NULL
Hi Trejkaz, What version of Derby did you use to create the original schema? This behavior (COLUMN_DEF = NULL) goes back at least as far as Derby 10.10.1.1. There is no semantic difference between a column which is declared without a default and a column which is declared as DEFAULT NULL. The following script shows the current behavior: ij> connect 'jdbc:derby:tmpdbs/db0;create=true'; ij> drop table t; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T' because it does not exist. ij> drop table s; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'S' because it does not exist. ij> create table t( a int, b int ); 0 rows inserted/updated/deleted ij> create table s( a int default null, b int ); 0 rows inserted/updated/deleted ij> insert into t(b) values (1); 1 row inserted/updated/deleted ij> insert into s(b) values (1); 1 row inserted/updated/deleted ij> select * from t; A |B --- NULL |1 1 row selected ij> select * from s; A |B --- NULL |1 1 row selected ij> call syscs_util.syscs_register_tool( 'databaseMetaData', true ); 0 rows inserted/updated/deleted ij> select column_def from table( getColumns( null, 'APP', 'T', 'A' ) ) t; COLUMN_DEF NULL 1 row selected ij> select column_def from table( getColumns( null, 'APP', 'S', 'A' ) ) t; COLUMN_DEF NULL 1 row selected ij> call syscs_util.syscs_register_tool( 'databaseMetaData', false ); 0 rows inserted/updated/deleted Thanks, -Rick On 11/12/15 3:53 PM, Trejkaz wrote: Hi all. I'm seeing some weird stuff in our database schema while trying to improve sanity for migrations. Basically I'm noticing that tables we migrated from earlier schemas have "no default" (COLUMN_DEF = null), whereas tables we create today seem to have COLUMN_DEF = NULL. I'm wondering whether there is any semantic difference between the two, because as far as I know, if you don't put a default in for a column, the default for that column is already null. Is that not the case? And if it is not the case, why does DEFAULT NULL even result in the schema being different? TX
Re: Difference between having no default and having DEFAULT NULL
On Wed, Nov 18, 2015 at 1:40 AM, Rick Hillegas <rick.hille...@gmail.com> wrote: > Hi Trejkaz, > > What version of Derby did you use to create the original schema? This > behavior (COLUMN_DEF = NULL) goes back at least as far as Derby 10.10.1.1. > There is no semantic difference between a column which is declared without a > default and a column which is declared as DEFAULT NULL. This is good news, so it's okay to remove that from our definitions without changing behaviour, and dropping the defaults will have no negative effect. I'm guessing the person who put them in was putting in integer columns and just wasn't sure whether the default was going to be null or 0, but I tested that myself already and found exactly what you posted in your reply, that it does default to null. The original schema long ago would have been created under 10.1.x.x or something similarly ancient. The furthest I can go back viewing the history is 10.4.x.x but it doesn't go back as far as the schema does. TX
Difference between having no default and having DEFAULT NULL
Hi all. I'm seeing some weird stuff in our database schema while trying to improve sanity for migrations. Basically I'm noticing that tables we migrated from earlier schemas have "no default" (COLUMN_DEF = null), whereas tables we create today seem to have COLUMN_DEF = NULL. I'm wondering whether there is any semantic difference between the two, because as far as I know, if you don't put a default in for a column, the default for that column is already null. Is that not the case? And if it is not the case, why does DEFAULT NULL even result in the schema being different? TX
Re: Difference
:-) Thanks. I considered 10.10.2.0 stable, actually for me an my use it is very stable. Peter On Thursday, 31 July 2014, 19:31, Myrna van Lunteren m.v.lunte...@gmail.com wrote: Hi, 10.10.2.0 has all the *new* functionality of 10.9.1.0 and 10.10.1.0. Plus it has more bug fixes than 10.8.3.0, both because the 10.10 branch was pulled from trunk at a later time and because 10.10.2.0 was released later and thus even more fixes were back-ported. It therefore also has more possible incompatibilities to older versions. 10.8.3.0 only has the most important fixes available at the time of release back-ported, and has very few incompatibilities compared to e.g. 10.8.2. There were some plans to make a 10.9.2 at one time but that fell by the wayside. It would have replaced the 10.8.3.0. Myrna On Thu, Jul 31, 2014 at 5:41 AM, Rick Hillegas rick.hille...@oracle.com wrote: On 7/31/14 4:07 AM, Peter Ondruška wrote: Dear all, what is the difference between version 10.10.2.0 and 10.8.3.0? Or why is there 10.8.3.0 along with 10.10.2.0? Thanks Peter The Latest Official Releases tend to be the latest releases produced on the 2 most active release branches. Once we publish 10.11.1, I expect that we'll remove 10.8.3.0 from that list. Right after we produce a feature release, the list has this meaning: i) The top release is the most feature-rich distribution. ii) The second release is the most stable distribution. Hope this helps, -Rick
Difference
Dear all, what is the difference between version 10.10.2.0 and 10.8.3.0? Or why is there 10.8.3.0 along with 10.10.2.0? Thanks Peter
Re: Difference
On 7/31/14 4:07 AM, Peter Ondruška wrote: Dear all, what is the difference between version 10.10.2.0 and 10.8.3.0? Or why is there 10.8.3.0 along with 10.10.2.0? Thanks Peter The Latest Official Releases tend to be the latest releases produced on the 2 most active release branches. Once we publish 10.11.1, I expect that we'll remove 10.8.3.0 from that list. Right after we produce a feature release, the list has this meaning: i) The top release is the most feature-rich distribution. ii) The second release is the most stable distribution. Hope this helps, -Rick
Re: Difference
Hi, 10.10.2.0 has all the *new* functionality of 10.9.1.0 and 10.10.1.0. Plus it has more bug fixes than 10.8.3.0, both because the 10.10 branch was pulled from trunk at a later time and because 10.10.2.0 was released later and thus even more fixes were back-ported. It therefore also has more possible incompatibilities to older versions. 10.8.3.0 only has the most important fixes available at the time of release back-ported, and has very few incompatibilities compared to e.g. 10.8.2. There were some plans to make a 10.9.2 at one time but that fell by the wayside. It would have replaced the 10.8.3.0. Myrna On Thu, Jul 31, 2014 at 5:41 AM, Rick Hillegas rick.hille...@oracle.com wrote: On 7/31/14 4:07 AM, Peter Ondruška wrote: Dear all, what is the difference between version 10.10.2.0 and 10.8.3.0? Or why is there 10.8.3.0 along with 10.10.2.0? Thanks Peter The Latest Official Releases tend to be the latest releases produced on the 2 most active release branches. Once we publish 10.11.1, I expect that we'll remove 10.8.3.0 from that list. Right after we produce a feature release, the list has this meaning: i) The top release is the most feature-rich distribution. ii) The second release is the most stable distribution. Hope this helps, -Rick
RE: Derby/HSQLDB major performance difference
Hi, Thanks for the tip. I reran my test last night with the property set as you suggested but the output is the same. The log shows the property being used (see below) but it does not seem to make a difference. 1 ?#?#? UrProva.java at line: 219 Dbg-out variable s_dbsize [70] 2 ?#?#? UrProva.java at line: 221 Dbg-out variable s_updatesize [30] 3 ?#?#? UrProva.java at line: 223 Dbg-out variable s_selectsize [100] 4 ?#?#? UrProva.java at line: 225 Dbg-out variable s_delmodsize [10] 5 ?#?#? UrProva.java at line: 228 Dbg-out variable driver [org.apache.derby.jdbc.EmbeddedDriver] 6 ?#?#? UrProva.java at line: 230 Dbg-out variable protocol [jdbc:derby:] 7 ?#?#? UrProva.java at line: 232 Dbg-out variable dbName [/home/cl/projects/hsqldbprova/derby 7 /xx;create=true] 8 ?#?#? UrProva.java at line: 236 Dbg-out variable propfilename [db.prop] 9 Loaded the appropriate driver 10 -- listing properties -- 11 derby.system.durability=test 12 Connected to and created database /home/cl/projects/hsqldbprova/derby/xx;create=true 13 Populating db I think you have a good point here, and it is not always one needs durability so sacrificing that for speed makes sense. Any ideas as to why the property does not bite? Regards, Jeff Stuckman wrote: Hello, Did you try setting derby.system.durability=test and rerunning your benchmark? From what I understand, Derby provides hard guarantees of durability -- if there is a power outage, system crash, or disk failure anytime after your commit() call has returned, the data is guaranteed to be available when your system comes back up. To guarantee this durability, Derby needs to work around the write caching that your OS will normally perform, which reduces performance. This will cause the very long insertion period that you see. (Derby even includes support for XA (distributed) transactions, which are impossible to properly support without durability guarantees) I couldn't find any information on durability on the HSQLDB website, and from the performance results that you describe, I'm inclined to believe that HSQLDB does not make this guarantee. If you set the above property, Derby will reduce its durability guarantees and perform faster. Jeff -Original Message- From: DerbyNovice [mailto:clars...@ureason.com] Sent: Friday, March 06, 2009 12:39 PM To: derby-user@db.apache.org Subject: Derby/HSQLDB major performance difference I am using Derby as an embedded db in my swing application. Recently I decided to have a go at HSQLDB (cached tables, embedded) to see how it coped. I have written a test program which * inserts a number of records in my db with random keys * makes an index on the keys. * runs a number of select statements * updates a number of records with new random values. At the same time I measure lapse time and memory in a separate thread. I made the same run with Derby and with HSQLDB, see the two uploaded charts, with -Xmx1024m . Initially the idea was to see which db was faster, but as soon as I saw the results I realised there are other differences. The scale on the x-axis is half seconds, i 1000 is 500 seconds. The scale on the y-axis is bytes as reported by gc. Notice the difference in scale between HSQLDB and derby. I have tried to optimise the memory with HSQLDB options but it has only marginal difference and it does not change the behaviour. Observations: * HSQLDB uses a magnitude more memory than Derby. * HSQLDB does not seem to benefit from the indices. * HSQLDB is faster in total, but not to the extent the memory usage suggests. * Derby uses a very long insertion period but the select statements are very fast and memory lean * Derby manages the memory during the run, the total memory goes up AND DOWN. * Derby seems to struggle (timewise) with the inserts (the long slope initially) but breeze through the select statements which all take less than a second. The run shown uses 70 records, but smaller runs show the same behaviour. For me this makes HSQLDB useless as it would gradually eat my applications memory. Anyone trying to weigh performance benefits between db's should be aware of these very different characteristics. I'd be pleased if anyone would care to comment on the test run and maybe shed some light on the totally different characteristics seen here. I'd be happy to upload the timing tests and my program too if there is an interest. Regards, DERBY RUN http://www.nabble.com/file/p22377140/mem.gif HSQL RUN http://www.nabble.com/file/p22377140/mem.gif -- View this message in context: http://www.nabble.com/Derby-HSQLDB-major-performance-difference-tp22377140p2 2377140.html Sent from the Apache Derby Users mailing list archive at Nabble.com. -- View this message in context: http://www.nabble.com/Derby-HSQLDB-major-performance-difference
Re: Derby/HSQLDB major performance difference
DerbyNovice wrote: I am using Derby as an embedded db in my swing application. Recently I decided to have a go at HSQLDB (cached tables, embedded) to see how it coped. I have written a test program which * inserts a number of records in my db with random keys * makes an index on the keys. * runs a number of select statements * updates a number of records with new random values. At the same time I measure lapse time and memory in a separate thread. I made the same run with Derby and with HSQLDB, see the two uploaded charts, with -Xmx1024m . Initially the idea was to see which db was faster, but as soon as I saw the results I realised there are other differences. The scale on the x-axis is half seconds, i 1000 is 500 seconds. The scale on the y-axis is bytes as reported by gc. Notice the difference in scale between HSQLDB and derby. I have tried to optimise the memory with HSQLDB options but it has only marginal difference and it does not change the behaviour. Observations: * HSQLDB uses a magnitude more memory than Derby. * HSQLDB does not seem to benefit from the indices. * HSQLDB is faster in total, but not to the extent the memory usage suggests. * Derby uses a very long insertion period but the select statements are very fast and memory lean * Derby manages the memory during the run, the total memory goes up AND DOWN. * Derby seems to struggle (timewise) with the inserts (the long slope initially) but breeze through the select statements which all take less than a second. The run shown uses 70 records, but smaller runs show the same behaviour. For me this makes HSQLDB useless as it would gradually eat my applications memory. Anyone trying to weigh performance benefits between db's should be aware of these very different characteristics. I'd be pleased if anyone would care to comment on the test run and maybe shed some light on the totally different characteristics seen here. I'd be happy to upload the timing tests and my program too if there is an interest. Regards, DERBY RUN http://www.nabble.com/file/p22433093/derby.gif HSQL RUN http://www.nabble.com/file/p22433093/hsql.gif -- View this message in context: http://www.nabble.com/Derby-HSQLDB-major-performance-difference-tp22377140p22433093.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby/HSQLDB major performance difference
Good morning everyone, I am sorry about the pictures being the same. Here are the two different ones. DERBY RUN http://www.nabble.com/file/p22409058/mem.gif HSQL RUN http://www.nabble.com/file/p22377140/mem.gif -- View this message in context: http://www.nabble.com/Derby-HSQLDB-major-performance-difference-tp22377140p22409058.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby/HSQLDB major performance difference
What is the measurement unit of the X and Y axis? What does each color represent ? I see something of a legend at the top corner but can't see any colors associated to the text. -- George H george@gmail.com On Mon, Mar 9, 2009 at 8:41 AM, DerbyNovice clars...@ureason.com wrote: Good morning everyone, I am sorry about the pictures being the same. Here are the two different ones. DERBY RUN http://www.nabble.com/file/p22409058/mem.gif HSQL RUN http://www.nabble.com/file/p22377140/mem.gif -- View this message in context: http://www.nabble.com/Derby-HSQLDB-major-performance-difference-tp22377140p22409058.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby/HSQLDB major performance difference
X axis displays number of half seconds, i.e. 1000 = 500 seconds lapse time Red is total memory, blue is used memory and green is free memory as gc reports. Regards George H wrote: What is the measurement unit of the X and Y axis? What does each color represent ? I see something of a legend at the top corner but can't see any colors associated to the text. -- George H george@gmail.com On Mon, Mar 9, 2009 at 8:41 AM, DerbyNovice clars...@ureason.com wrote: Good morning everyone, I am sorry about the pictures being the same. Here are the two different ones. DERBY RUN http://www.nabble.com/file/p22409058/mem.gif HSQL RUN http://www.nabble.com/file/p22377140/mem.gif -- View this message in context: http://www.nabble.com/Derby-HSQLDB-major-performance-difference-tp22377140p22409058.html Sent from the Apache Derby Users mailing list archive at Nabble.com. -- View this message in context: http://www.nabble.com/Derby-HSQLDB-major-performance-difference-tp22377140p22409260.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
RE: Derby/HSQLDB major performance difference
Hello, Did you try setting derby.system.durability=test and rerunning your benchmark? From what I understand, Derby provides hard guarantees of durability -- if there is a power outage, system crash, or disk failure anytime after your commit() call has returned, the data is guaranteed to be available when your system comes back up. To guarantee this durability, Derby needs to work around the write caching that your OS will normally perform, which reduces performance. This will cause the very long insertion period that you see. (Derby even includes support for XA (distributed) transactions, which are impossible to properly support without durability guarantees) I couldn't find any information on durability on the HSQLDB website, and from the performance results that you describe, I'm inclined to believe that HSQLDB does not make this guarantee. If you set the above property, Derby will reduce its durability guarantees and perform faster. Jeff -Original Message- From: DerbyNovice [mailto:clars...@ureason.com] Sent: Friday, March 06, 2009 12:39 PM To: derby-user@db.apache.org Subject: Derby/HSQLDB major performance difference I am using Derby as an embedded db in my swing application. Recently I decided to have a go at HSQLDB (cached tables, embedded) to see how it coped. I have written a test program which * inserts a number of records in my db with random keys * makes an index on the keys. * runs a number of select statements * updates a number of records with new random values. At the same time I measure lapse time and memory in a separate thread. I made the same run with Derby and with HSQLDB, see the two uploaded charts, with -Xmx1024m . Initially the idea was to see which db was faster, but as soon as I saw the results I realised there are other differences. The scale on the x-axis is half seconds, i 1000 is 500 seconds. The scale on the y-axis is bytes as reported by gc. Notice the difference in scale between HSQLDB and derby. I have tried to optimise the memory with HSQLDB options but it has only marginal difference and it does not change the behaviour. Observations: * HSQLDB uses a magnitude more memory than Derby. * HSQLDB does not seem to benefit from the indices. * HSQLDB is faster in total, but not to the extent the memory usage suggests. * Derby uses a very long insertion period but the select statements are very fast and memory lean * Derby manages the memory during the run, the total memory goes up AND DOWN. * Derby seems to struggle (timewise) with the inserts (the long slope initially) but breeze through the select statements which all take less than a second. The run shown uses 70 records, but smaller runs show the same behaviour. For me this makes HSQLDB useless as it would gradually eat my applications memory. Anyone trying to weigh performance benefits between db's should be aware of these very different characteristics. I'd be pleased if anyone would care to comment on the test run and maybe shed some light on the totally different characteristics seen here. I'd be happy to upload the timing tests and my program too if there is an interest. Regards, DERBY RUN http://www.nabble.com/file/p22377140/mem.gif HSQL RUN http://www.nabble.com/file/p22377140/mem.gif -- View this message in context: http://www.nabble.com/Derby-HSQLDB-major-performance-difference-tp22377140p2 2377140.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby/HSQLDB major performance difference
I've used hsqldb quite a bit. It's basically designed to read all the data into memory. In that regard, while it does support a SQL interface, it's really lacking in scalability. It's great for prototyping On Mar 8, 2009, at 9:58 PM, Jeff Stuckman stuck...@umd.edu wrote: Hello, Did you try setting derby.system.durability=test and rerunning your benchmark? From what I understand, Derby provides hard guarantees of durability -- if there is a power outage, system crash, or disk failure anytime after your commit() call has returned, the data is guaranteed to be available when your system comes back up. To guarantee this durability, Derby needs to work around the write caching that your OS will normally perform, which reduces performance. This will cause the very long insertion period that you see. (Derby even includes support for XA (distributed) transactions, which are impossible to properly support without durability guarantees) I couldn't find any information on durability on the HSQLDB website, and from the performance results that you describe, I'm inclined to believe that HSQLDB does not make this guarantee. If you set the above property, Derby will reduce its durability guarantees and perform faster. Jeff -Original Message- From: DerbyNovice [mailto:clars...@ureason.com] Sent: Friday, March 06, 2009 12:39 PM To: derby-user@db.apache.org Subject: Derby/HSQLDB major performance difference I am using Derby as an embedded db in my swing application. Recently I decided to have a go at HSQLDB (cached tables, embedded) to see how it coped. I have written a test program which * inserts a number of records in my db with random keys * makes an index on the keys. * runs a number of select statements * updates a number of records with new random values. At the same time I measure lapse time and memory in a separate thread. I made the same run with Derby and with HSQLDB, see the two uploaded charts, with -Xmx1024m . Initially the idea was to see which db was faster, but as soon as I saw the results I realised there are other differences. The scale on the x-axis is half seconds, i 1000 is 500 seconds. The scale on the y-axis is bytes as reported by gc. Notice the difference in scale between HSQLDB and derby. I have tried to optimise the memory with HSQLDB options but it has only marginal difference and it does not change the behaviour. Observations: * HSQLDB uses a magnitude more memory than Derby. * HSQLDB does not seem to benefit from the indices. * HSQLDB is faster in total, but not to the extent the memory usage suggests. * Derby uses a very long insertion period but the select statements are very fast and memory lean * Derby manages the memory during the run, the total memory goes up AND DOWN. * Derby seems to struggle (timewise) with the inserts (the long slope initially) but breeze through the select statements which all take less than a second. The run shown uses 70 records, but smaller runs show the same behaviour. For me this makes HSQLDB useless as it would gradually eat my applications memory. Anyone trying to weigh performance benefits between db's should be aware of these very different characteristics. I'd be pleased if anyone would care to comment on the test run and maybe shed some light on the totally different characteristics seen here. I'd be happy to upload the timing tests and my program too if there is an interest. Regards, DERBY RUN http://www.nabble.com/file/p22377140/mem.gif HSQL RUN http://www.nabble.com/file/p22377140/mem.gif -- View this message in context: http://www.nabble.com/Derby-HSQLDB-major-performance-difference-tp22377140p2 2377140.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Derby/HSQLDB major performance difference
I am using Derby as an embedded db in my swing application. Recently I decided to have a go at HSQLDB (cached tables, embedded) to see how it coped. I have written a test program which * inserts a number of records in my db with random keys * makes an index on the keys. * runs a number of select statements * updates a number of records with new random values. At the same time I measure lapse time and memory in a separate thread. I made the same run with Derby and with HSQLDB, see the two uploaded charts, with -Xmx1024m . Initially the idea was to see which db was faster, but as soon as I saw the results I realised there are other differences. The scale on the x-axis is half seconds, i 1000 is 500 seconds. The scale on the y-axis is bytes as reported by gc. Notice the difference in scale between HSQLDB and derby. I have tried to optimise the memory with HSQLDB options but it has only marginal difference and it does not change the behaviour. Observations: * HSQLDB uses a magnitude more memory than Derby. * HSQLDB does not seem to benefit from the indices. * HSQLDB is faster in total, but not to the extent the memory usage suggests. * Derby uses a very long insertion period but the select statements are very fast and memory lean * Derby manages the memory during the run, the total memory goes up AND DOWN. * Derby seems to struggle (timewise) with the inserts (the long slope initially) but breeze through the select statements which all take less than a second. The run shown uses 70 records, but smaller runs show the same behaviour. For me this makes HSQLDB useless as it would gradually eat my applications memory. Anyone trying to weigh performance benefits between db's should be aware of these very different characteristics. I'd be pleased if anyone would care to comment on the test run and maybe shed some light on the totally different characteristics seen here. I'd be happy to upload the timing tests and my program too if there is an interest. Regards, DERBY RUN http://www.nabble.com/file/p22377140/mem.gif HSQL RUN http://www.nabble.com/file/p22377140/mem.gif -- View this message in context: http://www.nabble.com/Derby-HSQLDB-major-performance-difference-tp22377140p22377140.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby/HSQLDB major performance difference
il 06/03/2009 18.38, Scrive DerbyNovice 38146800: [...] I'd be pleased if anyone would care to comment on the test run and maybe shed some light on the totally different characteristics seen here. I'd be happy to upload the timing tests and my program too if there is an interest. Regards, DERBY RUN http://www.nabble.com/file/p22377140/mem.gif HSQL RUN http://www.nabble.com/file/p22377140/mem.gif I'd would be very courios to see both, but appears that mem.gif and mem.gif could be the same image.
Re: Statement/PreparedStatement performance difference and problem
-04-07 15:50:06.788
End Execution Timestamp : 2008-04-07 15:50:09.95
Statement Execution Plan Text:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 46372
Rows filtered = 46364
restriction = true
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 1
next time (milliseconds) = 3068
close time (milliseconds) = 0
restriction time (milliseconds) = 117
projection time (milliseconds) = 0
optimizer estimated row count: 505.06
optimizer estimated cost:56292.47
Source result set:
Index Row to Base Row ResultSet for UDEVENTDATA:
Number of opens = 1
Rows seen = 46372
Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17, 18, 19, 20}
constructor time (milliseconds) = 0
open time (milliseconds) = 1
next time (milliseconds) = 2751
close time (milliseconds) = 25
optimizer estimated row count: 505.06
optimizer estimated cost:56292.47
Index Scan ResultSet for UDEVENTDATA using index
UDSOFTOBJECTDATA_DOMAINKEY_IDX_UDEVENTDATA at read committed isolation
level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 46372
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 3
next time (milliseconds) = 417
close time (milliseconds) = 25
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=1
Number of pages visited=424
Number of rows qualified=46372
Number of rows visited=46374
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).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 505.06
optimizer estimated cost:56292.47
OUPUT WITH ALTERNATIVE 2
LOOP NO 0
?#?#? SimpleApp2.java at line: 235 Dbg-out variable elapsed NEXT [36]
Counted 8 in resultset
?#?#? SimpleApp2.java at line: 235 Dbg-out variable elapsed SELECT [0]
Query Plan: Statement Name:
null
Statement Text:
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1)
Parse Time: 1
Bind Time: 0
Optimize Time: 0
Generate Time: 3
Compile Time: 4
Execute Time: -1207579584105
Begin Compilation Timestamp : 2008-04-07 15:46:24.1
End Compilation Timestamp : 2008-04-07 15:46:24.104
Begin Execution Timestamp : 2008-04-07 15:46:24.105
End Execution Timestamp : 2008-04-07 15:46:24.105
Statement Execution Plan Text:
null
QUESTION
Why cannot the prepared statement figure out how to use the indices
when
statements can, i.e. that domainkey should not be scanned ?
How can you make the prepared statement use the same execution plan as
the statement so as to return the same type of performance?
The problem seems to be Derby specific as MSQL returns adequate
performance for ALT 1.
Thanks in advance,
--
Thomas Nielsen
--
View this message in context:
http://www.nabble.com/Statement-PreparedStatement-performance-difference-and-problem-tp16537511p16763534.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Statement/PreparedStatement performance difference and problem
DerbyNovice skrev:
Hi,
Sorry to nag you, but do you think you could please have a look a my
small test demonstrating the problem?
Hello,
I'm wondering if you are seeing the same problem as described in DERBY-2572.
(https://issues.apache.org/jira/browse/DERBY-2752)
Can you have a look?
At least one workaround is mentioned, and it would be nice if you could
add a comment there saying you are facing the same problem.
regards,
--
Kristian
Thanks in advance
Thomas Nielsen - Sun Microsystems - Trondheim, Norway wrote:
Hi,
I haven't looked at this in detail, but can you please confirm that the
following is correct:
1) The query you run in alt. 2 is not identical to alt 1 - there's an
extra AND in the WHERE clause, making it an illegal query. Is this just
a copy-paste blurp?
2) The alt. 2 output of Statement text isn't the SELECT but rather a
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1). Is this correct? If so
are you comparing the timing of a SELECT to a CALL? Or am I missing
something?
As Knut said, the optimizer sometimes does make bad decisions...
Cheers,
Thomas
DerbyNovice wrote:
PROBLEM STATEMENT
-
ALTERNATIVE 1 uses a PreparedStatement with parameters.
ALTERNATIVE 2 mimics a Statement (and indeed a Statement returns the
same performance)
...
ALTERNATIVE 1
1ps = conn.prepareStatement(SELECT * From SA.UDEVENTDATA
WHERE
DomainKeyId = ? AND CreationTime ? AND Name ? );
END 1
...
ALTERNATIVE 1
1ps.setString(3,(String)s2);
1ps.setString(1,d46chez0v8cdg-c4m);
1ps.setString(2,1200269947000);
1
END 1
ALTERNATIVE 2
2String sels = SELECT * From SA.UDEVENTDATA WHERE AND
DomainKeyId = 'd46chez0v8cdg-c4m' AND CreationTime 1200269947000 AND
Name
'+s2+' ;
2 ps = conn.prepareStatement(sels);
END 2
startStat();
beginTiming_ = java.lang.System.currentTimeMillis ();
rs = ps.executeQuery();
endTiming_ = java.lang.System.currentTimeMillis ();
elapsed = endTiming_ - beginTiming_;
System.out.println( ?#?#? SimpleApp2.java at line: 235
Dbg-out
variable elapsed SELECT [+elapsed+] );
count = 0;
beginTiming_ = java.lang.System.currentTimeMillis ();
while(rs.next())
{
//do something with the result set
for( int i= 1; i = cols ; i++)
{
Object o = getData((String)vclass.elementAt(i-1),
rs,
i);
} /* end of for i */
count++;
}
endTiming_ = java.lang.System.currentTimeMillis ();
printStat();
elapsed = endTiming_ - beginTiming_;
System.out.println( LOOP NO +j );
System.out.println( ?#?#? SimpleApp2.java at line: 235
Dbg-out
variable elapsed NEXT [+elapsed+] );
System.out.println(Counted +count+ in resultset);
}
OUPUT WITH ALTERNATIVE 1
LOOP NO 0
?#?#? SimpleApp2.java at line: 235 Dbg-out variable elapsed NEXT [3092]
Counted 8 in resultset
?#?#? SimpleApp2.java at line: 235 Dbg-out variable elapsed SELECT [41]
Query Plan: Statement Name:
null
Statement Text:
SELECT * From SA.UDEVENTDATA WHERE DomainKeyId = ? AND
CreationTime ? AND Name ?
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 3069
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : 2008-04-07 15:50:06.788
End Execution Timestamp : 2008-04-07 15:50:09.95
Statement Execution Plan Text:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 46372
Rows filtered = 46364
restriction = true
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 1
next time (milliseconds) = 3068
close time (milliseconds) = 0
restriction time (milliseconds) = 117
projection time (milliseconds) = 0
optimizer estimated row count: 505.06
optimizer estimated cost:56292.47
Source result set:
Index Row to Base Row ResultSet for UDEVENTDATA:
Number of opens = 1
Rows seen = 46372
Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17, 18, 19, 20}
constructor time (milliseconds) = 0
open time (milliseconds) = 1
next time (milliseconds) = 2751
close time (milliseconds) = 25
optimizer estimated row count: 505.06
optimizer estimated cost:56292.47
Index Scan ResultSet for UDEVENTDATA using index
UDSOFTOBJECTDATA_DOMAINKEY_IDX_UDEVENTDATA at read
Re: Statement/PreparedStatement performance difference and problem
time (milliseconds) = 0 open time (milliseconds) = 3 next time (milliseconds) = 417 close time (milliseconds) = 25 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=1 Number of pages visited=424 Number of rows qualified=46372 Number of rows visited=46374 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). Ordered null semantics on the following columns: qualifiers: None optimizer estimated row count: 505.06 optimizer estimated cost:56292.47 OUPUT WITH ALTERNATIVE 2 LOOP NO 0 ?#?#? SimpleApp2.java at line: 235 Dbg-out variable elapsed NEXT [36] Counted 8 in resultset ?#?#? SimpleApp2.java at line: 235 Dbg-out variable elapsed SELECT [0] Query Plan: Statement Name: null Statement Text: CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1) Parse Time: 1 Bind Time: 0 Optimize Time: 0 Generate Time: 3 Compile Time: 4 Execute Time: -1207579584105 Begin Compilation Timestamp : 2008-04-07 15:46:24.1 End Compilation Timestamp : 2008-04-07 15:46:24.104 Begin Execution Timestamp : 2008-04-07 15:46:24.105 End Execution Timestamp : 2008-04-07 15:46:24.105 Statement Execution Plan Text: null QUESTION Why cannot the prepared statement figure out how to use the indices when statements can, i.e. that domainkey should not be scanned ? How can you make the prepared statement use the same execution plan as the statement so as to return the same type of performance? The problem seems to be Derby specific as MSQL returns adequate performance for ALT 1. Thanks in advance, -- Thomas Nielsen -- View this message in context: http://www.nabble.com/Statement-PreparedStatement-performance-difference-and-problem-tp16537511p16722575.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Statement/PreparedStatement performance difference and problem
Thanks Knut, I don't think this is the problem here as there has been no updates, the same happens if you create the db from scratch and run the program. Thanks anyway, I will look at the mechanism in play with the statistics. Cheers Knut Anders Hatlen wrote: DerbyNovice [EMAIL PROTECTED] writes: Why is the prepared statement slower than the unprepared statement? BACKGROUND -- DB as follows: CREATE TABLE SA.UDEVENTDATA ( ID VARCHAR(100) NOT NULL, DOMAINKEYID VARCHAR(100), CREATIONTIME BIGINT NOT NULL, NAME VARCHAR(32672), *** AND MORE COLUMNS NOT USED IN THIS EXAMPLE ); with indices : CREATE INDEX SA.UDSOFTOBJECTDATA_CREATIONTIME_IDX_UDEVENTDATA ON SA.UDEVENTDATA (CREATIONTIME); CREATE INDEX SA.UDSOFTOBJECTDATA_DOMAINKEY_IDX_UDEVENTDATA ON SA.UDEVENTDATA (DOMAINKEYID); CREATE INDEX SA.UDSOFTOBJECTDATA_NAME_IDX_UDEVENTDATA ON SA.UDEVENTDATA (NAME); and runs as embedded. DB contains 46000 records with the following properties DOMAINKEY are 98% same. NAMEs are all different. CREATIONTIME 82% different. Hi, Derby's optimizer sometimes makes bad decisions because the cardinality statistics are outdated. You could see if this thread helps you: http://www.nabble.com/Re%3A-FW%3A-Advice-on-*very*-badly-performing-query-p14140691.html -- Knut Anders -- View this message in context: http://www.nabble.com/Statement-PreparedStatement-performance-difference-and-problem-tp16537511p16580998.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Statement/PreparedStatement performance difference and problem
In the fresh db case, do you create your indexes before or after the data has been inserted? From you java repro I'm guessing indexes first, then inserts? If I'm not mistaken there's still a possibility for experiencing outdated statistics when done in this order (as inserts are updates to the cached/stored data). Someone please correct me if I'm wrong. BR, Thomas DerbyNovice wrote: Thanks Knut, I don't think this is the problem here as there has been no updates, the same happens if you create the db from scratch and run the program. Thanks anyway, I will look at the mechanism in play with the statistics. Cheers Knut Anders Hatlen wrote: DerbyNovice [EMAIL PROTECTED] writes: Why is the prepared statement slower than the unprepared statement? BACKGROUND -- DB as follows: CREATE TABLE SA.UDEVENTDATA ( ID VARCHAR(100) NOT NULL, DOMAINKEYID VARCHAR(100), CREATIONTIME BIGINT NOT NULL, NAME VARCHAR(32672), *** AND MORE COLUMNS NOT USED IN THIS EXAMPLE ); with indices : CREATE INDEX SA.UDSOFTOBJECTDATA_CREATIONTIME_IDX_UDEVENTDATA ON SA.UDEVENTDATA (CREATIONTIME); CREATE INDEX SA.UDSOFTOBJECTDATA_DOMAINKEY_IDX_UDEVENTDATA ON SA.UDEVENTDATA (DOMAINKEYID); CREATE INDEX SA.UDSOFTOBJECTDATA_NAME_IDX_UDEVENTDATA ON SA.UDEVENTDATA (NAME); and runs as embedded. DB contains 46000 records with the following properties DOMAINKEY are 98% same. NAMEs are all different. CREATIONTIME 82% different. Hi, Derby's optimizer sometimes makes bad decisions because the cardinality statistics are outdated. You could see if this thread helps you: http://www.nabble.com/Re%3A-FW%3A-Advice-on-*very*-badly-performing-query-p14140691.html -- Knut Anders -- Thomas Nielsen
Re: Statement/PreparedStatement performance difference and problem
Hi,
I haven't looked at this in detail, but can you please confirm that the
following is correct:
1) The query you run in alt. 2 is not identical to alt 1 - there's an
extra AND in the WHERE clause, making it an illegal query. Is this just
a copy-paste blurp?
2) The alt. 2 output of Statement text isn't the SELECT but rather a
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1). Is this correct? If so
are you comparing the timing of a SELECT to a CALL? Or am I missing
something?
As Knut said, the optimizer sometimes does make bad decisions...
Cheers,
Thomas
DerbyNovice wrote:
PROBLEM STATEMENT
-
ALTERNATIVE 1 uses a PreparedStatement with parameters.
ALTERNATIVE 2 mimics a Statement (and indeed a Statement returns the
same performance)
...
ALTERNATIVE 1
1ps = conn.prepareStatement(SELECT * From SA.UDEVENTDATA WHERE
DomainKeyId = ? AND CreationTime ? AND Name ? );
END 1
...
ALTERNATIVE 1
1ps.setString(3,(String)s2);
1ps.setString(1,d46chez0v8cdg-c4m);
1ps.setString(2,1200269947000);
1
END 1
ALTERNATIVE 2
2String sels = SELECT * From SA.UDEVENTDATA WHERE AND
DomainKeyId = 'd46chez0v8cdg-c4m' AND CreationTime 1200269947000 AND Name
'+s2+' ;
2 ps = conn.prepareStatement(sels);
END 2
startStat();
beginTiming_ = java.lang.System.currentTimeMillis ();
rs = ps.executeQuery();
endTiming_ = java.lang.System.currentTimeMillis ();
elapsed = endTiming_ - beginTiming_;
System.out.println( ?#?#? SimpleApp2.java at line: 235 Dbg-out
variable elapsed SELECT [+elapsed+] );
count = 0;
beginTiming_ = java.lang.System.currentTimeMillis ();
while(rs.next())
{
//do something with the result set
for( int i= 1; i = cols ; i++)
{
Object o = getData((String)vclass.elementAt(i-1),
rs,
i);
} /* end of for i */
count++;
}
endTiming_ = java.lang.System.currentTimeMillis ();
printStat();
elapsed = endTiming_ - beginTiming_;
System.out.println( LOOP NO +j );
System.out.println( ?#?#? SimpleApp2.java at line: 235 Dbg-out
variable elapsed NEXT [+elapsed+] );
System.out.println(Counted +count+ in resultset);
}
OUPUT WITH ALTERNATIVE 1
LOOP NO 0
?#?#? SimpleApp2.java at line: 235 Dbg-out variable elapsed NEXT [3092]
Counted 8 in resultset
?#?#? SimpleApp2.java at line: 235 Dbg-out variable elapsed SELECT [41]
Query Plan: Statement Name:
null
Statement Text:
SELECT * From SA.UDEVENTDATA WHERE DomainKeyId = ? AND
CreationTime ? AND Name ?
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 3069
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : 2008-04-07 15:50:06.788
End Execution Timestamp : 2008-04-07 15:50:09.95
Statement Execution Plan Text:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 46372
Rows filtered = 46364
restriction = true
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 1
next time (milliseconds) = 3068
close time (milliseconds) = 0
restriction time (milliseconds) = 117
projection time (milliseconds) = 0
optimizer estimated row count: 505.06
optimizer estimated cost:56292.47
Source result set:
Index Row to Base Row ResultSet for UDEVENTDATA:
Number of opens = 1
Rows seen = 46372
Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17, 18, 19, 20}
constructor time (milliseconds) = 0
open time (milliseconds) = 1
next time (milliseconds) = 2751
close time (milliseconds) = 25
optimizer estimated row count: 505.06
optimizer estimated cost:56292.47
Index Scan ResultSet for UDEVENTDATA using index
UDSOFTOBJECTDATA_DOMAINKEY_IDX_UDEVENTDATA at read committed isolation
level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 46372
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 3
next time (milliseconds) = 417
close time (milliseconds) = 25
next time in milliseconds/row = 0
scan information:
Bit set of columns
Re: Statement/PreparedStatement performance difference and problem
DerbyNovice [EMAIL PROTECTED] writes: Why is the prepared statement slower than the unprepared statement? BACKGROUND -- DB as follows: CREATE TABLE SA.UDEVENTDATA ( ID VARCHAR(100) NOT NULL, DOMAINKEYID VARCHAR(100), CREATIONTIME BIGINT NOT NULL, NAME VARCHAR(32672), *** AND MORE COLUMNS NOT USED IN THIS EXAMPLE ); with indices : CREATE INDEX SA.UDSOFTOBJECTDATA_CREATIONTIME_IDX_UDEVENTDATA ON SA.UDEVENTDATA (CREATIONTIME); CREATE INDEX SA.UDSOFTOBJECTDATA_DOMAINKEY_IDX_UDEVENTDATA ON SA.UDEVENTDATA (DOMAINKEYID); CREATE INDEX SA.UDSOFTOBJECTDATA_NAME_IDX_UDEVENTDATA ON SA.UDEVENTDATA (NAME); and runs as embedded. DB contains 46000 records with the following properties DOMAINKEY are 98% same. NAMEs are all different. CREATIONTIME 82% different. Hi, Derby's optimizer sometimes makes bad decisions because the cardinality statistics are outdated. You could see if this thread helps you: http://www.nabble.com/Re%3A-FW%3A-Advice-on-*very*-badly-performing-query-p14140691.html -- Knut Anders
Re: Statement/PreparedStatement performance difference and problem
))
{
return rs.getLong(col);
}
else if (dtype.equals(java.sql.Clob))
{
return rs.getClob(col);
}
else
{
System.out.println(Unknown type +dtype);
return new String(xx);
}
}
catch (SQLException sqle)
{
printSQLException(sqle);
}
return null;
}
private String randomstring1(int len)
{
Random RNG = new Random();
StringBuffer sb = new StringBuffer();
for( int i= 0; i len ; i++)
{
char c = (char)(RNG.nextInt(Character.MAX_VALUE + 1));
sb.append(c);
}
return sb.toString();
}
private String randomstring(int len)
{
Random RNG = new Random();
StringBuffer sb = new StringBuffer();
for( int i= 0; i len ; i++)
{
int ri = RNG.nextInt(RANSTR.length());
sb.append(RANSTR.charAt(ri));
}
return sb.toString();
}
private void startStat()
{
try
{
Statement s = conn.createStatement();
s.executeUpdate(CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1));
s.executeUpdate(CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1));
}
catch (SQLException sqle)
{
printSQLException(sqle);
}
}
private void printStat()
{
try
{
// retrieve query plan and run-time statistics
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery(VALUES
SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());
while(rs.next())
{
String str = rs.getString(1);
System.out.println(Query Plan: + str);
}
}
catch (SQLException sqle)
{
printSQLException(sqle);
}
}
/**
* p
* Starts the actual demo activities. This includes loading the correct
* JDBC driver, creating a database by making a connection to Derby,
* creating a table in the database, and inserting, updating and
retreiving
* some data. Some of the retreived data is then verified (compared)
against
* the expected results. Finally, the table is deleted and, if the
embedded
* framework is used, the database is shut down./p
* p
* Generally, when using a client/server framework, other clients may be
* (or want to be) connected to the database, so you should be careful
about
* doing shutdown unless you know that noone else needs to access the
* database until it is rebooted. That is why this demo will not shut
down
* the database unless it is running Derby embedded./p
*
* @param args - Optional argument specifying which framework or JDBC
driver
*to use to connect to Derby. Default is the embedded framework,
*see the codemain()/code method for details.
* @see #main(String[])
*/
void go(String[] args)
{
/* parse the arguments to determine which framework is desired*/
parseArguments(args);
System.out.println(UrProva starting in + framework + mode);
/* load the desired JDBC driver */
loadDriver();
/* We will be using Statement and PreparedStatement objects for
* executing SQL. These objects, as well as Connections and
ResultSets,
* are resources that should be released explicitly after use, hence
* the try-catch-finally pattern used below.
* We are storing the Statement and Prepared statement object
references
* in an array list for convenience.
*/
/* This ArrayList usage may cause a warning when compiling this class
* with a compiler for J2SE 5.0 or newer. We are not using generics
* because we want the source to support J2SE 1.4.2 environments. */
ArrayList statements = new ArrayList(); // list of Statements,
PreparedStatements
PreparedStatement psInsert = null;
PreparedStatement psUpdate = null;
Statement s = null;
ResultSet rs = null;
try
{
Properties props = new Properties(); // connection properties
// providing a user name and password is optional in the
embedded
// and derbyclient frameworks
//props.put(user, user1);
//props.put(password, user1);
// No difference props.put(derby.storage.pageCacheSize,
5000);
props.put(derby.language.logStatementText, true);
props.put(derby.language.logQueryPlan, true);
/* By default, the schema APP will be used when no username is
* provided.
* Otherwise, the schema name is the same as the user name (in
this
* case user1 or USER1
Statement/PreparedStatement performance difference and problem
estimated row count: 505.06 optimizer estimated cost:56292.47 Index Scan ResultSet for UDEVENTDATA using index UDSOFTOBJECTDATA_DOMAINKEY_IDX_UDEVENTDATA at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 46372 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 3 next time (milliseconds) = 417 close time (milliseconds) = 25 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=1 Number of pages visited=424 Number of rows qualified=46372 Number of rows visited=46374 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). Ordered null semantics on the following columns: qualifiers: None optimizer estimated row count: 505.06 optimizer estimated cost:56292.47 OUPUT WITH ALTERNATIVE 2 LOOP NO 0 ?#?#? SimpleApp2.java at line: 235 Dbg-out variable elapsed NEXT [36] Counted 8 in resultset ?#?#? SimpleApp2.java at line: 235 Dbg-out variable elapsed SELECT [0] Query Plan: Statement Name: null Statement Text: CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1) Parse Time: 1 Bind Time: 0 Optimize Time: 0 Generate Time: 3 Compile Time: 4 Execute Time: -1207579584105 Begin Compilation Timestamp : 2008-04-07 15:46:24.1 End Compilation Timestamp : 2008-04-07 15:46:24.104 Begin Execution Timestamp : 2008-04-07 15:46:24.105 End Execution Timestamp : 2008-04-07 15:46:24.105 Statement Execution Plan Text: null QUESTION Why cannot the prepared statement figure out how to use the indices when statements can, i.e. that domainkey should not be scanned ? How can you make the prepared statement use the same execution plan as the statement so as to return the same type of performance? The problem seems to be Derby specific as MSQL returns adequate performance for ALT 1. Thanks in advance, -- View this message in context: http://www.nabble.com/Statement-PreparedStatement-performance-difference-and-problem-tp16537511p16537511.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Difference between two timestamp fields
Dmitri Pissarenko wrote:
Hello!
SELECT APP.MyTable.startTime, {fn TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,
APP.MyTable.startTime, APP.MyTable.endTime)}
FROM APP.MyTable
WHERE APP.MyTable IS NOT NULL
Should this be
WHERE APP.MyTable.startTime IS NOT NULL
?
Thanks for the hint!
Now I'm getting this exception:
SQL State = 22003 SQL Code = 3 SQL Message = The resulting value
is outside the range for the data type INTEGER. Exception message =
java.sql.SQLDataException: The resulting value is outside the range
for the data type INTEGER.
What version are you using?
I'm using the version, which is delivered together with Java.
hmm... Looks like https://issues.apache.org/jira/browse/DERBY-2386
Fixed in 10.3 which is our upcoming release. Even though the change
has a very slight chance of affecting existing applications, I think it
would be safe to port to the 10.2 branch, since the function is not so
useful without the change. Let us know on derby-dev if you need help
doing that.
Kathey
Re: Difference between two timestamp fields
Hello!
I found the error. This query is correct (SQL_TSI_SECOND instead of
SQL_TSI_FRAC_SECOND):
SELECT APP.MyTable.startTime, {fn TIMESTAMPDIFF(SQL_TSI_SECOND,
APP.MyTable.startTime, APP.MyTable.endTime)}
FROM APP.MyTable
WHERE APP.MyTable.startTime IS NOT NULL
Thanks again.
Dmitri Pissarenko
--
http://www.xing.com/profile/Dmitri_Pissarenko
http://dapissarenko.blogspot.com/
Re: Difference between two timestamp fields
Dmitri Pissarenko wrote:
Hello!
I need to calculate the difference between two timestamp fields in
seconds. I tried this query:
SELECT APP.MyTable.startTime, {fn TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,
APP.MyTable.startTime, APP.MyTable.endTime)}
FROM APP.MyTable
WHERE APP.MyTable IS NOT NULL
Should this be
WHERE APP.MyTable.startTime IS NOT NULL
?
It seems like you should have gotten a message that column APP.MyTable
does not exist, not the one below. Otherwise your statement looks ok to
me. What version are you using?
but it doesn't work, I'm getting the error
SQL State = 42Y07 SQL Code = 3 SQL Message = Schema 'SYSTEM' does
not exist Exception message = java.sql.SQLSyntaxErrorException: Schema
'SYSTEM' does not exist
How can I calculate the difference between two timestamp fields?
TIA
Dmitri Pissarenko
Difference between view and select statement
I have a table of users, one of which has '' (empty string) as the username. I issue the following select statement: SELECT NameFormat(surname,initials) AS name, users.username AS link, email, allowance, CASE WHEN passwords.username IS NULL THEN '' ELSE 'Y' END AS local, surname, initials FROMusers LEFT OUTER JOIN passwords ON users.username=passwords.username WHERE users.username''; This works fine, listing 171 users (all except the one with the empty string as the username). Then I try making this a view: CREATE VIEW user_list AS [the same select statement as above]; Now when I do SELECT * FROM user_list I get 172 results, and this includes the one with the empty string as its name. Anyone got any ideas why this should be so? The only way to get the expected answer is to say SELECT * FROM user_list WHERE link'', which seems a bit perverse to me... -- John English | mailto:[EMAIL PROTECTED] Senior Lecturer | http://www.it.bton.ac.uk/staff/je School of Computing MIS | Those who don't know their history University of Brighton| are condemned to relive it (Santayana) --
Re: Difference between view and select statement
English John wrote: Anyone got any ideas why this should be so? The only way to get the expected answer is to say SELECT * FROM user_list WHERE link'', which seems a bit perverse to me... As a workaround, I've used CHAR(0) instead of '' for this, and that works fine. I'd still like to know what's going on here, though... -- John English | mailto:[EMAIL PROTECTED] Senior Lecturer | http://www.it.bton.ac.uk/staff/je School of Computing MIS | Those who don't know their history University of Brighton| are condemned to relive it (Santayana) --
defaultAccessMode defaultConnectionMode difference?
Hi, I am wondering what is the difference between using derby.database.defaultAccessMode and derby.database.defaultConnectionMode for authenticating users? Thanks in advance, Gerald
