View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004994 | SymmetricDS Pro | Improvement | public | 2021-05-18 06:02 | 2021-11-04 20:09 |
| Reporter | JaiGanesh S | Assigned To | elong | ||
| Priority | high | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.12.7 | ||||
| Target Version | 3.12.12 | Fixed in Version | 3.12.12 | ||
| Summary | 0004994: Performance of sync trigger is very slow in SQL Server while synchronizing large number of triggers | ||||
| Description | Hi, We have 200k tables in our sql server database. We have around 200 sym triggers and each trigger is expanded using $(targetExternalId). We are having around 100 nodes. Each set of 200 sym triggers, would be expanded to 100 nodes and eventually covering 20k sql tables. The db trigger creation process is very slow and it takes nearly 90 minutes for 15 nodes. When all 100 nodes are registered, this process could go for 8 hours and it is not acceptable from our business. We have tried increasing the thread count, but it didn't improve the performance. We did some analysis by running sql profiler. We found that the stored procedures used by jTDS to query the table schema were slow. Particularly the sproc sp_columns used to get the table columns is very slow. The procedure becomes slow as the number of database objects increase. Ideally the usecase for targetExternalId is when the schema is same for the expanded tables and only the table name differs. Following this, we were thinking that instead of querying the schema for all expanded tables in the targetExternalId trigger, we could query the schema for the first node and clone the schema for the remaining expanded nodes. This would speedup the process for triggers using the targetExternalId. Can you please work on improving the performance for synchronizing the triggers? | ||||
| Steps To Reproduce | SQL server 2017 Standard Edition Database with 200k tables Sym triggers: 200 triggers with $(targetExternalId) | ||||
| Tags | dialect: sql-server, sync-trigger, trigger | ||||
| related to | 0005050 | closed | elong | SymmetricDS Pro | Screens call sync triggers in contention with config router |
| related to | 0005023 | closed | pmarzullo | SymmetricDS | Sync Triggers should execute successfully in parallel by table |
| related to | 0005051 | closed | elong | SymmetricDS | Improve performance with sync trigger call for list of triggers |
| related to | 0005043 | closed | elong | SymmetricDS | Trigger router cache getting flushed every time initial load extract job runs |
|
|
We're considering switching to Microsoft's JDBC driver because JTDS is no longer being actively maintained. Microsoft's driver has improved over the years, so I think the need for JTDS has gone away. Can you switch to Microsoft's driver and see if the performance is better. We test against that driver and it is supported. |
|
|
Good to hear that SymmetricDS supports MS JDBC driver too. Can you please let me know how to switch to MS JDBC driver? |
|
|
Adding further to our analysis, we wrote a beanshell extension for the interface org.jumpmind.symmetric.config.ITableResolver where we handled the triggers with $(targetExternalId) keyword. In that extension, we queried the database for table schema for the first table and used the same schema for all other expanded tables for the trigger. In our case, the schema remains the same for the set of tables and only table name differs. This approach reduced the sync trigger process execution time a lot. We could get this working only for community edition but not for pro edition, as there is a built in extension for the interface in the pro edition. We don't know how to disable to inbuilt extension. Also we don't know the implication using Table.copy to copy the schema and reuse it for another table. As a poc we checked the resulting Table.toVerboseString output for cloned tables and they found to match except the table name. Please find the extension we used for our analysis. Hope it might help you to optimize the process for the specific targetExternalId usecase. resolve(catalog, schema, tables, platform, nodeService, trigger, useTableCache){ log.debug("ExpandTable Extension:resolve start"); if (trigger.getSourceTableName().contains("targetExternalId")) { log.debug("Expand Extension {} started",trigger.getSourceTableName()); boolean firstNode = true; Table firstNodeTable = null; nodes = nodeService.findNodesWhoPullFromMe(); nodes.addAll(nodeService.findNodesToPushTo()); for (Node node : nodes) { String tableName = FormatUtils.replaceToken(trigger.getSourceTableName(), "targetExternalId", node.getExternalId(), true); Table table = null; if(firstNode) { // read from database log.debug("Reading table schema for {} from database",tableName); table = platform.readTableFromDatabase(catalog, schema, tableName); firstNodeTable = table.copy(); firstNode = false; } else { // clone the schema of the first node table log.debug("Reading table schema for {} from cloned copy",tableName); table = firstNodeTable.copy(); table.setName(tableName); } //table = platform.readTableFromDatabase(catalogName, schemaName, tableName); if (table != null) { trigger.setSourceTableNameExpanded(true); tables.add(table); log.debug("Table expanded {} schema is {}",table.getName(),table.toVerboseString()); } } log.debug("Expand Extension {} ended",trigger.getSourceTableName()); } else { Table table = platform.getTableFromCache(catalog, schema, trigger.getSourceTableNameUnescaped(), !useTableCache); if (table != null) tables.add(table); } log.debug("ExpandTable Extension:resolve end"); } |
|
|
Are there any updates to this please? |
|
|
To switch to Microsoft JDBC driver, edit the engines/*.properties file and comment out the old line and add a new line for these lines: #db.driver=net.sourceforge.jtds.jdbc.Driver db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver #db.url=jdbc:jtds:sqlserver://hostname/databasename;sendStringParametersAsUnicode=false;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880;socketTimeout=360;appName=symds db.url=jdbc:sqlserver://hostname;databasename=databasename If you are on SymmetricDS version 3.11 or older, that's it. If you are on SymmetricDS version 3.12, then run the "symadmin module install mssql" command to download the Microsoft JDBC driver, or you can manually download it from Microsoft's website. |
|
|
Hi Tried with MS SQL jdbc driver and the performance is more or less the same. Thanks, JaiGanesh |
|
|
Thanks, the script was helpful. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2021-05-18 06:02 | JaiGanesh S | New Issue | |
| 2021-05-18 06:02 | JaiGanesh S | Tag Attached: dialect: sql-server | |
| 2021-05-18 06:02 | JaiGanesh S | Tag Attached: sync-trigger | |
| 2021-05-18 06:02 | JaiGanesh S | Tag Attached: trigger | |
| 2021-05-19 17:41 | elong | Assigned To | => elong |
| 2021-05-19 17:41 | elong | Status | new => feedback |
| 2021-05-19 17:41 | elong | Note Added: 0001940 | |
| 2021-05-20 04:26 | JaiGanesh S | Note Added: 0001941 | |
| 2021-05-20 04:26 | JaiGanesh S | Status | feedback => assigned |
| 2021-05-20 04:45 | JaiGanesh S | Note Added: 0001942 | |
| 2021-05-27 13:25 | JaiGanesh S | Note Added: 0001943 | |
| 2021-05-27 20:52 | elong | Note Added: 0001944 | |
| 2021-05-31 08:59 | JaiGanesh S | Note Added: 0001947 | |
| 2021-07-13 19:39 | elong | Relationship added | related to 0005050 |
| 2021-07-13 19:39 | elong | Relationship added | related to 0005023 |
| 2021-07-13 19:39 | elong | Relationship added | related to 0005051 |
| 2021-07-13 19:40 | elong | Relationship added | related to 0005043 |
| 2021-08-13 19:32 | elong | Status | assigned => resolved |
| 2021-08-13 19:32 | elong | Resolution | open => fixed |
| 2021-08-13 19:32 | elong | Fixed in Version | => 3.12.12 |
| 2021-08-13 19:32 | elong | Target Version | => 3.12.12 |
| 2021-08-13 19:32 | elong | Note Added: 0001992 | |
| 2021-11-04 20:09 | elong | Status | resolved => closed |