View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0002062 | SymmetricDS | Bug | public | 2014-11-10 23:35 | 2014-12-01 12:26 |
Reporter | brickman | Assigned To | chenson | ||
Priority | normal | ||||
Status | closed | Resolution | fixed | ||
Product Version | 3.6.12 | ||||
Target Version | 3.6.14 | Fixed in Version | 3.6.14 | ||
Summary | 0002062: Slow SYM_DATA reads on Firebird | ||||
Description | SymmeticDS Version 3.6.12 Database: Firebird 2.5.3 We have observed that there are significant performance issues when SymmetricDS reads the blobs in SYM_DATA during the data extraction on the Firebird database. To diagnose this further, we developed a simple test application to populate a table with 100,000 rows with a blob sub_type 1 column, and captured the timings to read the entire contents of the table iterating through the java.sql.ResultSet. When reading the blob column, it took 29700 milliseconds to read the 100,000 rows, with 29400 milliseconds spent in reading the blob column, i.e. almost 30 seconds overhead with reading blobs. Running the same test against a varchar column, the 100K rows were read in 450 milliseconds. We raised a couple of bugs to Firebird to assess: http://tracker.firebirdsql.org/browse/JDBC-369 http://tracker.firebirdsql.org/browse/CORE-4601 At this stage it looks unlikely that Firebird will be able to optimise their blob reads. We were able to work around the Firbird limitation by casting the blob to a varchar. To integrate this into SymmetricDS, we added the following method to org.jumpmind.symmetric.db.firebird.FirebirdSymmetricDialect, which is a similar approach taken by the OracleSymmetricDialect: @Override public String massageDataExtractionSql(String sql, Channel channel) { if (channel != null && !channel.isContainsBigLob()) { sql = StringUtils.replace(sql, "d.row_data", "cast(d.row_data as varchar(10000))"); sql = StringUtils.replace(sql, "d.old_data", "cast(d.old_data as varchar(10000))"); sql = StringUtils.replace(sql, "d.pk_data", "cast(d.pk_data as varchar(500))"); } return sql; } This change significantly improved the performance of data extraction and improved our overall batch processing times. Can you please consider including this method to future SymmetricDS releases? | ||||
Steps To Reproduce | See description | ||||
Tags | No tags attached. | ||||
SymmetricDS: master 491bcb3e 2014-11-10 18:46:01 Details Diff |
0002062: Slow SYM_DATA reads on Firebird |
Affected Issues 0002062 |
|
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/firebird/FirebirdSymmetricDialect.java | Diff File | ||
SymmetricDS: master e1e3e9cd 2014-11-10 18:48:53 Details Diff |
0002062: Slow SYM_DATA reads on Firebird |
Affected Issues 0002062 |
|
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/interbase/InterbaseSymmetricDialect.java | Diff File | ||
SymmetricDS: master 447de258 2014-11-10 18:49:32 Details Diff |
0002062: Slow SYM_DATA reads on Firebird |
Affected Issues 0002062 |
|
mod - symmetric-core/src/main/resources/symmetric-schema.xml | Diff File |
Date Modified | Username | Field | Change |
---|---|---|---|
2014-11-10 23:35 | brickman | New Issue | |
2014-11-10 23:43 | chenson | Note Added: 0000596 | |
2014-11-10 23:47 | chenson | Fixed in Version | => 3.6.14 |
2014-11-10 23:47 | chenson | Target Version | => 3.6.14 |
2014-11-10 23:51 | chenson | Status | new => resolved |
2014-11-10 23:51 | chenson | Resolution | open => fixed |
2014-11-10 23:51 | chenson | Assigned To | => chenson |
2014-11-11 00:00 | chenson | Changeset attached | => SymmetricDS 3.6 r8920 |
2014-11-11 00:00 | chenson | Changeset attached | => SymmetricDS trunk r8921 |
2014-11-11 00:00 | chenson | Changeset attached | => SymmetricDS trunk r8922 |
2014-11-11 00:00 | chenson | Changeset attached | => SymmetricDS trunk r8923 |
2014-12-01 12:26 | chenson | Status | resolved => closed |
2015-07-31 01:49 | chenson | Changeset attached | => SymmetricDS master 447de258 |
2015-07-31 01:49 | chenson | Changeset attached | => SymmetricDS master e1e3e9cd |
2015-07-31 01:49 | chenson | Changeset attached | => SymmetricDS master 491bcb3e |