Post reply

Querying a view instead of the table directly

  • June 3, 2006 at 4:35 am

    #67443

    Hello everyone,

    I have an application that queries a (large) table in SQL 2005 Express, that holds financial transactions. The table has a lot of fields but the query only selects about 10 of them.

    Will there be any performance gains if I create a view on this table using only the fields that are selected and then query the view instead of the table?

    The application (VB6) uses dynamic SQL to construct a string and then through ADO get a recordset and populate a grid.

  • June 6, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • June 6, 2006 at 7:51 pm

    #642183

    Usually, you would query the base table but specify only the attributes (aka columns) you need. Going againts a view which exposes only the columns you need can be done with the same effect.

    Not retrieving unneeded attributed will help a lot. Look at it this way:

    1000 cpu operations = 1 disk IO = 1.5 network IO

    Lowering the IO is ALWAYS a winner

    Using SELECT * is ALWAYS a bad practice {except special cases like ...EXISTS (Select *...  because the * does not retrieve anything}.

    Going with the view approach is OK, but remember you have to maintain both the view + the base table when there are changes; unnessesary overhead. I have also seen the opposite problem: people end up with lots of columns in a view because one of the scenarios need all those columns but they retrieve only a few most of the time...and the wonder why the view is slower than the base table (still has to compile and build a query plan).

    Hope that helps. Lower your IO and you will always win. The fastest server is always the one sitting idle

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

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