[infinispan-issues] [JBoss JIRA] Commented: (ISPN-686) JdbcStringBasedCacheStore loads previous data from store before an insert

[Sanne Grinovero (JIRA)]

    [ https://jira.jboss.org/browse/ISPN-686?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12554810#action_12554810 ] 

Sanne Grinovero commented on ISPN-686:
--------------------------------------

Instead of using "ON DUPLICATE KEY UPDATE" I'm now using MySQL's other proprietary extension: "REPLACE". In case people wished to use MyISAM engine, they could even use "REPLACE DELAYED" which seems to nicely fit the requirements for this.
To use these I had to implement a new custom CacheStore extending JdbcStringBasedCacheStore; I could contribute this if there's interest, but I think there would be more value in fixing the existing JdbcStringBasedCacheStore and letting users override the full SQL statement, or plug this logic in the existing database autodetect: I guess other RDBMS could have similar tricks to use.

> JdbcStringBasedCacheStore loads previous data from store before an insert
> -------------------------------------------------------------------------
>
>                 Key: ISPN-686
>                 URL: https://jira.jboss.org/browse/ISPN-686
>             Project: Infinispan
>          Issue Type: Bug
>          Components: Loaders and Stores
>    Affects Versions: 4.0.0.Final, 4.1.0.Final, 4.2.0.ALPHA2
>            Reporter: Sanne Grinovero
>            Assignee: Mircea Markus
>             Fix For: 4.2.0.BETA1, 4.2.0.Final, 5.0.0.Final
>
>
> The JdbcStringBasedCacheStore implementation loads the previous data on an insert to find out if it should generate an UPDATE or INSERT statement, while it would perform much better to just verify if the primary key exists.
> On top of this, some databases provide ways to avoid the need to check at all, like MySQL's " INSERT ... ON DUPLICATE KEY UPDATE"
> [http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html]

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira