Re: Performance & SQL Formatting Issue
Please, could you publish your updated Spring.xml file. I 'm unable to find the place where to put that "poolPreparedStatements" property (It's more than probable that I'm missing one or more Spring bean declaration in my own context file ;) ). ZC. M Goodell wrote: Found the issue. Within the spring.xml file prepared statement pooling needs to be enabled on the data source. The default is false. Once set to "true" I am able to roughly achieve the same performance level Mr. Butler has in his tests. 2009-02-04 15:45:17,375 main springibatis.Main INFO - Inserting a total of [2] records into table. 2009-02-04 15:45:20,015 main springibatis.PersonServiceDAO INFO - Elapsed milli-seconds [FORMATTED]: 2234 2009-02-04 15:45:21,609 main springibatis.PersonServiceDAO INFO - Elapsed milli-seconds [UN-FORMATTED]: 1594 Order of execution swapped: 2009-02-04 15:45:51,968 main springibatis.Main INFO - Inserting a total of [2] records into table. 2009-02-04 15:45:54,421 main springibatis.PersonServiceDAO INFO - Elapsed milli-seconds [UN-FORMATTED]: 2031 2009-02-04 15:45:56,140 main springibatis.PersonServiceDAO INFO - Elapsed milli-seconds [FORMATTED]: 1719 -Original Message- From: Jeff Butler [mailto:jeffgbut...@gmail.com] Sent: Wednesday, February 04, 2009 12:22 PM To: user-java@ibatis.apache.org Subject: Re: Performance & SQL Formatting Issue I duplicated this code but removed all the external dependencies (Spring, DBCP, etc.). I just stripped it down to plain iBATIS and MySQL. My results show this: 2 Unformatted Inserts: 3 seconds Avg. 2 Formatted Inserts: 2.5 seconds Avg. HOWEVER, if I reverse the order in which the methods are called, I see this: 2 Formatted Inserts: 3 seconds Avg. 2 Unformatted Inserts: 2.5 seconds Avg. So I conclude that the 0.5 second difference has nothing to do with the SQL format, but is attributable to basic startup stuff. In other words, whichever method is called first is somewhat slower regardless of the format of the SQL. This is not surprising. The difference is not exponential, but fairly constant. If I increase the inserts to 5 or 10, the first method is still about 0.5 seconds slower. Again, not surprising. There may be a legitimate issue here, but I'm not convinced it has anything to do with iBATIS or SQL format (sorry). Jeff Butler On Wed, Feb 4, 2009 at 12:29 AM, M. Goodell wrote: Attached is a simple application showing perfomance data based on formatted/unformatted SQL insert statements. IDE=NetBeans 6.5 1. You will need to edit the spring.xml file and change the database config accordingly. 2. See included: people.sql for SQL DDL 3. See log4j.properties to adjust log output verbosity. 4. Dependencies (exact jars I used): commons-dbcp-1.2.2.jar commons-logging-1.1.1.jar commons-logging.jar commons-logging-api-1.1.1.jar commons-pool-1.4.jar ibatis-2.3.4.726.jar log4j-1.2.15.jar mysql-connector-java-5.1.7-bin.jar spring.jar (2.5.6) 5. Database: MySQL 5.1.30-community My results with this application on my system are: 2009-02-03 23:15:29,222 main springibatis.Main INFO - Inserting [2] records into table. 2009-02-03 23:15:39,519 main springibatis.Main INFO - Milli-seconds using [formatted] SQL:10297 2009-02-03 23:15:41,879 main springibatis.Main INFO - Milli-seconds using [un-formatted] SQL:2360 2009-02-03 23:15:41,879 main springibatis.Main INFO - Difference in milli-seconds: 7937 If there is more information needed I am glad to provide it. A *BIG* Thank You to everyone who has taken the time to look at this. If it's something I am doing / not doing please keep the name calling to a minumum. =) Thanks! M. Goodell --- Orange vous informe que cet e-mail a ete controle par l'anti-virus mail. Aucun virus connu a ce jour par nos services n'a ete detecte. __ Information from ESET NOD32 Antivirus, version of virus signature database 3830 (20090205) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
Re: Performance & SQL Formatting Issue
OK, I found it, it's in the "dataSource" bean declaration when using a "org.apache.commons.dbcp.BasicDataSource" implementation. I was using a "org.springframework.jdbc.datasource.SimpleDriverDataSource" implementation, that's why I couldn't find the "poolPreparedStatements" property. On a side track, I had horrible (slow) performance of batch insert, and I solve my problem by turning off the "defaultAutoComit" property. Pascal J. Pascal Jacob wrote: Please, could you publish your updated Spring.xml file. I 'm unable to find the place where to put that "poolPreparedStatements" property (It's more than probable that I'm missing one or more Spring bean declaration in my own context file ;) ). ZC. M Goodell wrote: Found the issue. Within the spring.xml file prepared statement pooling needs to be enabled on the data source. The default is false. Once set to "true" I am able to roughly achieve the same performance level Mr. Butler has in his tests. 2009-02-04 15:45:17,375 main springibatis.Main INFO - Inserting a total of [2] records into table. 2009-02-04 15:45:20,015 main springibatis.PersonServiceDAO INFO - Elapsed milli-seconds [FORMATTED]: 2234 2009-02-04 15:45:21,609 main springibatis.PersonServiceDAO INFO - Elapsed milli-seconds [UN-FORMATTED]: 1594 Order of execution swapped: 2009-02-04 15:45:51,968 main springibatis.Main INFO - Inserting a total of [2] records into table. 2009-02-04 15:45:54,421 main springibatis.PersonServiceDAO INFO - Elapsed milli-seconds [UN-FORMATTED]: 2031 2009-02-04 15:45:56,140 main springibatis.PersonServiceDAO INFO - Elapsed milli-seconds [FORMATTED]: 1719 -Original Message- From: Jeff Butler [mailto:jeffgbut...@gmail.com] Sent: Wednesday, February 04, 2009 12:22 PM To: user-java@ibatis.apache.org Subject: Re: Performance & SQL Formatting Issue I duplicated this code but removed all the external dependencies (Spring, DBCP, etc.). I just stripped it down to plain iBATIS and MySQL. My results show this: 2 Unformatted Inserts: 3 seconds Avg. 2 Formatted Inserts: 2.5 seconds Avg. HOWEVER, if I reverse the order in which the methods are called, I see this: 2 Formatted Inserts: 3 seconds Avg. 2 Unformatted Inserts: 2.5 seconds Avg. So I conclude that the 0.5 second difference has nothing to do with the SQL format, but is attributable to basic startup stuff. In other words, whichever method is called first is somewhat slower regardless of the format of the SQL. This is not surprising. The difference is not exponential, but fairly constant. If I increase the inserts to 5 or 10, the first method is still about 0.5 seconds slower. Again, not surprising. There may be a legitimate issue here, but I'm not convinced it has anything to do with iBATIS or SQL format (sorry). Jeff Butler On Wed, Feb 4, 2009 at 12:29 AM, M. Goodell wrote: Attached is a simple application showing perfomance data based on formatted/unformatted SQL insert statements. IDE=NetBeans 6.5 1. You will need to edit the spring.xml file and change the database config accordingly. 2. See included: people.sql for SQL DDL 3. See log4j.properties to adjust log output verbosity. 4. Dependencies (exact jars I used): commons-dbcp-1.2.2.jar commons-logging-1.1.1.jar commons-logging.jar commons-logging-api-1.1.1.jar commons-pool-1.4.jar ibatis-2.3.4.726.jar log4j-1.2.15.jar mysql-connector-java-5.1.7-bin.jar spring.jar (2.5.6) 5. Database: MySQL 5.1.30-community My results with this application on my system are: 2009-02-03 23:15:29,222 main springibatis.Main INFO - Inserting [2] records into table. 2009-02-03 23:15:39,519 main springibatis.Main INFO - Milli-seconds using [formatted] SQL:10297 2009-02-03 23:15:41,879 main springibatis.Main INFO - Milli-seconds using [un-formatted] SQL:2360 2009-02-03 23:15:41,879 main springibatis.Main INFO - Difference in milli-seconds: 7937 If there is more information needed I am glad to provide it. A *BIG* Thank You to everyone who has taken the time to look at this. If it's something I am doing / not doing please keep the name calling to a minumum. =) Thanks! M. Goodell --- Orange vous informe que cet e-mail a ete controle par l'anti-virus mail. Aucun virus connu a ce jour par nos services n'a ete detecte. __ Information from ESET NOD32 Antivirus, version of virus signature database 3830 (20090205) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com --- Orange vous informe que cet e-mail a été contrôlé par l'anti-virus mail. Aucun virus connu à ce jour par nos services n'a été détecté.
dynamic CREATE TABLE woe
Hi all,
I encountered a
'com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException' while
executing the following SQL script:
CREATE TABLE data
$[].name$ $[].type$
The java code that prepare the list parameter and execute the SQL script
is as follow:
...
sqlMapClient.startTransaction();
sqlMapClient.update("create-table", prepareCreateTableParams());
sqlMapClient.commitTransaction();
...
private List> prepareCreateTableParams(
final String[] columnNames, final String[] columnTypes) {
List> params =
new ArrayList>();
for(int i = 0; i < columnNames.length; i++) {
HashMap map = new HashMap();
map.put("name", columnNames[i]);
map.put("type", columnTypes[i]);
params.add(map);
}
return params;
}
and the exception is:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the create-seqnsdata-table-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near '
, , , , , , , '
at line 1
at
com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:107)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:66)
at net.eads.iw.seqns.CsvJdbcInjector.inject(CsvJdbcInjector.java:117)
... 1 more
Obviously, iBatis is not correctly replacing $[].name$ and $[].type$ by
the value supplied in the list of map of string, string.
Do I have my iBatis-SQL script wrong or do iBatis is not doing its job
right ?
Tanks in advance.
Re: dynamic CREATE TABLE woe
Investigating more, I found the following evidences in the
PreparedStatement sent by iBatis (I finally found the way to make iBatis
log under SLF4J):
22:29:39.296 [DEBUG] [main] [java.sql.PreparedStatement:26] -
{pstm-12} Executing Statement:
CREATE TABLE seqnsdb.seqnsdata ( {name=ID, type=INTEGER} {name=ID,
type=INTEGER}, {name=ARISING_SEQ_ID, type=INTEGER} {name=ARISING_SEQ_ID,
type=INTEGER}, {name=AC_SERIAL_NO, type=VARCHAR(255)}
{name=AC_SERIAL_NO, type=VARCHAR(255)} ... )
It is evident that iBatis is not transforming the SQLScript as expected !
It seems like the whole Map.Entry is printed each time instead of only
the name value first then only the type value.
AAMOF I did replace $[].name$ and $[].type$ by simply $[]$ in the
script and get the exact same result, which confirms that $[].name$
don't do anything more than $[]$ which is obviously not what is expected
(or there is something i didn't understood)
Hope this helps someone helping me ;)
Tanks in advance.
How to iterate over two (or more) collections simultaneously ?
Hi again, Is it possible to iterate over two (or more) collections SIMULTANEOUSLY using the same indice ? Here is a example: parameterMap="create-data-table-param"> CREATE TABLE data $names[]$ $types[]$ Nested iterate statements won't do it because it produce n*n outputs instead of just n (n is the number of elements in the collections). Any help would be appreciated.
Re: dynamic CREATE TABLE woe
I finally achieve success with my original SQLScript by patching ibatis
instead of having to do convoluted things in my source code that I could
not stand before a QA session.
in file:
ibatis-2.3.4.726\src\ibatis-src\com\ibatis\common\beans\ProbeFactory.java
in method: getProbe(), line: 33
replaced: return GENERIC;
by: return BEAN;
Hope this will make its way into the main build.
regards.
Poitras Christian wrote:
This is a bug in how iBATIS handles maps inside a list parameter.
Curiously, using #[].name# and #[].type# gave the expected result, but not
$[].name$ and $[].type$...
You can use a simple patch by putting your list of maps inside a map:
private Map>> prepareCreateTableParams(
final String[] columnNames, final String[] columnTypes) {
Map>> ret = new HashMap>>();
List> params =
new ArrayList>();
for(int i = 0; i < columnNames.length; i++) {
HashMap map = new HashMap();
map.put("name", columnNames[i]);
map.put("type", columnTypes[i]);
params.add(map);
}
ret.put("list", params);
return ret;
}
And in xml:
CREATE TABLE data
$list[].name$ $list[].type$
This should work.
Christian
-Original Message-
From: Pascal Jacob [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 22, 2008 4:59 PM
To: user-java@ibatis.apache.org
Subject: Re: dynamic CREATE TABLE woe
Investigating more, I found the following evidences in the PreparedStatement
sent by iBatis (I finally found the way to make iBatis log under SLF4J):
22:29:39.296 [DEBUG] [main] [java.sql.PreparedStatement:26] - {pstm-12}
Executing Statement:
CREATE TABLE seqnsdb.seqnsdata ( {name=ID, type=INTEGER} {name=ID,
type=INTEGER}, {name=ARISING_SEQ_ID, type=INTEGER} {name=ARISING_SEQ_ID,
type=INTEGER}, {name=AC_SERIAL_NO, type=VARCHAR(255)} {name=AC_SERIAL_NO,
type=VARCHAR(255)} ... )
It is evident that iBatis is not transforming the SQLScript as expected !
It seems like the whole Map.Entry is printed each time instead of only the name
value first then only the type value.
AAMOF I did replace $[].name$ and $[].type$ by simply $[]$ in the script and
get the exact same result, which confirms that $[].name$ don't do anything more
than $[]$ which is obviously not what is expected (or there is something i
didn't understood)
Hope this helps someone helping me ;)
Tanks in advance.
---
Orange vous informe que cet e-mail a ete controle par l'anti-virus mail.
Aucun virus connu a ce jour par nos services n'a ete detecte.
Re: dynamic CREATE TABLE woe
https://issues.apache.org/jira/browse/IBATIS-550
Poitras Christian wrote:
Can you add it to JIRA and submit your patch?
http://issues.apache.org/jira/browse/IBATIS
This will greatly help to add it to main build.
Christian
-Original Message-
From: Pascal Jacob [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 23, 2008 3:42 PM
To: user-java@ibatis.apache.org
Subject: Re: dynamic CREATE TABLE woe
I finally achieve success with my original SQLScript by patching ibatis instead
of having to do convoluted things in my source code that I could not stand
before a QA session.
in file:
ibatis-2.3.4.726\src\ibatis-src\com\ibatis\common\beans\ProbeFactory.java
in method: getProbe(), line: 33
replaced: return GENERIC;
by: return BEAN;
Hope this will make its way into the main build.
regards.
Poitras Christian wrote:
This is a bug in how iBATIS handles maps inside a list parameter.
Curiously, using #[].name# and #[].type# gave the expected result, but not
$[].name$ and $[].type$...
You can use a simple patch by putting your list of maps inside a map:
private Map>> prepareCreateTableParams(
final String[] columnNames, final String[] columnTypes) {
Map>> ret = new HashMap>>();
List> params =
new ArrayList>();
for(int i = 0; i < columnNames.length; i++) {
HashMap map = new HashMap();
map.put("name", columnNames[i]);
map.put("type", columnTypes[i]);
params.add(map);
}
ret.put("list", params);
return ret;
}
And in xml:
CREATE TABLE data
$list[].name$ $list[].type$
This should work.
Christian
-Original Message-
From: Pascal Jacob [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 22, 2008 4:59 PM
To: user-java@ibatis.apache.org
Subject: Re: dynamic CREATE TABLE woe
Investigating more, I found the following evidences in the PreparedStatement
sent by iBatis (I finally found the way to make iBatis log under SLF4J):
22:29:39.296 [DEBUG] [main] [java.sql.PreparedStatement:26] - {pstm-12}
Executing Statement:
CREATE TABLE seqnsdb.seqnsdata ( {name=ID, type=INTEGER} {name=ID,
type=INTEGER}, {name=ARISING_SEQ_ID, type=INTEGER}
{name=ARISING_SEQ_ID, type=INTEGER}, {name=AC_SERIAL_NO,
type=VARCHAR(255)} {name=AC_SERIAL_NO, type=VARCHAR(255)} ... )
It is evident that iBatis is not transforming the SQLScript as expected !
It seems like the whole Map.Entry is printed each time instead of only the name
value first then only the type value.
AAMOF I did replace $[].name$ and $[].type$ by simply $[]$ in the
script and get the exact same result, which confirms that $[].name$
don't do anything more than $[]$ which is obviously not what is
expected (or there is something i didn't understood)
Hope this helps someone helping me ;)
Tanks in advance.
--
- Orange vous informe que cet e-mail a ete controle
par l'anti-virus mail.
Aucun virus connu a ce jour par nos services n'a ete detecte.
---
Orange vous informe que cet e-mail a ete controle par l'anti-virus mail.
Aucun virus connu a ce jour par nos services n'a ete detecte.
