Re: [h2] unable to write: the database is read only after system crash
Hi, I'm sorry, but I'm not familiar with JBoss. I know that the 2-phase-commit implementation of H2 is currently incomplete, and probably this is the root cause of the problem. What H2 can't do currently is: after a crash, in the recovery phase, H2 doesn't allow to start a new transaction until all in-doubt transactions are either rolled back or committed. I hope I can implement this missing features in the future, but demand for this feature is relatively low so far. Unfortunately with the current storage it's not possible to implement it (at least not easily); it will have to wait until the MVStore storage is ready. Note: having recovery is also not 'a must' for me actually, so would there be a way to proceed from this situation, somehow skipping recovery? Well, you could roll back all in-doubt transactions. This would be a manual step however: SELECT * FROM INFORMATION_SCHEMA.IN_DOUBT ; and then for each transaction, run: ROLLBACK TRANSACTION name (or COMMIT TRANSACTION name). Then re-open the database (close all connections or run shutdown). Regards, Thomas -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
Re: [h2] Misleading error when parsing an invalid INNER JOIN query missing the comma before last field reference
Hi,
Yes, that's weird. I found the problem now. The test case:
create table test(a int, b int);
select t.a, t.b t.c from test t;
The parser thinks the statement finishes at
select t.a, t.b t
and then doesn't find the column. I will try to fix the parser.
Thanks for reporting!
Regards,
Thomas
On Tuesday, October 1, 2013, Davide Cavestro wrote:
Trying to execute an invalid INNER JOIN query that misses the comma
before the declaration of the last field to be extracted, the engine
complains
Column 1st column name not found;
http://localhost:8082/query.do?jsessionid=a88e857137898e83ce1e2c0fb3f00760#
I feel this is a minor issue: the SQL is really invalid, but *the error
message is misleading*. That could become a problem for fairly complex
queries, where one could waste time investigating on the wrong direction.
Follows a reproducible test case.
CREATE TABLE foo (ID int, FIELDX int);
CREATE TABLE bar (ID int, FIELDY int);
SELECT
f.id,
f.fieldx
b.fieldy
FROM foo f INNER JOIN bar b ON (f.id=b.id)
(please note the missing comma before b.fieldy)
This example produces the following error message using h2 v. 1.3.173
Column F.ID not found; SQL statement:
SELECT
f.id,
f.fieldx
b.fieldy
FROM foo f INNER JOIN bar b ON (f.id=b.id)
[42122-170]http://localhost:8082/query.do?jsessionid=a88e857137898e83ce1e2c0fb3f00760#
42S22/42122
--
You received this message because you are subscribed to the Google Groups
H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send an
email to h2-database+unsubscr...@googlegroups.com javascript:_e({},
'cvml', 'h2-database%2bunsubscr...@googlegroups.com');.
To post to this group, send email to
h2-database@googlegroups.comjavascript:_e({}, 'cvml',
'h2-database@googlegroups.com');
.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups H2
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
[h2] csvread interprets unqoted zero-length string as null even when null value is explicitly given.
Hi, If course you could change the CSV source code; it is so small that rolling your own might not be a problem even in the long run. H2 has decided that a particular representation of the zero-length string always means NULL. Yes, so far. Even thought there is a relatively simple and clear documentation about what CSV should look like, almost all applications have their own special format. What is implemented in H2 is the most common features from the feedback I got. But I like to keep things simple (to prevent bugs, and to make things clear for the user as well). Regards, Thomas -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
Re: [h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message
I'll tune in here to provide you with some background info from the jOOQ
side. When Markus used jOOQ to *inline* bind values (e.g. WHERE
foldermap.parent IN (1, 2, 3, 4)), the query ran in an acceptable time on
my machine - around 1/2s. In the latter query, jOOQ renders bind values as
question marks for later binding. What may be interesting to note is the
fact that for historic reasons, jOOQ generates CAST(? AS BIGINT) for bind
values. I'm not sure if the CAST() is the source of confusion here. But
when creating thread dumps, I noticed that a significant amount of time is
spent in Value.convertTo():
main prio=6 tid=0x01d2e000 nid=0x1798 runnable [0x01d0d000]
java.lang.Thread.State: RUNNABLE
at org.h2.value.Value.convertTo(Value.java:507)
at org.h2.expression.Function.getSimpleValue(Function.java:781)
at org.h2.expression.Function.getValueWithArgs(Function.java:992)
at org.h2.expression.Function.getValue(Function.java:466)
at org.h2.expression.ConditionIn.getValue(ConditionIn.java:52)
at org.h2.expression.ConditionNot.getValue(ConditionNot.java:33)
at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:94)
at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:94)
at org.h2.expression.Expression.getBooleanValue(Expression.java:180)
at org.h2.command.dml.Select.queryFlat(Select.java:520)
So, without having evaluated this further, this *looks* as though a lot of
work is performed for the complete IN and NOT IN predicate for each record.
Hope this helps
Lukas
Am Montag, 7. Oktober 2013 21:38:44 UTC+2 schrieb Markus Fengler:
Hi Thomas,
I don't think it's slow because of a missing index. If I use the inline
statement its fast. Did you tried the example?
Am Montag, 7. Oktober 2013 21:35:16 UTC+2 schrieb Thomas Mueller:
Hi,
I guess the query is slow for some reason, possibly because there is no
index. Did you already check the documentation at
http://h2database.com/html/performance.html#explain_plan ?
Regards,
Thomas
On Sun, Oct 6, 2013 at 10:27 PM, Markus Fengler nar...@googlemail.comwrote:
Hi,
I want to get a ResultSet and the query contains a lot of Parameters.
If I use an inline Query then it works but if I use a PreparedStatement
with indexed Parameters there seems to be a problem.
Here is an example for this strange behaviour.
You can download the whole Example as maven project at
http://jmlp.volans.uberspace.de/ftp/issueExample.zip
Note: For generating the sql I use jooq library. I also posted the issue
at their repository but it seems that it is h2 specific. (
https://github.com/jOOQ/jOOQ/issues/2768)
@Test
public void testBugExample() throws SQLException {
/* Preparation of Data */
DSLContext dsl = DSL.using(this.con, SQLDialect.H2);
ListLong activeFolders =
dsl.select(FILES.ID).from(FILES).where(FILES.TYP.eq(D),
FILES.AVAILABLE.isTrue()).fetch(FILES.ID);
ListLong activeFiles =
dsl.select(FILES.ID).from(FILES).where(FILES.TYP.eq(F),
FILES.AVAILABLE.isTrue()).fetch(FILES.ID);
/* the Query with the Bug */
SelectConditionStepRecord1Long qry = dsl.select(FILES.ID
).from(FILES).join(FOLDERMAP).on(FILES.ID.eq(FOLDERMAP.ID))
.where(FILES.ID.notIn(activeFiles), FOLDERMAP.PARENT.in
(activeFolders));
String qryIndexed = qry.getSQL(ParamType.INDEXED);
String qryInline = qry.getSQL(ParamType.INLINED);
/* InlineParameter work */
log.info(Query Inline: + qryInline);
{
Statement stm = con.createStatement();
ResultSet rs = stm.executeQuery(qryInline);
while (rs.next())
rs.getLong(1);
rs.close();
stm.close();
}
/* InlineParameter work */
log.info(Query Indexed: + qryIndexed);
{
PreparedStatement stm = con.prepareStatement(qryIndexed);
/* set Parameter */
int pos = 1;
for (Long e : activeFiles)
stm.setLong(pos++, e);
for (Long e : activeFolders)
stm.setLong(pos++, e);
ResultSet rs = stm.executeQuery();
while (rs.next())
rs.getLong(1);
rs.close();
stm.close();
}
/* does not finish to this line */
log.info(finish);
}
--
You received this message because you are subscribed to the Google
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send
an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the
Re: [h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message
Hi Lukas,
you are right. Now I did four tests.
First with inline (e.g. where foldermap.parent in (1,2,3,4,...)) - the
query takes less than 2 seconds to finish.
Second with indexed parameters (e.g. where foldermap.parent in
(?,?,?,?,...)) - the query takes arount 37 seconds to finish.
Third with indexed and cast parameters (e.g. where foldermap.parent in
(cast(? as bigint),cast(? as bigint),cast(? as bigint),cast(? as
bigint),...)) - the query takes about 160 seconds to finish.
And last but not least a fourth with inline and cast (e.g. where
foldermap.parent in (cast(1 as bigint), cast(2 as bigint),...) - the query
takes less than one second.
For me it looks like that there is a performance issue at the h2 in
handling indexed parameters.
Am Dienstag, 8. Oktober 2013 08:31:11 UTC+2 schrieb Lukas Eder:
I'll tune in here to provide you with some background info from the jOOQ
side. When Markus used jOOQ to *inline* bind values (e.g. WHERE
foldermap.parent IN (1, 2, 3, 4)), the query ran in an acceptable time on
my machine - around 1/2s. In the latter query, jOOQ renders bind values as
question marks for later binding. What may be interesting to note is the
fact that for historic reasons, jOOQ generates CAST(? AS BIGINT) for bind
values. I'm not sure if the CAST() is the source of confusion here. But
when creating thread dumps, I noticed that a significant amount of time is
spent in Value.convertTo():
main prio=6 tid=0x01d2e000 nid=0x1798 runnable [0x01d0d000]
java.lang.Thread.State: RUNNABLE
at org.h2.value.Value.convertTo(Value.java:507)
at org.h2.expression.Function.getSimpleValue(Function.java:781)
at org.h2.expression.Function.getValueWithArgs(Function.java:992)
at org.h2.expression.Function.getValue(Function.java:466)
at org.h2.expression.ConditionIn.getValue(ConditionIn.java:52)
at org.h2.expression.ConditionNot.getValue(ConditionNot.java:33)
at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:94)
at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:94)
at org.h2.expression.Expression.getBooleanValue(Expression.java:180)
at org.h2.command.dml.Select.queryFlat(Select.java:520)
So, without having evaluated this further, this *looks* as though a lot
of work is performed for the complete IN and NOT IN predicate for each
record.
Hope this helps
Lukas
Am Montag, 7. Oktober 2013 21:38:44 UTC+2 schrieb Markus Fengler:
Hi Thomas,
I don't think it's slow because of a missing index. If I use the inline
statement its fast. Did you tried the example?
Am Montag, 7. Oktober 2013 21:35:16 UTC+2 schrieb Thomas Mueller:
Hi,
I guess the query is slow for some reason, possibly because there is no
index. Did you already check the documentation at
http://h2database.com/html/performance.html#explain_plan ?
Regards,
Thomas
On Sun, Oct 6, 2013 at 10:27 PM, Markus Fengler
nar...@googlemail.comwrote:
Hi,
I want to get a ResultSet and the query contains a lot of Parameters.
If I use an inline Query then it works but if I use a PreparedStatement
with indexed Parameters there seems to be a problem.
Here is an example for this strange behaviour.
You can download the whole Example as maven project at
http://jmlp.volans.uberspace.de/ftp/issueExample.zip
Note: For generating the sql I use jooq library. I also posted the
issue at their repository but it seems that it is h2 specific. (
https://github.com/jOOQ/jOOQ/issues/2768)
@Test
public void testBugExample() throws SQLException {
/* Preparation of Data */
DSLContext dsl = DSL.using(this.con, SQLDialect.H2);
ListLong activeFolders =
dsl.select(FILES.ID).from(FILES).where(FILES.TYP.eq(D),
FILES.AVAILABLE.isTrue()).fetch(FILES.ID);
ListLong activeFiles =
dsl.select(FILES.ID).from(FILES).where(FILES.TYP.eq(F),
FILES.AVAILABLE.isTrue()).fetch(FILES.ID);
/* the Query with the Bug */
SelectConditionStepRecord1Long qry = dsl.select(FILES.ID
).from(FILES).join(FOLDERMAP).on(FILES.ID.eq(FOLDERMAP.ID))
.where(FILES.ID.notIn(activeFiles), FOLDERMAP.PARENT.in
(activeFolders));
String qryIndexed = qry.getSQL(ParamType.INDEXED);
String qryInline = qry.getSQL(ParamType.INLINED);
/* InlineParameter work */
log.info(Query Inline: + qryInline);
{
Statement stm = con.createStatement();
ResultSet rs = stm.executeQuery(qryInline);
while (rs.next())
rs.getLong(1);
rs.close();
stm.close();
}
/* InlineParameter work */
log.info(Query Indexed: + qryIndexed);
{
PreparedStatement stm = con.prepareStatement(qryIndexed);
/* set Parameter */
int pos = 1;
for (Long e : activeFiles)
Re: [h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message
Hi Thanks for the updated test case. I've looked into this, and the situation is thusly: We have special case optimised code for handling IN (1,2,3) queries. Unfortunately, for IN (?,?,?) queries the optimised code does not get used because we at the point in time we run the optimiser, the optimiser sees the prepared-statement-parameters and bails out. Running the optimiser on a PreparedStatement kind of defeats the point of a PreparedStatement, which is to minimise per-execution setup cost. It might be possible to teach the code in org.h2.expression.ConditionIn#optimize to handle this case, but I'm loathe to add that much complexity for corner cases like this. I'll think about it some more. Regards, Noel. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
Re: [h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message
Hello, 2013/10/8 Noel Grandin noelgran...@gmail.com Hi Thanks for the updated test case. I've looked into this, and the situation is thusly: We have special case optimised code for handling IN (1,2,3) queries. Unfortunately, for IN (?,?,?) queries the optimised code does not get used because we at the point in time we run the optimiser, the optimiser sees the prepared-statement-parameters and bails out. Running the optimiser on a PreparedStatement kind of defeats the point of a PreparedStatement, which is to minimise per-execution setup cost. That's certainly true. I'm not sure if H2 does this, but other databases keep cursors and execution plans around for re-execution, in case equivalent statements are encountered. However, given the length of the IN predicate, I think that the above rule might no longer hold true. The SQL string is different for 3895 parameters than it is for 3896 parameters. This is a common problem when tuning IN predicates. When they are long, it is almost always better to inline the bind values (not only in H2). Of course, it would be even better to create an actual semi-join using a subselect. It might be possible to teach the code in org.h2.expression.ConditionIn#**optimize to handle this case, but I'm loathe to add that much complexity for corner cases like this. I'll think about it some more. I couldn't help but think about this a bit myself as well. ;-) In SQL, some expressions are deterministic (in the Oracle sense of the word), and some expressions are constant. Both expression attributes propagate through expressions. If a function F is deterministic, then F(A, B) is deterministic only if both A and B are also deterministic expressions. This can be further expanded to constants. If F is deterministic, then F(A, B) is constant only if A and B are constant expressions. Bind values are constant expressions for a single query execution. Thus, it would be possible to pre-process a SQL statement, substituting all constant expressions by their constant value. E.g. CAST(? AS BIGINT) could be substituted by the actual converted bind value. Alternatively, such expressions could be pre-calculated once and cached for a single query execution, similarly to what Oracle does with scalar subquery caching ( http://blog.jooq.org/2011/09/02/oracle-scalar-subquery-caching/) I would imagine that this is quite valuable in many non-corner cases as well, as H2 has a lot of built-in deterministic functions, such as: LOWER(), UPPER(), etc. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
Re: [h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message
On 2013-10-08 11:42, Lukas Eder wrote: In SQL, some expressions are deterministic (in the Oracle sense of the word), and some expressions are constant. Both expression attributes propagate through expressions. If a function F is deterministic, then F(A, B) is deterministic only if both A and B are also deterministic expressions. This can be further expanded to constants. If F is deterministic, then F(A, B) is constant only if A and B are constant expressions. Bind values are constant expressions for a single query execution. Thus, it would be possible to pre-process a SQL statement, substituting all constant expressions by their constant value. E.g. CAST(? AS BIGINT) could be substituted by the actual converted bind value. Alternatively, such expressions could be pre-calculated once and cached for a single query execution, similarly to what Oracle does with scalar subquery caching (http://blog.jooq.org/2011/09/02/oracle-scalar-subquery-caching/) We do function determinism and constant propagation in the Statement case, but not so much in the PreparedStatement case. If you feel like looking at improving the situation, feel free to have a poke around, I'm happy to answer questions about how the code is structured. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
Re: [h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message
Hi, I'm really impressed by the your support. Thank you for this. @Noel: If it's to hard to implement then don't think to long about it. As you said it's a special case (although I like it to use the jOOQ Function `select * from something where x in (Collection of Ids)', but it's not to hard to make a workaround (Lukas suggested http://www.jooq.org/doc/3.1/manual/sql-building/bind-values/inlined-parameters/ ). But please add this special case (indexed parameters in IN statements) as additional information to your h2-documentation. Am Dienstag, 8. Oktober 2013 11:25:30 UTC+2 schrieb Noel Grandin: Hi Thanks for the updated test case. I've looked into this, and the situation is thusly: We have special case optimised code for handling IN (1,2,3) queries. Unfortunately, for IN (?,?,?) queries the optimised code does not get used because we at the point in time we run the optimiser, the optimiser sees the prepared-statement-parameters and bails out. Running the optimiser on a PreparedStatement kind of defeats the point of a PreparedStatement, which is to minimise per-execution setup cost. It might be possible to teach the code in org.h2.expression.ConditionIn#optimize to handle this case, but I'm loathe to add that much complexity for corner cases like this. I'll think about it some more. Regards, Noel. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
Re: [h2] unable to write: the database is read only after system crash
Thanks! That works fine. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
[h2] Re: csvread interprets unqoted zero-length string as null even when null value is explicitly given.
Thomas, Took a look at the source and it is pretty straightforward. Changed/added four or five lines and it seems to be working fine. Understand why you don't want to change the behaviour since it would almost certainly break existing stuff and as you say add complication to an already messy situation. The messiness I'm referring to is lack of any decent CSV standardization, not H2. Thanks. On Thursday, October 3, 2013 5:59:43 PM UTC-4, WillyMac wrote: I would expect that the only representation for null would be the one specified. Maybe it is intended to work this way, but I find it confusing to have two null representations. Why bother specifying null=xxx if ,, is read as null anyway? Or maybe my syntax is wrong? *Input file:* c1,c2,c3 a,,c a,,c a,XX_null_XX,c *SQL:* select * from csvread( 'tst.csv', null, 'null=XX_null_XX' ) *Output (? == null):* C1C2C3 - - - a ? c a c a ? c -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
[h2] jdbc:h2:mem: threadsafe?
Hi, I think the documentation is quite clear. Of course in Java, you can pass around objects to any thread you like. If your application has a handle on an object, you can also call methods on it concurrently in multiple threads. H2 is thread-safe (as documented), but there are parts of the JDBC API that are problematic, for example it doesn't make sense to call PreparedStatement.setString + execute concurrently on the same object. But that has nothing to do with H2. Regards, Thomas On Monday, October 7, 2013, Laird Nelson wrote: On Monday, October 7, 2013 2:41:21 PM UTC-7, Laird Nelson wrote: The documentation says that connecting to the JDBC URL jdbc:h2:mem: creates a private in-memory database. Is the database so created private to the thread that creates it? To be clear, I'm aware of this documentation paragraph as well: In some cases, only one connection to a in-memory database is required. This means the database to be opened is private. In this case, the database URL is jdbc:h2:mem: Opening two connections within the same virtual machine means opening two different (private) databases. I just want to make sure that two connections in two *threads* in the same VM each get a database scoped to that thread. Best, Laird -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
[h2] trace file format
Hi, So, if accu is less than 100%, the rest of the time went where? To other queries or statements that are below that line. And if self is less than accu in hprof it has to do with sub-function execution time, I believe No. Self mean self, I don't know how to explain it otherwise sorry. Maybe the documentation about hprof is clearer. Or you can also ask at StackOverflow. Regards, Thomas -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
[h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message
Hi, Would it be possible to disable generating CAST? If the bind variable is set using PreparedStatement.setLong, I don't see why CAST would be needed. Of course support for superfluous CAST (or any deterministic function on a parameter) could be implemented in H2, but so far it wasn't needed, and I'm hesitant to implement features that are not really needed. Also, it might break a known workaround to force not using a certain index (using the expression -ID=-?). I know that's a hack :-) I don't think it's slow because of a missing index. OK, so the index is not be missing, but it is probably not used because of the CAST function. That would explain the difference. In general, I would probably use a profiler to check what's going on. See also http://h2database.com/html/performance.html#built_in_profiler - also, it would help to know the query plans, see http://h2database.com/html/performance.html#explain_plan Regards, Thomas -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
Re: [h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message
2013/10/8 Thomas Mueller thomas.tom.muel...@gmail.com Hi, Would it be possible to disable generating CAST? If the bind variable is set using PreparedStatement.setLong, I don't see why CAST would be needed. This is why: https://groups.google.com/forum/#!msg/h2-database/byINJvfm1S8/gtHaq1jvyPMJ After I had reported the above, I had written this blog post here, explaining why jOOQ casts bind values if databases don't infer them in some cases: http://blog.jooq.org/2011/08/31/rdbms-bind-variable-casting-madness Remember? :-) But I'll review those casts. I'm sure that many of them aren't needed, and jOOQ could omit blindly adding casts to all bind variables, e.g. when placed in the IN predicate. Of course support for superfluous CAST (or any deterministic function on a parameter) could be implemented in H2, but so far it wasn't needed, and I'm hesitant to implement features that are not really needed. Also, it might break a known workaround to force not using a certain index (using the expression -ID=-?). I know that's a hack :-) True, I'm aware of similar hacks with Oracle: ID + 0 = ?. Although, that's not exactly what I was proposing. While -ID prevents index usage, -? is still a constant. So ID = -(-?) should still work the same way as ID = ?, in principle. I don't think it's slow because of a missing index. OK, so the index is not be missing, but it is probably not used because of the CAST function. That would explain the difference. In general, I would probably use a profiler to check what's going on. See also http://h2database.com/html/performance.html#built_in_profiler - also, it would help to know the query plans, see http://h2database.com/html/performance.html#explain_plan Regards, Thomas -- You received this message because you are subscribed to a topic in the Google Groups H2 Database group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/1B4PuijeaYU/unsubscribe. To unsubscribe from this group and all its topics, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
Re: [h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message
Hi, I see. Maybe it would make sense to add a special case for CAST(? AS type) in the parser, so that it's still a parameter, but with a specific type. I will try. Regards, Thomas On Tue, Oct 8, 2013 at 7:39 PM, Lukas Eder lukas.e...@gmail.com wrote: 2013/10/8 Thomas Mueller thomas.tom.muel...@gmail.com Hi, Would it be possible to disable generating CAST? If the bind variable is set using PreparedStatement.setLong, I don't see why CAST would be needed. This is why: https://groups.google.com/forum/#!msg/h2-database/byINJvfm1S8/gtHaq1jvyPMJ After I had reported the above, I had written this blog post here, explaining why jOOQ casts bind values if databases don't infer them in some cases: http://blog.jooq.org/2011/08/31/rdbms-bind-variable-casting-madness Remember? :-) But I'll review those casts. I'm sure that many of them aren't needed, and jOOQ could omit blindly adding casts to all bind variables, e.g. when placed in the IN predicate. Of course support for superfluous CAST (or any deterministic function on a parameter) could be implemented in H2, but so far it wasn't needed, and I'm hesitant to implement features that are not really needed. Also, it might break a known workaround to force not using a certain index (using the expression -ID=-?). I know that's a hack :-) True, I'm aware of similar hacks with Oracle: ID + 0 = ?. Although, that's not exactly what I was proposing. While -ID prevents index usage, -? is still a constant. So ID = -(-?) should still work the same way as ID = ?, in principle. I don't think it's slow because of a missing index. OK, so the index is not be missing, but it is probably not used because of the CAST function. That would explain the difference. In general, I would probably use a profiler to check what's going on. See also http://h2database.com/html/performance.html#built_in_profiler - also, it would help to know the query plans, see http://h2database.com/html/performance.html#explain_plan Regards, Thomas -- You received this message because you are subscribed to a topic in the Google Groups H2 Database group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/1B4PuijeaYU/unsubscribe. To unsubscribe from this group and all its topics, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
Re: [h2] trace file format
Oh. accu is just a running sum? On Tuesday, October 8, 2013 10:24:11 AM UTC-7, Thomas Mueller wrote: Hi, So, if accu is less than 100%, the rest of the time went where? To other queries or statements that are below that line. And if self is less than accu in hprof it has to do with sub-function execution time, I believe No. Self mean self, I don't know how to explain it otherwise sorry. Maybe the documentation about hprof is clearer. Or you can also ask at StackOverflow. Regards, Thomas -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
[h2] Re: console considerably faster than code
I believe this can also be caused by the OS block caching, which won't be affected by restarting H2. I see this behavior: 1) Run query in embedded app, it takes 15 sec 2) Shut down and run the same query in console, it takes 200 ms 3) Run it in the app again after restarting, it takes 200ms 4) Run a novel query in the app, it takes 15 sec This makes it seem like the disk io is the bottleneck, though I note that my disk usage is always fairly low. On Wednesday, June 9, 2010 9:44:48 PM UTC-7, Thomas Mueller wrote: Hi, It could be a caching issue. To make sure, could you test again with the H2 Console, but ensure that the database is closed (no .lock.db file), then open a connection, and run this query (no other queries before that)? One way to speed up multi-dimensional queries is: http://h2database.com/html/features.html#multi_dimensional Regards, Thomas -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
[h2] some performance numbers
I'm wondering if these numbers look typical of h2, or if I still have some bottleneck I haven't identified. I have a probes table (mentioned in other threads) with one index over two columns: eid (int) and name (varchar). (eid, name) is unique. For each eid there might be 10k-500k rows. The table has about 38M rows. A query that retrieves 500 rows matching a single eid with 240k rows, takes about 5 seconds. This is using the TABLE() inner join, instead of a big IN query. The explain analyze output shows it hitting the index. If I close the db and re-open it, the same query takes much less than a second, which I suspect reflects the OS block cache. Though, as I mentioned earlier, the disk doesn't seem very strained during the first query. I'm not sure what that means. The query: SELECT `probes`.`name` as `gene`, `probes`.`id` FROM `probes` INNER JOIN TABLE(name varchar=?) T ON T.`name`=`probes`.`name` WHERE (`probes`.`eid` = ?) The explain analyze: SELECT PROBES.NAME AS GENE, PROBES.ID FROM TABLE(NAME VARCHAR=?1) T /* function */ /* scanCount: 501 */ INNER JOIN PUBLIC.PROBES /* PUBLIC.EID_NAME_INDEX_8: EID = ?2 AND NAME = T.NAME */ ON 1=1 /* scanCount: 1000 */ WHERE (PROBES.EID = ?2) AND (T.NAME = PROBES.NAME) -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
Re: [h2] trace file format
Hi Yes, in percent. Regards, Thomas Am 08.10.2013 21:01 schrieb Brian Craft craft.br...@gmail.com: Oh. accu is just a running sum? On Tuesday, October 8, 2013 10:24:11 AM UTC-7, Thomas Mueller wrote: Hi, So, if accu is less than 100%, the rest of the time went where? To other queries or statements that are below that line. And if self is less than accu in hprof it has to do with sub-function execution time, I believe No. Self mean self, I don't know how to explain it otherwise sorry. Maybe the documentation about hprof is clearer. Or you can also ask at StackOverflow. Regards, Thomas -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
Re: [h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message
Hi,
According to a simple test case, H2 is already using an index when using an
unnecessary CAST. So I'm not sure yet what the problem is for your case.
Could you run the query with explain and post the query plan? Example:
Connection conn = DriverManager.getConnection(jdbc:h2:mem:);
Statement stat = conn.createStatement();
stat.execute(create table test(id identity));
PreparedStatement prep;
ResultSet rs;
prep = conn.prepareStatement(
explain select * from test where id in (?, ?));
prep.setInt(1, 1);
prep.setInt(2, 1);
rs = prep.executeQuery();
rs.next();
System.out.println(rs.getString(1));
prep = conn.prepareStatement(
explain select * from test where id in +
(cast(? as bigint), cast(? as bigint)));
prep.setInt(1, 1);
prep.setInt(2, 1);
rs = prep.executeQuery();
rs.next();
System.out.println(rs.getString(1));
conn.close();
The output is:
SELECT
TEST.ID
FROM PUBLIC.TEST
/* PUBLIC.PRIMARY_KEY_2: ID IN(?1, ?2) */
WHERE ID IN(?1, ?2)
SELECT
TEST.ID
FROM PUBLIC.TEST
/* PUBLIC.PRIMARY_KEY_2: ID IN(CAST(?1 AS BIGINT), SIN(CAST(?2 AS
BIGINT))) */
WHERE ID IN(CAST(?1 AS BIGINT), SIN(CAST(?2 AS BIGINT)))
So in both cases the primary key is used. When changing id in to -id
in, then a table scan is used (no index). An index is also used when using
a deterministic function or a combination (SIN(?), SIN(CAST(? AS INT)), and
so on).
Regards,
Thomas
On Tuesday, October 8, 2013, Lukas Eder wrote:
2013/10/8 Thomas Mueller thomas.tom.muel...@gmail.com javascript:_e({},
'cvml', 'thomas.tom.muel...@gmail.com');
Hi,
Would it be possible to disable generating CAST? If the bind variable is
set using PreparedStatement.setLong, I don't see why CAST would be needed.
This is why:
https://groups.google.com/forum/#!msg/h2-database/byINJvfm1S8/gtHaq1jvyPMJ
After I had reported the above, I had written this blog post here,
explaining why jOOQ casts bind values if databases don't infer them in some
cases:
http://blog.jooq.org/2011/08/31/rdbms-bind-variable-casting-madness
Remember? :-)
But I'll review those casts. I'm sure that many of them aren't needed, and
jOOQ could omit blindly adding casts to all bind variables, e.g. when
placed in the IN predicate.
Of course support for superfluous CAST (or any deterministic function on
a parameter) could be implemented in H2, but so far it wasn't needed, and
I'm hesitant to implement features that are not really needed. Also, it
might break a known workaround to force not using a certain index (using
the expression -ID=-?). I know that's a hack :-)
True, I'm aware of similar hacks with Oracle: ID + 0 = ?. Although, that's
not exactly what I was proposing. While -ID prevents index usage, -? is
still a constant. So ID = -(-?) should still work the same way as ID =
?, in principle.
I don't think it's slow because of a missing index.
OK, so the index is not be missing, but it is probably not used because
of the CAST function. That would explain the difference.
In general, I would probably use a profiler to check what's going on. See
also http://h2database.com/html/performance.html#built_in_profiler -
also, it would help to know the query plans, see
http://h2database.com/html/performance.html#explain_plan
Regards,
Thomas
--
You received this message because you are subscribed to a topic in the
Google Groups H2 Database group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/h2-database/1B4PuijeaYU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
h2-database+unsubscr...@googlegroups.com javascript:_e({}, 'cvml',
'h2-database%2bunsubscr...@googlegroups.com');.
To post to this group, send email to
h2-database@googlegroups.comjavascript:_e({}, 'cvml',
'h2-database@googlegroups.com');
.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send an
email to h2-database+unsubscr...@googlegroups.com javascript:_e({},
'cvml', 'h2-database%2bunsubscr...@googlegroups.com');.
To post to this group, send email to
h2-database@googlegroups.comjavascript:_e({}, 'cvml',
'h2-database@googlegroups.com');
.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups H2
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
