Databases
StackOverflow2013
Postsdbo
POPerformance of VIEW vs. SQL statement
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPerformance of VIEW vs. SQL statement
    text
    copied!<p>I have a query that goes something like the following:</p> <pre><code>select &lt;field list&gt; from &lt;table list&gt; where &lt;join conditions&gt; and &lt;condition list&gt; and PrimaryKey in (select PrimaryKey from &lt;table list&gt; where &lt;join list&gt; and &lt;condition list&gt;) and PrimaryKey not in (select PrimaryKey from &lt;table list&gt; where &lt;join list&gt; and &lt;condition list&gt;) </code></pre> <p>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.</p> <p>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).</p> <p>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.)</p> <p>EDIT for clarification...</p> <p>Here's the query I'm working with:</p> <pre><code>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) ) </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload