SQL Server Best Practices – Staging Relation in Stored Procedure: Temp Table vs View

performancesql serverstored-procedurestemporary-tablesview

When I write stored procedures that take relation A and output relation Z, I like to create 'staging' relations along the way, so I can inspect the state of the data after each step in the programming logic is applied.

So far, I have been using temp tables to instantiate these staging relations.

Should I be using views instead? If this is the case, then what happens when different views are being joined in the same query, and these different views are hitting the same base tables? Does performance suffer?

Overall, which approach leads to best overall performance? Is there a way to give a general answer that applies to most scenarios, without having to run traces on specific implementations?

Best Answer

If you are doing this to aid debugging, rather than to facilitate restart after a failure. I would suggest temp tables.

Temp tables are visible only to the scope in which they are created. In other words, if two users execute the same stored procedure (SP) simultaneously they will be given different temp tables and most importantly their data will be completely isolated from each other's. Views are globally visible (permissions not withstanding) and data written by one can be seen by all, with the risk of leakage. If you define a view and write your working values to it you have to add extra columns to separate your values from other users'.

Temp tables are dropped automatically by the system when they go out of scope. This makes your debug code simpler since you don't have to program around dirty restarts after crashes.

Temp tables can be created on-the-fly with the SELECT..INTO syntax. Thus their schema will automatically keep up with changes to the surrounding schema. Views do not have this advantage.

If you are processing many rows you can index Temp tables with only the same drawbacks that indexing normal tables incurr. In SQL Server at least - which is my speciality - there are many restraints on a view's defintion before it can be indexed, which could affect their perormance or applicability in your case.

SQL Server makes table-valued variables available as a lighter-weight alternative to temp tables. See this SO question for the differences.

If you do happen to be on SQL Server, and are lucky enough to have 2014 available, and your row counts are modest, these working tables are an excellent candidate for non-persisted, in-memory tables.

Related Solutions

Sql-server – Views – Indexes/Keys/Execution Plans

You are correct you cannot use indexed views. Sadly what you have here is a business problem not a database problem. If you need to know the PKs and indexes from the other database and you don't have even viewing access, then require the DBAs who do have that access to provide the information you need to do your job or require them to write and performance tune the queries. No one should be writing queries against a database they do not have access to. If they won't give you access, then the work belongs to them too. I would have my boss talk to their boss and ask them to solve this set of problems for you. They created the problem it's up to them to fix it.

Sql-server – Performance of Inline-TVF vs. Views

The query optimizer treats an inline table valued function exactly like a view:

CREATE FUNCTION dbo.InlineUdf(@arg1 int)
RETURNS TABLE
AS
RETURN 
(
    ... your query here ...
);

A multi-statement table-valued function is run more like a stored procedure. They typically have to be executed multiple times, rather than be folded into the main query:

CREATE FUNCTION dbo.MultiStatementUdf (@col1 int)
RETURNS @result TABLE 
(
    id int primary key NOT NULL,
    ... 
)
AS
BEGIN
   DECLARE @var1 int
   set @var1 = 42

   INSERT @result
   SELECT ...
   RETURN
END;

Best Answer

Related Solutions

Sql-server – Views – Indexes/Keys/Execution Plans

Sql-server – Performance of Inline-TVF vs. Views

Related Question