通过 StarSQL 访问 DB2 - 效率。输出参数或选择查询
我正在编写一些代码，用于查询在漂亮的旧 MainFrame 上运行的旧 DB2 数据库。查询必须用 StarSQL 编写，这样做的目标是大大减少MIP 对通过 Command.Text 传入的当前查询的使用。
对于那些不知道的人，您会根据大型机上的 CPU 使用率 (MIP) 付费（很多！），因此您希望事情尽可能高效地运行。您真的不想说“Select * From TableA”并将其作为 CommandType.Text 传递到数据库，因为它将需要编译该语句然后返回结果。您需要将其保存为过程（已编译）并删除您想要的确切列的 * 。
那么，我的问题。我自己无法回答这个问题，因为我们的 MainFrame 大师正在休假......
我有一个返回约 30 列的过程。通过 Select 查询返回这些内容，还是将它们作为输出参数返回，会更有效吗？这是通过存储过程实现的。
我并不担心 C# 代码的长度，而是担心那个该死的大型机的效率。
我需要考虑以下因素：
SELECT PHNS.CLNT_INTERNL_KY as CLNT_INTRNL_KY
使用额外的 CPU 使用率将该列名称应用于该列，但是使用游标将其保存为输出参数会更有效吗？
如果您想了解任何其他信息，请告诉我。
干杯，
（标签上“可能”有一个starSQL标签，可惜我没有1500分......）
I'm working on some code that queries an old DB2 database running on a nice old MainFrame. The Query has to be written in StarSQL The goal of this is to drastically reduce the MIP's usage of the current query that is being passed in via Command.Text.
To those that do not know, you get charged (a LOT!) based on CPU Usage (MIP) on mainframes, thus you want things to run as efficiently as possible. You don't really want to say "Select * From TableA" and pass that as a CommandType.Text to the Database because it's going to need to compile that statement and then return the results. You'd need to save that as a procedure (which is already compiled) and strip out the * for the exact columns you want.
So, my question. I cannot answer this myself because our MainFrame guru is on vacation...
I have a procedure that returns ~30 columns. Would it be more efficient to return these through a Select query, or by returning them as output parameters. This is through a Stored Procedure.
I'm not worried about length of C# code, but efficiency on that blasted Mainframe.
I need to take in account things like:
SELECT PHNS.CLNT_INTERNL_KY as CLNT_INTRNL_KY
Uses extra CPU usage to apply that column name to that column, but would it be more efficient to save that as a output parameter using cursors?
If you would like any other information let me know.
Cheers,
(There "could" be a starSQL Tag on the tags, but alas I'm not 1500 points...)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
许多查询 API 不允许使用存储过程，但如果您有此选项，则使用存储过程可以通过在编译时（而不是每次执行期间）解析和优化查询来节省一些 CPU 时间。如果没有，您仍然可以从动态 SQL 语句的缓存中受益。动态语句的访问计划临时存储在包缓存中，因此，如果很快遇到相同的逐字节相同语句（包括参数标记和文字值），DB2 将重用包缓存中的访问计划，而不是再次从头开始优化。
对于使用不同文字值频繁运行的语句，使用存储过程可以节省大量编译时间。在输入参数是可选的或变化很大的情况下（例如灵活搜索），存储过程可能会在编译时产生不需要的访问计划，因为它不知道在运行时将填充哪些参数。在这些情况下，存储过程可能需要在运行时通过 REOPT 策略重新优化查询，但这种方法显然会浪费预编译带来的节省。
我建议使用 DB2 的 EXPLAIN 工具来确定查询工作负载中的实际成本（编译、扫描、排序）。如果查询扫描大量行，则评估每一行的 CPU 成本可能会很快超过优化动态查询的费用。发出 SELECT * 的查询通常会阻止优化器利用可以用更少的 I/O 操作满足相同查询的索引。 WHERE 子句中的过滤和连接谓词（或缺少它们）也会阻止优化器选择索引。
Many query APIs do not permit the use of stored procedures, but if you have that option, using a stored procedure can save some CPU time by parsing and optimizing the query once at compile time rather than during every execution. If not, you may still benefit from the caching that takes place for dynamic SQL statements. The access plans for dynamic statements are temporarily stored in the package cache, so if the same byte-for-byte identical statement (including parameter markers and literal values) is encountered soon enough, DB2 will reuse the access plan from the package cache instead of optimizing it from scratch all over again.
Using stored procedures can save a significant amount of compile time for statements that are run very frequently with different literal values. In cases where the input parameters are optional or can vary considerably (such as flexible searches), a stored procedure could produce an undesirable access plan at compile time because it doesn't know which parameters will be populated at runtime. In those situations, the stored procedure may need to re-optimize the query at runtime through a REOPT policy, but that approach obviously takes away the savings of precompilation.
I'd recommend using DB2's EXPLAIN facility to determine where the real costs are in your query workload (compilation vs. scanning vs. sorting). If a query scans a significant number of rows, the CPU costs of evaluating each row can quickly surpass the expense of optimizing a dynamic query. Queries that issue SELECT * often prevent the optimizer from exploiting an index that could satisfy the same query with fewer I/O operations. Filter and join predicates in the WHERE clause (or the lack of them) can also prevent the optimizer from selecting an index.