优化这个？ [英] optimize this?

查看：88 发布时间：2019/6/5 11:55:20 db2

本文介绍了优化这个？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

IBM有一个名为VSAM重定向器的VSE操作系统产品。

它允许您使用VSAM访问RDBMS表/视图，就好像它们是实际的一样。 VSAM文件。我们现在在Oracle之间进行比较，这是我们多年来一直在生产中运行的
和DB2，我们只是从
开始。我正在尝试的其中一个测试是看看

VSAM重定向器与DB2和Oracle的效率如何。

以下是两种类型的语句。 keygetstmt用于使用唯一键检索单个

行。 fwdkeyseekstmt用于获取keygetstmt检索的行之后的下一行

。（现在我想起来了我是

不确定为什么会使用keygetstmt，在这里，但那里既不是这里也不是

。）

无论如何，DB2似乎要慢得多。我不知道它是否是DB2本身，我们如何设置它，或者它运行在不同的

（更慢？）硬件上。下面是两个语句，后面是''转储''

，显示已用时间。奇数编号的查询执行keygetstmt

预编译语句。偶数编号的查询执行fwdkeyseekstmt

准备好的语句。

VSAM重定向服务器都是用Java编写的，顺便说一下，使用JDBC。

对于Oracle，我们使用的是oracle.jdbc.driver.OracleDriver（" thin" driver）。对于

DB2我们正在使用com.ibm.db2.jcc.DB2Driver。

Oracle

keygetstmt =

" SELECT *

来自VSAM_POSPAY_ISSUED_CHECKS

WHERE ACCOUNT_SERIAL_NUMBER =？"

fwdkeyseekstmt =

" SELECT UNIQRBACNT，ACCOUNT_SERIAL_NUMBER

FROM（SELECT UNIQRBACNT，ACCOUNT_SERIAL_NUMBER

来自VSAM_POSPAY_ISSUED_CHECKS

WHERE ACCOUNT_SERIAL_NUMBER？

ORDER BY ACCOUNT_SERIAL_NUMBER）

WHERE ROWNUM< 2

06：00：38：860 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃3 ---

06:00:38： 870 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃4 ---

06：00：38：880 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃5 ---

06：00：38：880 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃6 ---

06：00：38：890 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃7 ---

06：00：38：890 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃8 ---

06:00:38： 900 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃9 ---

06：00：38：900 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃10 ---

06：00：38：910 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃11 ---

06：00：38：910 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃12 ---

06：00：38：920 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃13 ---

06:00:38： 920 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃14 ---

06：00：38：920 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃15 ---

06：00：38：930 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃ 16 ---

06：00：38：940 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃17 ---

