VIEW performance versus SQL statement

Question

I have a query that looks something like this:

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

In subsampling queries, there are several sub-sublayer queries of my own that I don’t show, so as not to clutter the operator.

One of the developers of my team believes that the presentation will be better. I do not agree that the SQL statement uses variables passed by the program (based on the user login ID).

Are there any hard and fast rules when using a view versus using an SQL statement? What performance problems arise when executing your own SQL queries compared to regular tables and views. (Note that all joins / conditions do not match indexed columns, so this should not be a problem.)

EDIT for clarification ...

Here is 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

Depending on the database provider, in general, executing a query on a view combines the SQL defined in the views with the Where clause and Order By clause added to the sql that you pass against the View to come up with a combined full SQL query to execute. Then it is executed as if it was passed to the request process itself, so there should be no difference.

Views are an organizational tool, not a productivity tool.

From SQL Server View Resolution

When an SQL statement refers to an unindexed view, the parser and query optimizer parses the source as an SQL statement and view and then resolves them into a single execution plan. There is more than one plan for the SQL statement and a separate plan for viewing.

Answer 2

Normal (not indexes / materialized). Views are simply aliases; they do not offer any performance benefits. Selecting from a view generates exactly the same query plan as selecting directly from a table.

Answer 3

Deviations to the side, are not redundant PrimaryKey AND clauses? If the PrimaryKey value is IN in the list, will it not be in another list? I think that compressing these two articles in one will increase productivity.