Performance of VIEW vs. SQL statement

Question

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)
)

Answer 1

Views aside, aren't the PrimaryKey AND clauses redundant? If the PrimaryKey value is IN a list, wouldn't it not be IN the other list? I think condensing those two clauses into one would boost performance.

Answer 2

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.

Answer 3

Regular (non indexes / materialized) Views are just aliases; they don't offer any performance advantages. Selecting from a View generates exactly the same query plan as selecting directly from the table.