span8
span4
如何跨两个数据库比较一系列表?我们使用了一个读取.csv文件的设置,该文件有两列:
-列1是数据库1中的一个查询,和
-列2是数据库2中的一个查询
这两个查询应该返回相同的、有序的值列表(基本上是标识符列表),并将其提供给SQLExecutor。使用FeatureMerger比较两个结果流,UnmergedRequestor和UnUsedSupplier端口报告两个数据库中不存在的特性。这对于相对较小的表非常有用。如果查询的表有超过100万条记录,性能就会下降。我使用数据库读取器解决了这个性能问题。我指定了一次获取记录的数量(ORACLE)和批量读取大小(MSSQL),并将它们设置为每次获取100.000条记录。我可以使用WorkspaceRunner解决这个问题,但是我的问题是:SQLCreator/SQLExecutor转换器上的这些参数在哪里?
参数可在SQLExecutor:
参数可用的MSSQL阅读器:
参数可在Oracle Reader:
任何答复将不胜感激,以及任何有关如何解决这个“数据库比较”项目的意见。
所以我现在不能访问SQL Server实例,但是我使用Oracle运行了一些快速测试。阅读了大约160万条非空间记录后,我发现SQLExecutor与“classic”阅读器之间的差别非常小,即使将批量读取的大小设置为200、5000或100000也一样。
最佳时间与“经典”读者和批量阅读大小5000:57秒
使用SQLExecutor的最佳时间:1分10秒
差别不大,但不会改变游戏规则。
我想知道你们的数字是多少@helmoet,在SQLExecutor和reader之间。可能是某些特定于SQL服务器的问题导致了性能问题?
一个有趣的细节:有多少输入功能贵SQLExecutors有哪些?在我的经验,SQLExecutors实际上相当缓慢不必执行数千个查询(=输入功能),因为数据库服务器必须从头开始为每个输入功能分析SQL语句时,导致大量的开销。
绑定变量就已经解决了这个问题,但目前尚未在SQLExecutor支持,也看到这老想法,遗憾的是没有得到来自安全多的爱从用户;-)亚搏在线
https://knowledge.亚搏在线safe.com/idea/23829/sqlexecutor-support-for-bind-variables.html
@helmoet首先,我将尝试用FeatureJoiner替换您原来的工作流程中的FeatureMerger。亚搏在线这应该是更大的性能,但将使用更多的缓存,所以请确保您的FME_TEMP指向一个SSD驱动器。
如果你喜欢的话,你也可以先试用一下供应商的产品。只需确保提供供应商的阅读器是导航器中的第一个。然后您将只缓存供应商记录,而不是同时缓存请求者和供应商。
您还可以尝试使用基于JDBC的读取器来读取SQL Server数据库。
如果您希望使用SQLExecutor/Creator:我不熟悉用于批量读取的底层SQL,但是您可能需要启用PL/ SQL或类似的语言BULK COLLECT。也许我们的一位常驻数据库专家可以在这方面提供帮助@david_r
羞愧....
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX X X X使用Oracle测试查询读者(默认批量读取大小)X X X XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = -功能阅读总结= - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = -双2753901 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =总特征读2753901 = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = -功能写总结= - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =总功能写0 = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - 2翻译是成功的FME会话持续时间:2分5.4秒。(CPU: 87.1s用户,18.2s系统)END - ProcessID: 7676,峰值进程内存使用量:386108 kB,当前进程内存使用量:326028 kB Translation was SUCCESSFUL XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX X X X Test query using SQLExecutor X X X XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Features Read Summary =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ============================================================================== Total Features Read 0 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Features Written Summary =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ============================================================================== Total Features Written 0 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Translation was SUCCESSFUL with 1 warning(s) (0 feature(s) output) FME Session Duration: 2 minutes 24.9 seconds.(CPU: 116.7s用户,20.1s系统)END - ProcessID: 8456,峰值进程内存使用量:392392 kB,当前进程内存使用量:329276 kB Translation was SUCCESSFUL XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX X X X Test query using MSSQLReader (default Bulk Read Size) X X X XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Features Read Summary =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- GeoAsset.GEBIED_H 2753901 ============================================================================== Total Features Read 2753901 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Features Written Summary =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ============================================================================== Total Features Written 0 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Microsoft SQL Server Non-Spatial Reader: Closing `EGDB' .读取操作完成翻译成功,有14个警告(0个特征)输出)FME会话持续时间:2分17.7秒。(CPU: 116.4s user, 20.7s system) END - ProcessID: 4340, peak process memory usage: 376720 kB, current process memory usage: 316692 kB Translation was SUCCESSFUL XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX X X X Test query using SQLExecutor X X X XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Features Read Summary =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ============================================================================== Total Features Read 0 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Features Written Summary =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ============================================================================== Total Features Written 0 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Translation was SUCCESSFUL with 1 warning(s) (0 feature(s) output) FME Session Duration: 2 minutes 43.0 seconds.(CPU:139.2s用户,21.2s系统)END - 的ProcessID:9156,峰值进程的内存使用:381292 KB,当前进程的内存使用情况:321292 KB翻译成功
我只是检查Oracle的版本太:
结果:没有非常大的区别。我会重新考虑我的设置。谢谢你的协助@markat亚搏在线safe,@david_r。
©2019安全亚搏在线软件公司法律