[jira] [Commented] (SOLR-1262) DIH needs support for callable statements

["Joachim Martin (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/SOLR-1262?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13024930#comment-13024930 ] 

Joachim Martin commented on SOLR-1262:
--------------------------------------

I have been told by my Oracle DBA that DIH's use of Statement vs Prepared Statement is causing serious problems on the database side.  There is a performance gain by not having to re-parse a prepared statement, but more importantly, each Statement that needs to be re-parsed takes up space in the cache.  If you have repeating related entities (e.g. Author->Books[]), each related query is a unique statement.

Many developers, myself included, would never consider writing a database app without Prepared Statements for performance reasons.  I think it's even more important in a batch update situation where you are running N additional related entity queries.

I like the syntax of MyBatis' mapped statements: 

select field1, field2 from related_table where entity_id = #{id, jdbcType=NUMERIC}



> DIH needs support for callable statements 
> ------------------------------------------
>
>                 Key: SOLR-1262
>                 URL: https://issues.apache.org/jira/browse/SOLR-1262
>             Project: Solr
>          Issue Type: Improvement
>          Components: contrib - DataImportHandler
>    Affects Versions: 1.3
>         Environment: linux
> mysql
>            Reporter: Abdul Chaudhry
>            Assignee: Noble Paul
>            Priority: Minor
>             Fix For: Next
>
>
> During an indexing run we noticed that we were spending a lot of time creating and tearing down queries in mysql
> The queries we are using are complex and involve joins spanning across multiple tables.
> We should support prepared statements in the data import handler via the data-config.xml file - for those databases that support prepared statements.
> We could add a new attribute to the entity entity in dataConfig - say - pquery or preparedQuery and then pass the prepared statement and have values filled in by the actual queries for each row using a placeholder - like a ? or something else.
> I would probably start by hacking class JdbcDataSource to try a test but was wondering if anyone had experienced this or had any suggestions or if there is something in the works that I missed - I couldn't find any other bugs mentioning using prepared statements for performance.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@lucene.apache.org
For additional commands, e-mail: dev-help@lucene.apache.org