Hallo,
für "Double" habe ich kein Beispiel gefunden. Ich kann mich nicht mehre
erinnern warum ich damals "Double" genommen habe.
"break ;" habe ich ersetzt damit es auch weitere Felder geprüft werden.
Hier ist ein Beispiel:
public static void main(String[] args) {
String dbFilePath = "c:/temp/";
File f = new File(dbFilePath + "test.h2.db");
if (f.exists()) {
f.delete();
}
org.h2.Driver.load();
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:h2:file:" + dbFilePath + "test",
"sa", "test");
StringReader sr = new StringReader("CREATE TABLE TEST (COLUMN1 BIGINT,
COLUMN2 BIGINT);");
RunScript.execute(conn, sr);
sr = new StringReader("CREATE INDEX IDX_TEST_COLUMN1 ON TEST(COLUMN1);");
RunScript.execute(conn, sr);
sr = new StringReader("CREATE INDEX IDX_TEST_COLUMN1_COLUMN2 ON
TEST(COLUMN1, COLUMN2);");
RunScript.execute(conn, sr);
for (int i = 0; i < 1000; i++) {
for (int j = 0; j < 1000; j++) {
sr = new StringReader("insert into TEST values (" + i + "," + j + ");");
RunScript.execute(conn, sr);
}
}
long time = new Date().getTime();
sr = new StringReader("SELECT * FROM TEST ac where COLUMN1 >20 AND COLUMN1
< 50 AND COLUMN2 = 1");
RunScript.execute(conn, sr);
System.out.println(new Date().getTime() - time + "ms");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
Mit "break;" werden die Indexe gleiche Kosten habe, und es wird dann erste
genommen (141ms), Ohne "break;" wird dann zweite genommen (95ms). (32%
schneller)
Es ist ein vielleicht schlechter Beispiel, aber mann kann das mindestens
vergleichen.
Mit freundlichen Grüßen
Johann Schmidt
Am Donnerstag, 30. Januar 2014 21:02:16 UTC+1 schrieb Thomas Mueller:
>
> Hallo
>
> I would need a simple, reproducible test case.
>
> German is OK, ich spreche Deutsch.
>
> Ich bräuchte einen einfachen, reproduzierbaren Test-Case.
>
> Ich habe gesehen dass einige "break" verschwunden sind, aber ich denke
> diese werden benötigt. Bei einem Mehrspalten-Index ist für
> Bereichs-Abfragen bei der ersten Spalte Schluss. Beispiel: (1, a) (1, b)
> (2, a) (2, b) .... Eine Abfrage "> (1 d)" wird recht viele Datensätze
> betreffen, hier kann man die zweite Spalte nicht berücksichtigen denke ich.
>
> Regards,
> Thomas
>
>
>
> On Wed, Jan 29, 2014 at 11:21 AM, <[email protected] <javascript:>> wrote:
>
>> In the patch is not only double changed, but I have replaced some
>> "break".
>> So it will calculate entire index, not just the beginning.
>>
>> Ich spreche kein Englisch und kann deshalb nicht so gut erklären.
>>
>> Am Dienstag, 28. Januar 2014 19:04:45 UTC+1 schrieb Thomas Mueller:
>>>
>>> Hi,
>>>
>>> OK I see. However, the patch doesn't seem to help here. I'm afraid the
>>> optimizer of H2 isn't smart enough to use the right plan in your case.
>>>
>>> I think that one query is faster than the other is more or less
>>> coincidence, as the number of reads seem to be the same.
>>>
>>> Regards,
>>> Thomas
>>>
>>>
>>>
>>>
>>> On Tuesday, January 28, 2014, Pandu Purnama <[email protected]> wrote:
>>>
>>>> Hello,
>>>> The reason I want to use idx_type is based on some articles about
>>>> indexing group by. Here's one of them:
>>>> http://use-the-index-luke.com/sql/sorting-grouping/indexed-group-by
>>>>
>>>> And it does seems to increase the performance on my experiment in H2
>>>> web console with 1 million records.
>>>> Please try run these queries in H2 web console (will create 1 million
>>>> rows).
>>>>
>>>> CREATE TABLE `agent_cfg` (
>>>> `AGENT_ID` int(11) NOT NULL auto_increment,
>>>> `DELETED` int(1) NOT NULL default '0',
>>>> `ORG_ID` int(11) NOT NULL default '0',
>>>> `PHYSICAL_CLASS_TYPE` tinyint(3) NOT NULL default '0',
>>>> PRIMARY KEY (`AGENT_ID`)
>>>> );
>>>>
>>>>
>>>> CREATE INDEX IDX_AGENT_CFG_CLASS_SUM ON agent_cfg(PHYSICAL_CLASS_TYPE,
>>>> DELETED, ORG_ID);
>>>>
>>>>
>>>> CREATE INDEX IDX_AGENT_CFG_ORG_SUM ON agent_cfg(ORG_ID, DELETED);
>>>>
>>>>
>>>>
>>>> @loop 100000 insert into agent_cfg values (1 + ?,0,0,0);
>>>>
>>>> SELECT ac.physical_class_type, count(*) FROM AGENT_CFG ac where
>>>> ac.deleted = 0 and ac.org_id = 0 group by ac.physical_class_type order by
>>>> ac.physical_class_type;
>>>>
>>>> DROP INDEX IDX_AGENT_CFG_ORG_SUM;
>>>>
>>>> SELECT ac.physical_class_type, count(*) FROM AGENT_CFG ac where
>>>> ac.deleted = 0 and ac.org_id = 0 group by ac.physical_class_type order by
>>>> ac.physical_class_type;
>>>>
>>>> On my test the second select is faster because one of the index has
>>>> been dropped.
>>>> 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 [email protected].
>>>> To post to this group, send email to [email protected].
>>>> 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 [email protected] <javascript:>.
>> To post to this group, send email to [email protected]<javascript:>
>> .
>> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
