Sql-server – Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view

performancesql serversql-server-2005

Sorry for the long question, but let me give you my scenario. I have a DB that houses data exported from another DB. The containing tables are copies from the other (non-SQL) database. Each column name in these export tables is named using the following pattern:

UserFriendlyName_OriginalColumnName

Since our user's are primarily interested in the UserFriendlyName portion of the column, we have created a View for each table, where the name of each field is strictly the UserFriendlyName portion of the column name.

Most of these Views are strictly queries of one data table. For some of these views, however, I link to one other data table to pull out a description field or two. Note, this is never more than one linked table.

Performance wise, which method if better? If I am linking one data table to another for a description field, can I link straight to the description tables View so that I don't have to alias the column? Is it significantly better to use two data tables in the View and alias each column from the parent tables?

This may seem a bit arbitrary but this data exportation project is getting bigger and bigger. I'd like to formalize my process in how I link my information together. The goal is to strictly use the Data Views if and when possible. However, if this adds a sizable amount of performance degradation, I'd rather stick with always using Data Tables in all of my Data Views.

Best Answer

I might be misunderstanding your question. Are you asking if given the following tables and views:

CREATE TABLE dbo.Foo
(
    [col1_abc] INT
    , [col2_def] INT
)
GO

CREATE TABLE dbo.Bar
(
    [col1_ghi] INT
    , [col2_jkl] INT
)
GO

CREATE VIEW dbo.vwFoo
AS
SELECT
    [col1_abc] AS col1
    , [col2_def] AS col2
FROM
    dbo.Foo 
GO

CREATE VIEW dbo.vwBar
AS
SELECT
    [col1_ghi] AS col1
    , [col2_jki] AS col2
FROM
    dbo.Bar 
GO

Will this:

SELECT  
    f.col1
    , f.col2 
    , b.col2
FROM
    dbo.vwFoo f
INNER JOIN
    dbo.vwBar b
ON  b.col1 = f.col1

perform comparably to:

SELECT
    f.[col1_abc]
    , f.[col2_def] 
    , b.[col2_jki]
FROM
    dbo.Foo b
INNER JOIN
    dbo.Bar b
ON  b.[col1_ghi] = f.[col1_abc]

If that's the question the answer is yes, the performance will be identical.

In SQL2000, the optimiser sometimes struggled with deeply nested complex views joining to each other but not with simple scenarios such as this. I've not encountered the problem so much in 2005+.

Related Question