06：00：38：940 [ VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃18 ---

06：00：38：950 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃19 ---

06：00：38：950 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃20 ---

06：00：38：960 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃ 21 ---

06：00：38：960 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃22 ---

DB2

keygetstmt =

" SELECT *

来自VSAM_POSPAY_ISSUED_CHECKS

WHERE ACCOUNT_SERIAL_NUMBER =？

FETCH仅限第一行

仅为FETCH优化1行

fwdkeyseekstmt =

SELECT UNIQRBACNT，ACCOUNT_SERIAL_NUMBER

FROM VSAM_POSPAY_ISSUED_CHECKS

WHERE ACCOUNT_SERIAL_NUMBER？

订购ACCOUNT_SERIAL_NUMBER

FETCH第一个1行仅

仅限FETCH
OPTIMIZE FOR 1 ROW"

06：01：00：271 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃3 ---

06：01：00：281 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃4 ---

06：01：00：451 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃5 ---

06：01：00：461 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃6 ---

06：01：00：632 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃7 ---

06：01：00：642 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃8 ---

06：01：00：822 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃9 ---

06：01：00：822 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃10 ---

06：01：00：992 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃11 ---

06：01：01：020 [VSAM_POSPAY_ISSUED_CHECKS，1] - - 数据库查询＃12 ---

06：01：01：172 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃13 ---

06:01 ：01：192 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃14 ---

06：01：01：363 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃15 ---

06：01：01：373 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃16 ---

06：01：01：543 [VSAM_POSPAY_ISSUED_CHECKS，1] - - 数据库查询＃17 ---

06：01：01：553 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃18 ---

06:01 ：01：723 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃19 ---

06：01：01：743 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃20 ---

06：01：01：913 [VSAM_POSPAY_ISSUED_CHECKS，1] ---数据库查询＃21 ---

06：01：01：923 [VSAM_POSPAY_ISSUED_CHECKS，1] - - 数据库查询＃22 ---

最初，DB2语句不包括仅获取前1行

子句。实际上，加上它加速了很多，但仍然没有接近甲骨文的速度。

其他任何我可能做的事情用来加快速度？因为我们是模仿VSAM的
我不认为为所有

行做一个select语句然后从光标中取出它们就是我们想要的。再说一次，

也许就是这样。但是我想尽可能地保持现状，因为它不是我们的代码。

最后我们只做随机访问（使用唯一键选择一个只有一行

），但我认为这将是一个有点值得b
值得测试两者之间。也许不是。如果我只选择所有行

并完成它，那么速度是相当的。

谢谢！

Frank < br $> b $ b ---

Frank Swarbrick

高级开发人员/分析师 - 大型机应用程序

FirstBank Data Corporation - Lakewood，CO美国

IBM has a product for the VSE operating system called the VSAM Redirector.
It allows you to use VSAM to access RDBMS tables/views as if they were
actual VSAM files. We''re doing a comparison right now between Oracle, which
we''ve been running in production for many years, and DB2, which we are just
starting with. One of the tests I am trying is to see how efficient the
VSAM Redirector works with DB2 versus Oracle.

Below are two types of statements. keygetstmt is used to retrieve a single
row using a unique key. fwdkeyseekstmt is used to get the next row
following the row retrieved by keygetstmt. (Now that I think about it I''m
not sure why keygetstmt is used at all, here, but that''s neither here nor
there at this point.)

Anyway, DB2 seems to be significantly slower doing this. I don''t know if
it''s DB2 itself, how we have it set up, or that its running on different
(slower?) hardware. Below are the two statements followed by a ''dump''
showing the elapsed time. Odd numbered queries execute the keygetstmt
prepared statement. Even numbered queries execute the fwdkeyseekstmt
prepared statement.

The VSAM Redirector server is all written in Java, by the way, using JDBC.
For Oracle we''re using oracle.jdbc.driver.OracleDriver ("thin" driver). For
DB2 we''re using com.ibm.db2.jcc.DB2Driver.

Oracle
keygetstmt =
"SELECT *
FROM VSAM_POSPAY_ISSUED_CHECKS
WHERE ACCOUNT_SERIAL_NUMBER = ?"

fwdkeyseekstmt =
"SELECT UNIQRBACNT , ACCOUNT_SERIAL_NUMBER
FROM ( SELECT UNIQRBACNT , ACCOUNT_SERIAL_NUMBER
FROM VSAM_POSPAY_ISSUED_CHECKS
WHERE ACCOUNT_SERIAL_NUMBER ?
ORDER BY ACCOUNT_SERIAL_NUMBER )
WHERE ROWNUM < 2"

06:00:38:860 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #3 ---
06:00:38:870 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #4 ---
06:00:38:880 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #5 ---
06:00:38:880 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #6 ---
06:00:38:890 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #7 ---
06:00:38:890 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #8 ---
06:00:38:900 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #9 ---
06:00:38:900 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #10 ---
06:00:38:910 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #11 ---
06:00:38:910 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #12 ---
06:00:38:920 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #13 ---
06:00:38:920 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #14 ---
06:00:38:920 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #15 ---
06:00:38:930 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #16 ---
06:00:38:940 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #17 ---
06:00:38:940 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #18 ---
06:00:38:950 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #19 ---
06:00:38:950 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #20 ---
06:00:38:960 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #21 ---
06:00:38:960 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #22 ---

DB2
keygetstmt =
"SELECT *
FROM VSAM_POSPAY_ISSUED_CHECKS
WHERE ACCOUNT_SERIAL_NUMBER = ?
FETCH FIRST 1 ROW ONLY
OPTIMIZE FOR 1 ROW FOR FETCH ONLY"

fwdkeyseekstmt =
"SELECT UNIQRBACNT , ACCOUNT_SERIAL_NUMBER
FROM VSAM_POSPAY_ISSUED_CHECKS
WHERE ACCOUNT_SERIAL_NUMBER ?
ORDER BY ACCOUNT_SERIAL_NUMBER
FETCH FIRST 1 ROW ONLY
FOR FETCH ONLY
OPTIMIZE FOR 1 ROW "

06:01:00:271 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #3 ---
06:01:00:281 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #4 ---
06:01:00:451 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #5 ---
06:01:00:461 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #6 ---
06:01:00:632 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #7 ---
06:01:00:642 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #8 ---
06:01:00:822 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #9 ---
06:01:00:822 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #10 ---
06:01:00:992 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #11 ---
06:01:01:020 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #12 ---
06:01:01:172 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #13 ---
06:01:01:192 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #14 ---
06:01:01:363 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #15 ---
06:01:01:373 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #16 ---
06:01:01:543 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #17 ---
06:01:01:553 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #18 ---
06:01:01:723 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #19 ---
06:01:01:743 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #20 ---
06:01:01:913 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #21 ---
06:01:01:923 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #22 ---

Originally the DB2 statements did not include the "fetch first 1 row only"
clause. By adding that it sped it up quite a bit, actually, but still not
near Oracle''s speed.

Any other things I might be able to use to speed things up? Because we are
emulating VSAM I don''t think that doing a single select statement for all
rows and then fetching them from the cursor is what we want. Then again,
perhaps it is. But I''d like to keep things as close as possible to how they
are now, since it''s not really our code anyway.

In the end we only really do random access (select for one an only one row
using the unique key), but I thought that this would be a somewhat
worthwhile test between the two. Perhaps not. If I simply select all rows
and be done with it then the speed is pretty comparable.

Thanks!
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

优化这个？ [英] optimize this?

问题描述

推荐答案

相关文章

其他开发语言最新文章

热门教程

热门工具

登录关闭

优化这个？ [英] optimize this?

问题描述

推荐答案

相关文章

其他开发语言最新文章

热门教程

热门工具

登录 关闭

登录关闭