Post reply

Performance wise (view or Temp Table )Which one is better

  • December 18, 2012 at 3:43 am

    #272206

    Hi ,

    Table A is having 1 million Records, Table B is having 100 million Records.

    I need to show data (for report pupose) from both the tables using join basing conditions.

    Which one is better : View or Temp Table (Performance wise)

    Please help me in that

  • December 18, 2012 at 3:51 am

    #1569455

    Depends what you are trying to do.

    Please follow the link in my signature on posting code and data for the best help.

    But with a temp table you would have to load and then read, where as a view would be a read so less going on via a view.

    That being said why not a procedure?

    What is the structure of the tables?

    What is the index definitions on the tables?

    What is the joining criteria?

    Is there referential integrity between the tables?

  • December 21, 2012 at 5:58 am

    #1570867

    Regular views offer nothing from a performance standpoint. Views are simply replaced by their definition in the query submitted for execution and then optimized as if the query submittor had supplied the entirety of the view-query themselves.

    For example, if I have a view named dbo.SomeData defined as:

    CREATE VIEW dbo.SomeData

    AS

    SELECT ColumnName FROM dbo.SomeTable;

    GO

    and I say

    SELECT * FROM dbo.SomeData;

    this (in general terms) is what the query engine will eventually optimize after view-replacement is done:

    SELECT * FROM (SELECT ColumnName FROM dbo.SomeTable);

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply