[h2] Re: Query Optimizer limitations

[Adam McMahon]

Also, if you can, try loading the data into another DB and running the same 
querry.  This will give you an idea if it is a problem with how you are 
using H2, or if it is just a hard query for most engines to optimize.  
Also, you may get faster results if you use the H2 PageStore engine.

On Thursday, January 19, 2017 at 3:26:21 PM UTC-5, Adam McMahon wrote:
>
> As far as I am aware, the problem with the query optimizer is that it does 
> not use indexs in every situation that one might expect.  But yours seems 
> to be using the index.  So, I don't know what else it could be (but I am no 
> expert at H2).
>
> How long does it take to run the query?  How many rows are there, and what 
> is the general size of the rows (number of columns)?
>
>
>
> On Thursday, January 19, 2017 at 5:47:55 AM UTC-5, Anil Dasari wrote:
>>
>> Hi Adam,
>>
>> I mean query is very slow. Explain plan looks good and it is using index. 
>> I did not compare with other databases. 
>>
>> Thanks.
>>
>

-- 
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Query Optimizer limitations

[Adam McMahon]

As far as I am aware, the problem with the query optimizer is that it does 
not use indexs in every situation that one might expect.  But yours seems 
to be using the index.  So, I don't know what else it could be (but I am no 
expert at H2).

How long does it take to run the query?  How many rows are there, and what 
is the general size of the rows (number of columns)?



On Thursday, January 19, 2017 at 5:47:55 AM UTC-5, Anil Dasari wrote:
>
> Hi Adam,
>
> I mean query is very slow. Explain plan looks good and it is using index. 
> I did not compare with other databases. 
>
> Thanks.
>

-- 
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: File corruption with nioMemLZF

[Noel Grandin]

Can you post your performance test code?
On Thu, 19 Jan 2017 at 18:36, Anatolii K  wrote:

> Sorry to say so, but performance dropped dramatically.
> Previously compressed storage was just a bit slower than uncompressed.
> But now it become almost 100 times slower!
>
>
>
> On Thursday, January 19, 2017 at 1:32:49 PM UTC, Noel Grandin wrote:
>
> I have pushed a fix for this, it now survives your test.
>
> I had to simplify the current code and just synchronize the methods, which
> means it may lose some performance. If this
> is a problem for you, I'm sorry, but you'll have to come up with some
> patches yourself.
>
> --
> 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 https://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: Voting up for SQL Trigger

[Noel Grandin]

No, I meant that I'd be happy to accept a patch that implemented such a
thing, not that I intended to do it myself
On Thu, 19 Jan 2017 at 16:35, Yan  wrote:

> Thanks Noel - any target release and ETA for it ?
>
>
> On Wednesday, January 18, 2017 at 11:29:38 PM UTC-5, Noel Grandin wrote:
>
> I'd be happy with a patch that did that
>
> On Tue, 17 Jan 2017 at 22:47, Yan  wrote:
>
> Fair enough Noel.
>
> What about opening up inline java coding like alias/functions ? Currently
> triggers must refer to an existing compiled class - the alternative to
> provide the java code in the trigger creation statement would really be a
> plus.
>
> As an alias gets implicitly the connection, a trigger method would in
> addition receive the old and new rows.
>
>
> On Monday, January 16, 2017 at 1:56:20 PM UTC-5, Noel Grandin wrote:
>
> we have triggers, we just don't have triggers implemented in some embedded
> SQL mini-language.
>
> http://h2database.com/html/grammar.html#create_trigger
>
> I don't think this is likely to happen any time soon, since I consider the
> current solution adequate, and I don't see the point in creating a
> half-baked trigger language when we can already use a full-featured
> language like Java.
> ​
>
> --
> 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 https://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>
> --
> 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 https://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: File corruption with nioMemLZF

[Anatolii K]

Sorry to say so, but performance dropped dramatically. 
Previously compressed storage was just a bit slower than uncompressed.
But now it become almost 100 times slower!



On Thursday, January 19, 2017 at 1:32:49 PM UTC, Noel Grandin wrote:
>
> I have pushed a fix for this, it now survives your test. 
>
> I had to simplify the current code and just synchronize the methods, which 
> means it may lose some performance. If this 
> is a problem for you, I'm sorry, but you'll have to come up with some 
> patches yourself. 
>

-- 
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: Voting up for SQL Trigger

[Yan]

Thanks Noel - any target release and ETA for it ?

On Wednesday, January 18, 2017 at 11:29:38 PM UTC-5, Noel Grandin wrote:
>
> I'd be happy with a patch that did that
> On Tue, 17 Jan 2017 at 22:47, Yan  
> wrote:
>
>> Fair enough Noel. 
>>
>> What about opening up inline java coding like alias/functions ? Currently 
>> triggers must refer to an existing compiled class - the alternative to 
>> provide the java code in the trigger creation statement would really be a 
>> plus. 
>>
>> As an alias gets implicitly the connection, a trigger method would in 
>> addition receive the old and new rows.
>>
>>
>> On Monday, January 16, 2017 at 1:56:20 PM UTC-5, Noel Grandin wrote:
>>>
>>> we have triggers, we just don't have triggers implemented in some 
>>> embedded SQL mini-language.
>>>
>>> http://h2database.com/html/grammar.html#create_trigger
>>>
>>> I don't think this is likely to happen any time soon, since I consider 
>>> the current solution adequate, and I don't see the point in creating a 
>>> half-baked trigger language when we can already use a full-featured 
>>> language like Java.
>>> ​
>>>
>> -- 
>> 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 https://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>

-- 
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Last chance SQL editing in JDBC + fooling DBProduct name

[Yan]

Thanks Kerry. It turns out there are many "driver delegaters" out there. 
One I find interesting 
is: 
http://www.nic.uoregon.edu/~amorris/regression/testtau/tau2/tools/src/perfdmf/src/database/DBConnector.java
 
It's not designed for H2 backend but highlights the transformation you can 
do in a JDBC proxy and log the result before/after.

Aside from overriding the DBProductName, I still believe it's best to 
contribute to further H2's MODE functionality and expand on other DB 
compatibility when the issue is either dialect or missing scalar functions 
(if it can be mimic'd in Java) - the idea for a generic regex replace 
through properties file therefore still stands in my mind...

As for your commission, will see ;-) Need first to find someone who can 
develop this as I am not a Java expert - will start looking and be happy to 
share with the community to take over.

On Wednesday, January 18, 2017 at 2:15:56 AM UTC-5, Kerry Sainsbury wrote:
>
> My cunning idea would be to not put this into H2 at all. Instead create 
> your own JDBC Driver (it's not that hard -- I've written one) that 
> delegates to a "real" JDBC Driver. That way you can tell the client app any 
> lies you like. A quick Google reveals this example 
> ,
>  
> which would be a good start.
>
> You'd also implement your own java.sql.Connection class which does the 
> regex mangling you describe, prior to passing the SQL off to the real JDBC 
> Driver's Connection object.
>
> It would have the advantage of being able to work with any JDBC driver and 
> so be potentially useful to users of non-H2 databases.
>
> I'll be happy to take 30% as a Commission :-)
>
>
>
> On 18 January 2017 at 10:35, Yan  
> wrote:
>
>> Despite all the built-in flexibility of H2 to be compatible with other 
>> databases, there are instances where apps are designed - and one has no 
>> access to the source code - to work only with specific RDBMS, say Oracle or 
>> MSSQL. 
>> I still want to use h2 for development instances and not go through the 
>> hassle and cost of installing multiple databases. Setting the MODE 
>> parameter would not help when the DatabaseProductName is checked upon: it 
>> would return h2 regardless.
>>
>> I was thus wondering
>>
>>1. could you make the DBName configurable (properties file or 
>>parameter in the connection url ) ?
>>
>>2. If not, would a code change in the getDatabaseProductName of the 
>>h2 core library be a license breach or be considered a fork. Note I would 
>>not redistribute the H2-mod as only for my own use
>>
>>3. beyond the db name, some SQL statements are hardcoded especially 
>>DDL so they only work for the identified DBMS. 
>>It would be great to have an option to catch known statements and 
>>reformat them using regex using a list maintained in properties file e.g. 
>>ALTER TABLE (\w*) MODIFY (\w*) TIMESTAMP DEFAULT SYSTIMESTAMP = ALTER 
>>TABLE %1 ALTER %2 SET DEFAULT TIMESTAMP
>>This would be evaluated in the executeQuery, executeUpdate and 
>>execute methods and allow to transform any statement into H2 dialect 
>>without touching the original application code
>>
>> Does anyone have other ideas to fool an app ?
>>
>> -- 
>> 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 https://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>

-- 
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: File corruption with nioMemLZF

[Noel Grandin]

I have pushed a fix for this, it now survives your test.

I had to simplify the current code and just synchronize the methods, which means it may lose some performance. If this 
is a problem for you, I'm sorry, but you'll have to come up with some patches yourself.


--
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: File corruption with nioMemLZF

[Anatolii K]

Sorry for bothering you, but could you please look at it 

-- 
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Query Optimizer limitations

[Anil Dasari]

Hi Adam,

I mean query is very slow. Explain plan looks good and it is using index. I 
did not compare with other databases. 

Thanks.

-- 
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.