Executing Unused Fields in SQL View
I am using MS SQL Server.
When I define a database schema, I define a (non-materializable) view that includes many fields, for example, as follows (where "Subject" is the name of the table and the view is a self-join to the Topic Table):
CREATE VIEW View_Topic_Ancestor AS
SELECT
Subordinate.Id AS Subordinate_Id,
Subordinate.Folder_Id AS Subordinate_Folder_Id,
Subordinate.topicTitle AS Subordinate_topicTitle,
Subordinate.topicXhtml AS Subordinate_topicXhtml,
Subordinate.crossLinked AS Subordinate_crossLinked,
Superior.Id AS Superior_Id,
Superior.topicTitle AS Superior_topicTitle,
Superior.topicXhtml AS Superior_topicXhtml,
Superior.crossLinked AS Superior_crossLinked
FROM Topic AS Subordinate LEFT OUTER JOIN Topic AS Superior
ON Superior.Folder_Id = Subordinate.Folder_Id
AND
Superior.LeftValue = (SELECT MAX(Ancestor.LeftValue)
FROM Topic AS Ancestor
WHERE Subordinate.LeftValue > Ancestor.LeftValue
AND Subordinate.LeftValue < Ancestor.RightValue
AND Subordinate.Folder_Id = Ancestor.Folder_Id)
Later (at runtime) I use this view in a select statement like:
SELECT
T.Id AS Shared_Id,
V.Superior_Id,
V.Superior_topicTitle,
V.Subordinate_Id,
V.Subordinate_Folder_Id,
V.Subordinate_topicXhtml
FROM Topic AS T, View_Topic_Ancestor AS V
WHERE Folder_Id='e2eb2b68-738d-49ad-9787-a1e655b7973f'
AND T.crossLinked = V.Subordinate_Id
This SELECT statement does not reference (select) many of the fields that are in the view: for example, it selects the Subordinate_topicXhtml field but does not select the Superior_topicXhtml field.
My questions:
1) Do fields defined in the view, but not referencing runtime selection from the view, have a big performance impact? Suppose if you want the Superior_topicXhtml field to contain a lot of data (this is a very long string).
2) How can I check the answer myself? Is testing (measuring ellapsed time with a stopwatch) the only way, or is it possible to get an answer based on theory? I am using "Microsoft Server SQL Management Studio" for Microsoft SQL Server 2008 with SQL Express. I can see how to get (but haven't learned to interpret) the "estimated execution plan" for this query, but this only shows what is happening with the indexes and loops, not whether data is being fetched from unreferenced fields.
source to share
The query optimizer builds a query plan by combining sql that defines a view with the generated sql to "select" from the view into a single SQL query. If any particular attribute (column) is not needed in this combined optimized sql, it will not be used. In particular, if the final selection does not require it, it will not be sent over the wire. Also, if this is not required by any Where or Order By clause or anything else in the optimized sql, and all the attributes that the ARE needs are in the index, then this query can only read the index and not touch the table at all ... even thought he was considering referencing a column not in the index ... This can be seen by looking at the query execution plan for the query.
source to share
Once you have an execution plan displayed in Studio Management, you can use your mouse to hover over the nodes in the execution plan.
When you hover over, a large tooltip is displayed: which includes a "Result List" of the fields retrieved by these node fields and an "Estimated Line Count" that shows how many instances of these fields to read.
source to share