Sybase:从另一个存储过程中调用的存储过程太慢 [英] Sybase: Stored Procedure called from within another Stored Procedure is too slow

问题描述

我正在为 Sybase 数据库编写存储过程.我使用 Sybase Central 16.0 作为我的开发环境.我使用的机器运行 Microsoft Windows Server 2012 R2 标准版，并有 16GB RAM 在 2.8GHz CPU 上运行.

I'm writing a stored procedure for a Sybase database. I'm using Sybase Central 16.0 as my development environment. The machine I'm using runs Microsoft Windows Server 2012 R2 Standard and has 16GB RAM running on a 2.8GHz CPU.

我的存储过程使用一个游标来遍历一个表中的记录，该表有大约 400,000 条记录.每条记录都被增量写入 LONG VARCHAR 变量中，并且每第 N 条记录都会在变量上运行一个 MD5 哈希值并将该值存储在单独的表中.

My stored procedure uses a cursor to iterate over the records in a table with about 400,000 reecords.  Each record is written incrementally into a LONG VARCHAR variable and every Nth record the proc will run an MD5 hash value on the variable and store the value in a separate table.

TABLE_NAME, DATE_TIME_RAN, FROM_RECORD, TO_RECORD, HASH_VALUE

如果我只运行存储过程将此表作为 SQL Anywhere 中的 SQL 块进行散列(例如:BEGIN ... <在此处散列该表> ... END;)通过所有记录并在大约两分钟内成功完成.但是，如果我将此存储过程作为另一个存储过程中的嵌入式命令运行(例如:CALL .)，那么它永远不会完成.

If I run only the stored procedure to hash this table as a SQL block in SQL Anywhere (e.g.: BEGIN ... <hash the table here> ... END;) it goes through all records and completes successfully in about two minutes.  However if I run this stored procedure as an embedded command in another stored procedure (e.g.: CALL <MY_SCHEMA>.<MY_STORED_PROCEDURE>) then it never completes.

为什么在另一个存储过程中运行存储过程(在同一个数据集上)会有不同的表现?

Why would running the stored procedure (on the same dataset) from within another stored procedure perform differently?

推荐答案

设法找到了一个更好的解决方案，而无需浏览表格.我想我会在这里为可能面临类似问题的任何人发布解决方案.

Managed to find a much better solution to the issue without trawling through the table. I thought I'd post the solution here for anyone who might face a similar issue.

存储过程的要求是一次一行遍历一个大表，将行中每一列的内容连接成一个字符串并将其分配给一个变量.该变量的值每 N 行写入一个单独的表.以前我一直使用游标来执行此操作，但我们团队中的一位开发人员发现了使用 WITH 子句的更快方法.

The requirement for the stored procedure was to loop through a large table one row at a time, concatenating the contents of every column in the row into a string and assigning this to a variable. Every N rows this variable's value would be written to a separate table. Previously I had been using a cursor to do this but a developer on our team discovered a much faster way using the WITH clause.

INSERT INTO <MY_SCHEMA>.<MY_TABLE_THAT_WILL_CONTAIN_THE_CONCATENATED_VALUES_OF_EVERY_N_ROWS>
WITH 
    CONCATENATE_ROWS AS (
            SELECT RANK() OVER (ORDER BY <PRIMARY_KEY_COLUMN>) AS ROWID, <PRIMARY_KEY_COLUMN>, <COLUMN_1> || '' || <COLUMN_2> || '' || ... <COLUMN_N> AS ROW_DETAIL
            FROM <MY_TABLE_THAT_I_AM_QUERYING> 
            ORDER BY <PRIMARY_KEY_COLUMN>
    ), 
    GROUPED_ROWS AS (
            SELECT (((CONCATENATE_ROWS.ROWID-1)/ <number of rows I want to concatenate, e.g.: 10>)+1) AS GROUPID, CAST(LIST(CONCATENATE_ROWS.ROW_DETAIL, '' ORDER BY <PRIMARY_KEY_COLUMN>) AS VARCHAR(4000)) AS CONCAT_DETAILS
             FROM CONCATENATE_ROWS 
             GROUP BY (((CONCAT_ORDERS.ROWID-1)/ <number of rows I want to concatenate, e.g.: 10>)+1)
    )
SELECT <Whatever columns I want to insert into <MY_TABLE_THAT_WILL_CONTAIN_THE_CONCATENATED_VALUES_OF_EVERY_N_ROWS>> 
FROM GROUPED_ROWS;

这篇关于Sybase:从另一个存储过程中调用的存储过程太慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！