VIEW与SQL语句的性能 [英] Performance of VIEW vs. SQL statement

问题描述

我有一个类似下面的查询：

  select< field list> 
来自< table list> 
其中< join conditions> 
和< condition list> 
和PrimaryKey（从< table list> 
中选择PrimaryKey，其中< join list> 
和< condition list>）
和PrimaryKey不在（选择PrimaryKey from< ;表列表> 
其中< join list> 
和< condition list>）
  

$ b b

子选择查询都有自己的多个子选择查询，我不显示，以免混乱语句。

其中一个我的团队的开发人员认为视图会更好。我不同意，SQL语句使用程序传递的变量（基于用户的登录Id）。

有什么硬和快速的规则，使用与使用SQL语句？对于常规表与对视图运行SQL语句本身存在什么样的性能增益问题。 （注意，所有的连接/其中条件是对索引列，所以不应该是一个问题。）

编辑澄清...

这是我使用的查询：

  select obj_id 
 object 
其中obj_id在（
（select distinct（sec_id）
 from security 
其中sec_type_id = 494 
和（
（sec_usergroup_id = 3278 
和sec_usergroup_type_id = 230）
或
（sec_usergroup_id（选择ug_gi_id 
来自user_group 
，其中ug_ui_id = 3278）
和sec_usergroup_type_id = 231）
） 
和sec_obj_id in（
 select obj_id from object 
其中obj_ot_id在（select of_ot_id 
 from obj_form 
 left outer join obj_type 
 on ot_id = of_ot_id 
 where ot_app_id = 87 
 and of_id in（select sec_obj_id 
 from security 
 where sec_type_id = 493 
 and（
（sec_usergroup_id = 3278 
 and sec_usergroup_type_id = 230）
或
（sec_usergroup_id（选择ug_gi_id 
来自user_group 
，其中ug_ui_id = 3278）
和sec_usergroup_type_id = 231）
）
）
和of_usage_type_id = 131 
）
）
）
）
或
（obj_ot_id（select of_ot_id 
 obj_form 
 left outer join obj_type 
 on ot_id = of_ot_id 
其中ot_app_id = 87 
和of_id in（select sec_obj_id 
来自security 
其中sec_type_id = 493 
和（
（sec_usergroup_id = 3278 
和sec_usergroup_type_id = 230）
或
（sec_usergroup_id（select ug_gi_id 
 from user_group 
其中ug_ui_id = 3278）
 and sec_usergroup_type_id = 231）
）
）
和of_usage_type_id = 131 
 
）
和
 obj_id不在（选择sec_obj_id 
从安全
其中sec_type_id = 494）
）
  

观看次数是一种组织工具，而不是效果提升工具。

从 SQL Server视图解析

当SQL语句引用
非索引视图时，解析器和查询
优化器分析
的SQL语句和视图以及
的源代码，然后将它们解析为单个
执行计划。没有一个计划
用于SQL语句，而单独的
计划用于视图。

I have a query that goes something like the following:

select <field list> 
from <table list>
where <join conditions>
and <condition list>
and PrimaryKey in (select PrimaryKey from <table list>
    where <join list> 
    and <condition list>)
and PrimaryKey not in (select PrimaryKey from <table list>
    where <join list>
    and <condition list>)

The sub-select queries both have multiple sub-select queries of their own that I'm not showing so as not to clutter the statement.

One of the developers on my team thinks a view would be better. I disagree in that the SQL statement uses variables passed in by the program (based on the user's login Id).

Are there any hard and fast rules on when a view should be used vs. using a SQL statement? What kind of performance gain issues are there in running SQL statements on their own against regular tables vs. against views. (Note that all the joins / where conditions are against indexed columns, so that shouldn't be an issue.)

EDIT for clarification...

Here's the query I'm working with:

select obj_id
from object
where obj_id in( 
(select distinct(sec_id) 
        from security 
        where sec_type_id = 494
        and (
            (sec_usergroup_id = 3278 
            and sec_usergroup_type_id = 230)
            or
            (sec_usergroup_id in (select ug_gi_id 
            from user_group 
            where ug_ui_id = 3278)
            and sec_usergroup_type_id = 231)
        )
        and sec_obj_id in (
        select obj_id from object 
        where obj_ot_id in (select of_ot_id 
            from obj_form 
            left outer join obj_type 
            on ot_id = of_ot_id 
            where ot_app_id = 87
            and of_id in (select sec_obj_id 
                from security
                where sec_type_id = 493
                and (
                    (sec_usergroup_id = 3278 
                    and sec_usergroup_type_id = 230)
                    or
                    (sec_usergroup_id in (select ug_gi_id 
                        from user_group 
                        where ug_ui_id = 3278)
                    and sec_usergroup_type_id = 231)
                    )                
            )   
            and of_usage_type_id  = 131
        )
        )   
        )
)
or 
(obj_ot_id in (select of_ot_id 
        from obj_form
        left outer join obj_type 
        on ot_id = of_ot_id 
        where ot_app_id = 87
        and of_id in (select sec_obj_id 
            from security
            where sec_type_id = 493
            and (
                (sec_usergroup_id = 3278 
                and sec_usergroup_type_id = 230)
                or
                (sec_usergroup_id in (select ug_gi_id 
                    from user_group 
                    where ug_ui_id = 3278)
                and sec_usergroup_type_id = 231)
                )
        )
        and of_usage_type_id  = 131

    )
    and
    obj_id not in (select sec_obj_id 
        from security 
        where sec_type_id = 494)
)

解决方案

Depending on the database vendor, in general, the execution of a query against a view combines the SQL defined in the View with the Where clause predicates and Order By clause sort expressions appended to the sql that you pass against the View, to come up with a combined complete SQL query to execute. This is then executed as though it had itself been passed to query processsor, so there should be no difference.

Views are an organizational tool, not a performance enhancement tool.

From SQL Server View resolution

When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.

这篇关于VIEW与SQL语句的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！