[ http://www.datanucleus.org/servlet/jira/browse/NUCRDBMS-251?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17027#action_17027 ]
Yang ZHONG edited comment on NUCRDBMS-251 at 9/6/09 6:21 AM:
-------------------------------------------------------------
Only for the only purpose of Proof of Concept, changing org.datanucleus.store.rdbms.RDBMSStoreHelper.getClassNameForIdKeyUsingDiscriminator from
SQLExpression sqlFldVal = exprFactory.newLiteral(sqlStmt, idMapping, sm.getObject());
sqlStmt.whereAnd(sqlFldExpr.eq(sqlFldVal), true);
to
SQLExpression sqlFldVal = exprFactory.newLiteralParameter(sqlStmt, idMapping, sm.getObject());
sqlStmt.whereAnd(sqlFldExpr.eq(sqlFldVal), true);
sqlStmt.setRange(0, 1);
will generate
SELECT A0.JDOCLASSX FROM A A0 WHERE A0.S = ?
instead of
SELECT A0.JDOCLASSX FROM A A0 WHERE 'test' = A0.S
At the same time, since RDBMSStoreHelper.getClassNameForIdKeyUsingDiscriminator only needs only one result, I recommend
sqlStmt.setRange(0, 1);
for maximum performance possible.
Thanks.
was (Author: yang_zhong):
Only for the only purpose of Proof of Concept, changing org.datanucleus.store.rdbms.RDBMSStoreHelper.getClassNameForIdKeyUsingDiscriminator from
SQLExpression sqlFldVal = exprFactory.newLiteral(sqlStmt, idMapping, sm.getObject());
sqlStmt.whereAnd(sqlFldExpr.eq(sqlFldVal), true);
to
SQLExpression sqlFldVal = exprFactory.newLiteralParameter(sqlStmt, idMapping, sm.getObject());
sqlStmt.whereAnd(sqlFldExpr.eq(sqlFldVal), true);
sqlStmtMain.setRange(0, 1);
will generate
SELECT A0.JDOCLASSX FROM A A0 WHERE A0.S = ?
instead of
SELECT A0.JDOCLASSX FROM A A0 WHERE 'test' = A0.S
At the same time, since RDBMSStoreHelper.getClassNameForIdKeyUsingDiscriminator only needs only one result, I recommend
sqlStmtMain.setRange(0, 1);
for maximum performance possible.
Thanks.
> Prepared Statements should have been used instead of literals in order to improve performance big time
> ------------------------------------------------------------------------------------------------------
>
> Key: NUCRDBMS-251
> URL: http://www.datanucleus.org/servlet/jira/browse/NUCRDBMS-251
> Project: DataNucleus RDBMS
> Issue Type: Improvement
> Affects Versions: 1.1.2, 1.1.3, 1.1.4, 1.1.5
> Environment: Java 5, Linux
> Reporter: Yang ZHONG
> Priority: Minor
> Attachments: 251.zip
>
>
> Add this to JRE/lib/logging.properties:
> DataNucleus.Datastore.Native.level=FINE
> and change "java.util.logging.ConsoleHandler.level" to "FINE",
> or configure log4j,
> then the attached Test Case outputs:
> ... org.datanucleus.store.rdbms.SQLController executeStatementQuery
> FINE: SELECT A0.JDOCLASSX FROM A A0 WHERE 'test' = A0.S
> ... org.datanucleus.store.rdbms.SQLController executeStatementQuery
> FINE: SELECT 'org.datanucleus.test.A' AS NUCLEUS_TYPE FROM A A0 WHERE 'test' = A0.S AND A0.JDOCLASSX = 'org.datanucleus.test.A' UNION SELECT 'org.datanucleus.test.B' AS NUCLEUS_TYPE FROM A A0 WHERE 'test' = A0.S AND A0.JDOCLASSX = 'org.datanucleus.test.B' UNION SELECT 'org.datanucleus.test.C' AS NUCLEUS_TYPE FROM A A0 WHERE 'test' = A0.S AND A0.JDOCLASSX = 'org.datanucleus.test.C'
> which should have been
> SELECT A0.JDOCLASSX FROM A A0 WHERE A0.S = ?
> and
> SELECT A0.JDOCLASSX AS NUCLEUS_TYPE FROM A A0 WHERE A0.S = ? AND A0.JDOCLASSX = ? UNION SELECT A0.JDOCLASSX AS NUCLEUS_TYPE FROM A A0 WHERE A0.S = ? AND A0.JDOCLASSX = ? UNION SELECT A0.JDOCLASSX AS NUCLEUS_TYPE FROM A A0 WHERE A0.S = ? AND A0.JDOCLASSX = ?
> respectively in order for much better performance.
> Thanks!
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://www.datanucleus.org/servlet/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|