SYBASE ASE 与 Microsoft SQL Server 在层次构建示例中的对比

db_developer

4.00/5 (1投票)

2010年9月30日

CPOL

6分钟阅读

31570

本文描述了现代工业级关系型数据库如何解决构建层级结构的任务，并顺带比较了 SyBASE ASE 和 Microsoft SQL Server SQL。

引言

我是一名Microsoft SQL Server 开发人员，我对这项技术充满热情。我曾经有过SyBASE ASE的经验。它本可以成为我的长期工作岗位。但最终没有。原因是：为了获得一份工作，我必须通过几轮测试和面试。其中一个阶段是实现不同复杂度的 SQL 查询。其中一项任务是构建层级关系。作为 Microsoft SQL Server 2008 的爱好者，我很快就完成了它……然后我被录用了。在所有安装/权限设置进行的同时，我的经理给了我一项任务，要求我使用 SyBASE 来实现我的测试，以填补这段时间。我在 SyBASE 中重复了这些查询，但之后我辞职了，继续寻找工作（当然是作为 Microsoft SQL Server 开发人员）。原因是 Microsoft SQL Server 从开发人员的角度来看，相对于 SyBASE 具有显著优势。本文将重点比较 Microsoft SQL Server 和 SyBASE 在构建层级结构方面的不同之处。

背景

本文是我对 SyBASE 的短暂接触的总结。我不是 SyBASE 的专家。因此，我不敢保证我关于 SyBASE SQL 的言论的客观性。本文不涉及上述两个关系型数据库在性能和成本方面的任何比较，我只关注 Microsoft SQL Server 和 SyBASE SQL 方言在 SQL 查询组合的速度和便捷性上的比较。

问题

我们有一个名为employees的表，其中包含主键 (employee_id) 和一个自引用的外键 (manager_id)，用于指示当前员工的经理 - 'null' 表示 '自管理'（见下图）。需要知道哪些经理拥有 8 个以上的下属。所以主要问题是**构建一个包含所有下属（包括间接下属）的完整层级表**（这是经典问题）。然后只需汇总数据并根据标准选择有趣的总计。那么，游戏开始吧！

employee entity with manger_id field to indicate manager of current employee

Microsoft SQL Server 2008 解决方案

设计

您可以使用**递归查询**构建一个包含给定员工所有下属的表。
将此查询打包成一个**返回表的函数**，该函数接受给定的经理 ID 作为 manager_id 参数。
您可以使用 `CROSS APPLY` 为每个员工调用创建的函数，该函数基于之前创建的函数。

实际上是解决方案

获取给定经理所有下属的函数

CREATE FUNCTION fn_get_all_subordinates(@manager_id AS bigint) 
    RETURNS @subordinates TABLE
(
    manager_id  bigint NOT NULL
   ,employee_id bigint NOT NULL
   ,employee_level int NOT NULL
)
AS
BEGIN
	with Manager_Subordinates(manager_id, employee_id, employee_level)
	as
	(
	-- Anchor Member (AM)
	select emp.manager_id, emp.employee_id, 0 employee_level
	from employees emp
	where emp.manager_id = @manager_id
	
	union all

	-- Recursive Member (RM)
	select ms.manager_id, emp.employee_id, employee_level + 1
	from employees emp
		join Manager_Subordinates ms
			on emp.manager_id = ms.employee_id
	)
	insert into @subordinates
	select manager_id, employee_id, employee_level
	from Manager_Subordinates ms;
	
	RETURN
END
GO

获取拥有 8 个以上下属（包括间接下属）的经理的 T-SQL 查询

select emp.employee_name
from
employees emp
	join
	--filter: all managers that have direct and indirect total 
         --subordinates more than 8
	(	
		select stat.manager_id employee_id
		from employees emp
			cross apply fn_get_all_subordinates(emp.employee_id) stat
		group by stat.manager_id
		having COUNT(stat.employee_id) > 8
	) stat
		on emp.employee_id = stat.employee_id

SyBASE ASE 解决方案

研究

首先，我遇到的问题是至少存在两种 SyBase。一种是 'ASE'，另一种是 'AnyWhere'。最糟糕的是，我必须处理前者，它的 SQL 功能更少。它不支持 With Recursive，因此不支持递归查询。这很糟糕，但这只是故事的开始。下一个奇怪之处是 SyBase ASE 不支持 TABLE 作为返回类型。因此，执行递归的唯一方法是在一个存储过程中创建一个临时表（'sharp-table'），然后从前一个存储过程调用另一个存储过程，并强制后者用行填充此表。这也不太好。所以，我能接受的唯一解决方案是迭代地构建层级结构（将递归算法转换为迭代算法是一个挑战——我喜欢它！）。

设计

