Hi Thomas,
I have verified the queries with EXPLAIN ANAYLYZE, the queries are
using right indexes.
Here I am giving the query sample and schema structure.
Query sample:
------------------------
SELECT ATTR1, MAX(ATTR10) AS ATTR10, SUM(ATTR11) AS ATTR11,SUM(ATTR12) AS
ATTR12, SUM(ATTR13) AS ATTR13,SUM(ATTR14) AS ATTR14, SUM(ATTR15) AS
ATTR15,SUM(ATTR16) AS ATTR16, SUM(ATTR17) AS ATTR17,SUM(ATTR18) AS
ATTR18,SUM(ATTR19) AS ATTR19, SUM(ATTR20) AS ATTR20,SUM(ATTR21) AS ATTR21,
MAX(ATTR22) AS ATTR22, ATTR2, ATTR3, ATTR4, ATTR5 FROM table8 WHERE ATTR2 =
9 AND ATTR2 IN
(91,384,352,179,92,115,102,503,173,2354,82,72,112,83,94,296,1272,519,170,78,182,113,34,366,74,101,183,30,171,294,167,86,42,111,96,95,337,32,43,76,87,2356,79,177,174,117,90,88,175,100,335,169,176,334,85,354,180,2355,80,353,99,336,385,33,77,84,89,2366,172,518,168,97,114,1274,81,178,293,75,2359,31,1275,383,2360,116,351,1273,295,181,110,73,98,93)
AND ATTR4 IN (2,4,9,8,11,6,1,3,10,7,12,5) GROUP BY ATTR1, ATTR2, ATTR4
ORDER BY ATTR3 asc, ATTR5 asc LIMIT 0, 50;
Out of 8 tables only table5 will have 3 k rows, all other tables will have
below 500 rows. Updating table 5 with 3k rows taking longer time because of
select queries.
I didn't get why select should block the update queries.
DB schema structure
-----------------------------
CREATE MEMORY TABLE table1 (
ATTR1 INT NOT NULL,
ATTR2 INT default 0,
ATTR3 VARCHAR(255) default NULL,
ATTR4 INT default 0,
ATTR5 VARCHAR(255) default NULL,
ATTR6 INT default 0,
ATTR7 VARCHAR(255) default NULL,
ATTR8 INT default 0,
ATTR9 VARCHAR(255) default NULL,
ATTR10 VARCHAR(50) default NULL,
ATTR11 BIGINT NOT NULL,
ATTR12 INT default 0,
ATTR13 INT default 0,
ATTR14 INT default 0
);
CREATE INDEX table1_IDX1 ON table1(ATTR2);
CREATE INDEX table1_IDX2 ON table1(ATTR4);
CREATE INDEX table1_IDX3 ON table1(ATTR6);
CREATE INDEX table1_IDX4 ON table1(ATTR8);
CREATE MEMORY TABLE table2 (
ATT1 INT NOT NULL,
ATTR2 INT NOT NULL,
ATTR3 VARCHAR(50) default NULL,
ATTR4 BIGINT NOT NULL,
ATTR5 INT default 0,
ATTR6 VARCHAR(50) default NULL,
ATTR7 BIGINT default 0
);
CREATE INDEX table2_IDX1 ON table2(ATTR2);
CREATE MEMORY TABLE table3 (
ATTR1 INT NOT NULL,
ATTR2 INT NOT NULL,
ATTR3 VARCHAR(50) default NULL,
ATTR4 INT default 0,
ATTR5 INT default 0,
ATTR6 INT default 0
);
CREATE INDEX table3_IDX1 ON table3(ATTR2);
CREATE MEMORY TABLE table4 (
ATTR1 INT NOT NULL,
ATTR2 INT default 0,
ATTR3 VARCHAR(255) default NULL,
ATTR4 INT default 0,
ATTR5 VARCHAR(255) default NULL,
ATTR6 VARCHAR(50) default NULL,
ATTR7 BIGINT NOT NULL,
ATTR8 VARCHAR(50) default NULL,
ATTR9 INT default 0,
ATTR10 INT default 0,
ATTR11 INT default 0,
ATTR12 INT default 0,
ATTR13 INT default 0,
ATTR14 INT default 0,
ATTR15 INT default 0,
ATTR16 INT default 0,
ATTR17 INT default 0,
ATTR18 INT default 0,
ATTR19 INT default 0,
ATTR20 INT default 0
);
CREATE INDEX table4_IDX3 ON table4(ATTR2);
CREATE INDEX table4_IDX4 ON table4(ATTR4);
CREATE MEMORY TABLE table5 (
ATTR1 INT NOT NULL,
ATTR2 INT default 0,
ATTR3 INT default 0,
ATTR4 BIGINT default 0,
ATTR5 VARCHAR(255) default NULL,
ATTR6 VARCHAR(50) default NULL,
ATTR7 VARCHAR(50) default NULL,
ATTR8 VARCHAR(50) default NULL,
ATTR9 BIGINT NOT NULL,
ATTR10 VARCHAR(50) default NULL,
ATTR11 INT default 0,
ATTR12 INT default 0,
ATTR13 INT default 0,
ATTR14 INT default 0,
ATTR15 INT default 0,
ATTR16 INT default 0,
ATTR17 INT default 0,
ATTR18 INT default 0,
ATTR19 INT default 0,
ATTR20 INT default 0,
ATTR21 INT default 0,
ATTR22 INT default 0,
ATTR23 INT default 0,
ATTR24 INT default 0,
ATTR25 INT default 0,
ATTR26 INT default 0,
ATTR27 INT default 0,
ATTR28 INT default 0,
ATTR29 INT default 0,
ATTR30 INT default 0,
ATTR31 INT default 0,
ATTR32 INT default 0,
ATTR33 INT default 0,
ATTR34 INT default 0,
ATTR35 INT default 0,
ATTR36 INT default 0,
ATTR37 INT default 0,
ATTR38 INT default 0,
ATTR39 INT default 0,
ATTR40 INT default 0,
ATTR41 INT default 0,
ATTR42 BIGINT default 0,
ATTR43 BIGINT default 0,
ATTR44 BIGINT default 0,
ATTR45 BIGINT default 0,
ATTR46 BIGINT default 0,
ATTR47 BIGINT default 0,
ATTR48 BIGINT default 0,
ATTR49 BIGINT default 0,
ATTR50 BIGINT default 0,
ATTR51 BIGINT default 0,
ATTR52 BIGINT default 0,
ATTR53 BIGINT default 0,
ATTR54 BIGINT default 0,
ATTR55 BIGINT default 0,
ATTR56 BIGINT default 0,
ATTR57 BIGINT default 0,
ATTR58 BIGINT default 0,
ATTR59 BIGINT default 0,
ATTR60 BIGINT default 0,
ATTR61 BIGINT default 0,
ATTR62 BIGINT default 0,
ATTR63 BIGINT default 0,
ATTR64 BIGINT default 0,
ATTR65 DOUBLE default 0,
ATTR66 DOUBLE default 0,
ATTR67 INT default 0,
ATTR68 VARCHAR(255) default NULL,
ATTR69 VARCHAR(255) default NULL,
ATTR70 BIGINT default 0,
ATTR71 VARCHAR(255) default NULL,
ATTR72 BIGINT default 0,
ATTR73 INT default 0,
ATTR74 BOOLEAN,
ATTR75 VARCHAR(50) default NULL
);
CREATE INDEX table5_IDX1 ON table5(ATTR2);
CREATE INDEX table5_IDX2 ON table5(ATTR3);
CREATE MEMORY TABLE table6 (
ATTR1 INT NOT NULL,
ATTR2 BIGINT NOT NULL,
ATTR3 VARCHAR(50) default NULL,
ATTR4 VARCHAR(50) default NULL,
ATTR5 VARCHAR(50) default NULL,
ATTR6 BIGINT NOT NULL,
ATTR7 VARCHAR(50) default NULL,
ATTR8 INT NOT NULL,
ATTR9 INT NOT NULL,
ATTR10 INT default 0
);
CREATE INDEX table6_IDX1 ON table6(ATTR2);
CREATE MEMORY TABLE table7 (
ATTR1 INT NOT NULL,
ATTR2 INT default 0,
ATTR3 VARCHAR(255) default NULL,
ATTR4 INT default 0,
ATTR5 VARCHAR(255) default NULL,
ATTR6 VARCHAR(50) default NULL,
ATTR7 BIGINT NOT NULL,
ATTR8 INT default 0,
ATTR9 INT default 0,
ATTR10 INT default 0,
ATTR11 INT default 0,
ATTR12 INT default 0,
ATTR13 BIGINT default 0,
ATTR14 VARCHAR(50) default NULL
);
CREATE INDEX table7_IDX1 ON table7(ATTR2);
CREATE INDEX table7_IDX2 ON table7(ATTR4);
CREATE MEMORY TABLE table8 (
ATTR1 INT NOT NULL,
ATTR2 INT default 0,
ATTR3 VARCHAR(255) default NULL,
ATTR4 INT default 0,
ATTR5 VARCHAR(255) default NULL,
ATTR6 INT default 0,
ATTR7 VARCHAR(255) default NULL,
ATTR8 VARCHAR(50) default NULL,
ATTR9 BIGINT NOT NULL,
ATTR10 INT default 0,
ATTR11 INT default 0,
ATTR12 INT default 0,
ATTR13 INT default 0,
ATTR14 INT default 0,
ATTR15 INT default 0,
ATTR16 INT default 0,
ATTR17 INT default 0,
ATTR18 INT default 0,
ATTR19 INT default 0,
ATTR20 INT default 0,
ATTR21 INT default 0,
ATTR22 BIGINT default 0
);
CREATE INDEX table8_IDX1 ON table8(ATTR2);
CREATE INDEX table8_IDX2 ON table8(ATTR4);
CREATE INDEX table8_IDX3 ON table8(ATTR6);
Thanks,
Suresh
I am using
On Fri, Feb 18, 2011 at 12:47 AM, Thomas Mueller <
[email protected]> wrote:
> Hi,
>
> It looks like you are using a query that contains a condition of the
> form "column IN (...)". This query seems to be slow. I guess you need
> to analyze your application and check if the right indexes exist.
> Obviously, I can't say what is slow and why because I don't have your
> application. See also
> http://h2database.com/html/performance.html#application_profiling - I
> guess some of the queries use table scans which are slow (in any
> database).
>
> By the way, this also seems to use a lot of time:
>
> 259/18814
> at javax.xml.namespace.QName.equals(QName.java:329)
> at org.apache.xmlbeans.impl.store.Saver.processElement(Saver.java:421)
> at org.apache.xmlbeans.impl.store.Saver.process(Saver.java:307)
> at
> org.apache.xmlbeans.impl.store.Saver$TextSaver.saveToString(Saver.java:1727)
> at org.apache.xmlbeans.impl.store.Cursor._xmlText(Cursor.java:546)
> at org.apache.xmlbeans.impl.store.Cursor.xmlText(Cursor.java:2436)
> at
> org.apache.xmlbeans.impl.values.XmlObjectBase.xmlText(XmlObjectBase.java:1455)
> at com.transerainc.rtmc.xml.XmlSimulator.postXml(XmlSimulator.java:63)
> at com.transerainc.rtmc.xml.SimulatorTask.run(SimulatorTask.java:29)
>
> Regards,
> Thomas
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
