1

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?

  • 2
    Alex, do you really mean view or do you mean table-vlaued variable? Also, which DBMS are you using, and which version? – Michael Green Apr 27 '14 at 13:00
  • I meant view. Sorry for getting back to this so late. I am using SQL Server 2008, 2008R2, and 2012. – Alex V May 5 '14 at 13:53
1

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.

1

An advantage of using global temp tables for debugging is that they persist after the SP completes--so long as that session doesn't exit--and then they are accessible from any session. Using a local temp table would require that it be created before running the SP which can be tedious depending on its definition.

A pattern like this can be used to capture multiple stage calculations within an SP without having to alter/copy/paste the SP. Performance is only compromised when debugging.

alter proc [usp_RebuildSomeTables]
    @pDebugTraceExtent   smallint = 0 -- mirror intermediate results to globals temp tables
as
begin try

    -- On production, raise error if @pDebugTraceExtent is non-zero

    -- ... Do Some Processing ...

    if (pDebugTraceExtent > 0)
    begin
        if (object_id('tempdb..##A') is not null)
            drop table ##A;

        select * 
          into ##A
          from #A;  -- or SourceViewA

        print 'Created ##A for Debug';        
        if (pDebugTraceExtent = 1)
            return;
    end

    -- ... More processing ...

    if (@pDebugTraceExtent > 0)
    begin
        if (object_id('tempdb..##B') is not null)
            drop table ##B;

        select * 
          into ##B
          from #B;  -- or SourceViewB

        print 'Created ##B for Debug';        
        if (@pDebugTraceExtent = 2)
            return;
    end
end try
begin catch 
   -- ...
end catch

Note there is a parsing bug that prevents using "select * into #N" more than once for the same temp table name within an SP.

0

Your question is spot on: when you query a view you’re still reading from all of the underlying tables. Every time. Now, you can create an indexed view which will persist the data on disk, but now you have to maintain that index. Indexed views work great for situations where your result set returns a lot of rows and needs to be aggregated. However, it is not a good idea to create an indexed view on tables that are highly transactional. This is because the database engine must maintain the index on the view as the base table data is updated, which can degrade transaction performance. Views are usually created for the following reasons:

Security - give the users access to views instead of directly accessing the tables. Simplicity - reuse complex queries. Intuitive naming - use to provide aliases on column names that make more sense to humans. Subquery - the view can be bits and pieces of many tables that is treated like a single entity in a select statement. Retain old structure - when application has schema changes, one can use views to mimic the old table structure so that any "forgotten" sprocs or adhoc queries will not break.

Views have limitations. Views cannot reference other views. They can't count. They can't select min, max, or distinct. They also can't order by, self-joins, outer joins or subquery. You can't change the underlying tables schema.

Temp tables don't have those limitations, so that could be a big deciding factor.

If you don't have to worry about those limitations, then the decision really depends on the specific implementation. Generally speaking, if it is going to be small, then go with a temp table. If it is going to be big, you will have to test and see which one performs better.

http://msdn.microsoft.com/en-us/library/ms191432.aspx

  • I presume you meant to say SQL Server "indexed views" specifically have those limitations but still the list is not correct. You can group by or use count_big. – Martin Smith Apr 30 '14 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.