您选择具有 'Top manager has null in her manager_id attribute' 标准的所有顶级经理。
然后，您通过将经理 ID 与 manager_id 中的值匹配来获取顶级经理的所有下属，并将它们保存到目标表中，其层级级别为 0 - 表示 '直接下属'（'目标表' 是包含 '经理-下属' 对以及 '下属级别' 的表）。
您获取级别 0 的下属的下属，并将它们保留为原始经理的级别 1 下属，然后您获取级别 1 的下属的下属——将它们保留为级别 2，依此类推。
这是迭代的结束。
然后，您将第一次迭代中的级别 0 下属视为下一个 '根'，并为 '新顶级经理' 重复表填充，然后获取级别 1 的经理，依此类推，直到达到最深层级的叶子。

算法假设：层级结构不包含循环，因为这违背了员工层级关系的本质。如果存在循环，您将得到一个无限循环。但是，您可以通过在实际插入之前检查表中是否存在要插入的行来轻松扩展算法以处理这些情况。

实际上是解决方案

这是支持填充测试数据的脚本（请纠正它以确保数据库的完整性）

create table employee
(
    employee_id numeric(38,0) identity primary key
    ,employee_first_name nvarchar(20) not null
    ,employee_last_name nvarchar(30) not null
    ,employee_name as employee_last_name + ', ' + substring(employee_first_name, 1, 1)
    ,manager_id numeric(38,0) null
    ,group_id numeric(38,0) null
    ,title_id numeric(38,0) DEFAULT 4 not null 
    ,salary_type char(1) not null
    ,salary_amount numeric(15,4) not null
)  

insert into employee(employee_first_name, employee_last_name, _
	manager_id, group_id, title_id, salary_type, salary_amount)
values('Joe', 'Kilik', null, null, 1, 'w', 5000)
insert into employee(employee_first_name, employee_last_name, _
	manager_id, group_id, title_id, salary_type, salary_amount)
values('Sue', 'Kilik', 1, null, 2, 'w', 4500)
insert into employee(employee_first_name, employee_last_name, _
	manager_id, group_id, title_id, salary_type, salary_amount)
values('Sarah', 'Gilt', 2, 2, 1, 'w', 5000)
insert into employee(employee_first_name, employee_last_name, _
	manager_id, group_id, title_id, salary_type, salary_amount)
values('John', 'Kahl', 3, 2, default, 'w', 3000)
insert into employee(employee_first_name, employee_last_name, _
	manager_id, group_id, title_id, salary_type, salary_amount)
values('Jimmi', 'Gross', 3, 2, default, 'w', 2800)
insert into employee(employee_first_name, employee_last_name, _
	manager_id, group_id, title_id, salary_type, salary_amount)
values('Carlos', 'Castanello', 3, 5, default, 'w', 7000)
insert into employee(employee_first_name, employee_last_name, _
	manager_id, group_id, title_id, salary_type, salary_amount)
values('Doe', 'Johns', 1, null, 2, 'w', 2500)
insert into employee(employee_first_name, employee_last_name, _
	manager_id, group_id, title_id, salary_type, salary_amount)
values('Gad', 'Real', 7, null, 3, 'w', 3500)

select * from employee order by manager_id

/*
    Joe
        Doe
            Gad
        Sue
            Sarah
                Carlos
                Jimmi
                John        
*/

这是获取层级结构的脚本（SyBASE ASE 实现）

    declare @cur_lvl numeric(2,0)
    declare @cur_iter numeric(4,0)

    declare @rows_selected int

    create table #all_submits           -- result table
    (
        manager_id numeric(38,0)
        ,submit_id numeric(38,0)
        ,submit_level numeric(2,0)
        ,product_iter numeric(4,0)
    )
   
    --insert roots (first)
    set @cur_iter = 0

    insert into #all_submits (manager_id, submit_id, submit_level, product_iter)
    select employee_id, employee_id, -1, @cur_iter
    from employee mgr where manager_id is null


    while (1=1)      -- loop over levels of management
    begin    
        
        set @cur_lvl = 0

        while (1=1)      -- loop over levels of submits
        begin
            --insert submits for current management level
            insert into #all_submits (manager_id, submit_id, submit_level, product_iter)
            select mgr.manager_id, sbm.employee_id, @cur_lvl, @cur_iter
            from employee sbm join #all_submits mgr on sbm.manager_id = mgr.submit_id 
            where product_iter = @cur_iter and mgr.submit_level = @cur_lvl - 1
        
            set @rows_selected = @@ROWCOUNT
        
            if (0 = @rows_selected)
                break

            
    
            set @cur_lvl = @cur_lvl + 1
        end

        --insert roots (next)
        insert into #all_submits (manager_id, submit_id, submit_level, product_iter)
        select submit_id, submit_id, -1, @cur_iter + 1
        from #all_submits mgr 
        where submit_level = @cur_iter and product_iter = 0
    
        set @rows_selected = @@ROWCOUNT
        
        if (0 = @rows_selected)
            break

        set @cur_iter = @cur_iter + 1

    end

    --this is script select
    select mng.employee_first_name manager_name, _
	sbm.employee_first_name submit_name, s.submit_level
    from #all_submits s
        join employee mng on mng.employee_id = s.manager_id
        join employee sbm on sbm.employee_id = s.submit_id
    where s.manager_id <> s.submit_id
    order by product_iter, submit_level, mng.employee_first_name
    
    drop table #all_submits

这是获取相关经理的部分（您应该替换上面脚本中的 select）

    select mng.employee_first_name, submits_amount
    from
    (
        select manager_id, count(*) submits_amount
        from #all_submits s
        where s.manager_id <> s.submit_id
        group by s.manager_id
        having count(*) > 8
    ) stat
        join employee mng on mng.employee_id = stat.manager_id

    order by submits_amount desc

我在 SyBASE 上处理这项任务花费了大约 3-4 小时（180-240 分钟），而在 Microsoft SQL Server 2008 上只花了 15-25 分钟。而且谁知道它还隐藏了多少 bug，尽管花费了大量时间进行测试/调试。简单的数字胜过千言万语。我不知道 SyBASE 的成本是多少，但很明显，使用 Microsoft SQL Server 进行开发/维护由于编码效率高（在此示例中快 9-12 倍）而大大降低了成本。

控制截图

查看 SyBASE 中 '迭代' 算法的实现，我不喜欢为了从 `#all_submits` 表中获取 ID 来扫描整个表，以获取第一次迭代的下一个提交级别（'insert roots (next)' 部分，在外层循环内紧跟着内层循环）。最好创建一个结构来快速选择相关的行。Microsoft SQL Server 2008 中的筛选索引可以完美地处理这个问题。它创建了一个数据结构来支持快速查找，这个结构只包含我们感兴趣的元素，并且由于所有后续的插入操作都在索引范围之外进行（它是筛选过的！），因此它不会被更改。我们将 `FILLFACTOR` 设置为 100 以避免索引中的任何间隙，因为我们只构建一次索引，并且之后不会再对其进行插入（我们只覆盖第一次迭代的数据）。为了避免转移到聚集索引，我们通过 `INCLUDE` 选项将相关数据包含在该索引中。

创建筛选索引以加速从特定范围选择数据

        ...........
        
                break            
    
            set @cur_lvl = @cur_lvl + 1
        end

		--build filtered index on first iteration product
		IF (0 = @cur_iter)
		BEGIN
			--create index on temporary table
			CREATE NONCLUSTERED INDEX NCI_iter0_lvl_AllSubmits
			ON #all_submits(product_iter, submit_level)
			INCLUDE(submit_id)
			WHERE product_iter = 0
			WITH (FILLFACTOR=100)
		END

        --insert roots (next)
        insert into all_submits (manager_id, submit_id, submit_level, product_iter)

        ............

强制使用创建的索引来选择下一个级别的提交

        ............

        --insert roots (next)
        insert into all_submits (manager_id, submit_id, submit_level, product_iter)
        select submit_id, submit_id, -1, @cur_iter + 1
        from all_submits mgr 
        WITH (FORCESEEK, INDEX(NCI_iter0_lvl_AllSubmits)) --force using of created index
        where product_iter = 0 and submit_level = @cur_iter

        ............

Oracle 解决方案

为了完善画面，我再加入一个值得称赞的竞争对手——Oracle。它获得了第一个奖项。Oracle 提供了 SELECT ... START WITH initial-condition CONNECT BY PRIOR recurse-condition。在 Microsoft SQL Server 2008 中，只有 `hierarchyid` 类型可以与 Oracle 的解决方案争夺第一名，但它需要更改设计，而 Oracle 则处理 '经典' 的父子关系实现。

分层选择数据

select lpad(' ',2*(level-1)) || employee_first_name s 
  from employee
  start with manager_id is null
  connect by prior employee_id = manager_id;

结论之外

Microsoft SQL Server 2008 远远领先于 SyBASE，尽管它不是绝对的领导者。它需要吸收市场上存在的许多便利的解决方案。我指的是 Oracle 的 'Windows 类'，例如。当然，Microsoft SQL Server 2008 支持 aggregate_function over(partition by ... order by ....)，但这与 Oracle 的动态窗口游标和允许引用该游标内部行的函数相比，微不足道……

因此，我希望 Microsoft 很快能实现类似的功能，以及许多其他便利的功能。

历史

2010 年 9 月 30 日：初次发布