StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Views can be a detriment to performance when the view contains logic, columns, rows, or tables that aren't ultimately used by your final query. I can't tell you how many times I've seen stuff like:</p> <pre><code>SELECT ... FROM (View with complex UNION of ActiveCustomer and InactiveCustomer tables) WHERE Active = True </code></pre> <p>(thus filtering out all rows that were included in the view from the InactiveCustomer table), or </p> <pre><code>SELECT (one column) FROM (view that returns 50 columns) </code></pre> <p>(SQL has to retrieve lots of data that is then discarded at a later step. Its possible those other columns are expensive to retrieve, like through a bookmark lookup), or </p> <pre><code>SELECT ... FROM (view with complex filters) WHERE (entirely different filters) </code></pre> <p>(its likely that SQL could have used a more appropriate index if the tables were queried directly), or</p> <pre><code>SELECT (only fields from a single table) FROM (view that contains crazy complex joins) </code></pre> <p>(lots of CPU overhead through the join, and unnecessary IO for the table reads that are later discarded), or my favorite:</p> <pre><code>SELECT ... FROM (Crazy UNION of 12 tables each containing a month of data) WHERE OrderDate = @OrderDate </code></pre> <p>(Reads 12 tables when it only really needs to read 1).</p> <p>In <em>most</em> cases, SQL is smart enough to "see through the covers" and come up with an effective query plan anyway. But in other cases (especially very complex ones), it can't. In each of the above situations, the answer was to remove the view and query the underlying tables instead.</p> <p>At the <em>very least</em> (even if you think SQL would be smart enough to optimize it anyway), eliminating the view can sometimes make your own query debugging and optimization easier (a bit more obvious what needs to be done).</p>

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