locked
Which is faster when using SELECT query, VIEW or Table ? RRS feed

  • Question

  • User855629551 posted

    Hi,

    I have a database in that I have a view which inner joins four tables.

    1. If I create a new table with same structure & data of this view, and I query the table, will that fetch records more faster than fetching records from VIEW ?

    2. Another question is I am searching in six columns of view (Data type VARCHAR(size 500) ) to fetch records (I am using like operator '%searchtext%' ). My View has 5000 records. So this query is taking more time. How can I improve the performance of this query with out removing LIKE operator

    Please answer two of my questions

    Friday, July 8, 2011 4:50 AM

Answers

  • User455156504 posted

    Hi,

    if the table and the view have the same structure and data in it, a view is slower than a normaler query, because the view has to be generated from the table every time you query it. This is a step which is not necessary in a normal query. This is why a view has normally only a piece of data from a table. In this case a view is faster than a normal query.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 8, 2011 8:35 AM

All replies

  • User-269404413 posted

    Naresh,

    1. Views will improve the performance.

    2. Like operator always taking some time, what u can do is in the select query if any unncessary columsn are there then remove that.

    and in the same view if you have any rpimary key column wiht int datataype then add a where condtion as that column should > 0

     

    Friday, July 8, 2011 5:22 AM
  • User-595703101 posted

    Select from a View or from a table will not make too much sense.

    Of course if the View does not have unnessary joins, fields, etc. You can check the execution plan of your queries, joins and indexes used to improve the View performance

    You can even create index on views for faster search requirements. http://technet.microsoft.com/en-us/library/cc917715.aspx

    But if you are searching like '%...%' than the sql engine will not benefit from an index on text column. If you can force your users to make searches like '...%' than that will be fast

    Friday, July 8, 2011 6:38 AM
  • User-2082239438 posted

    An indexed view is similar to a table. The data touched by the view's definition have been materialized to disk. This is used mainly for aggregation, so we can avoid scanning the table / index everytime we need to pull aggregated data.


    Types of Views
    http://msdn.microsoft.com/en-us/library/ms190426(SQL.90).aspx


    In SQL Server 2008, SQL server has partitioned-aligned indexed views.


    Improving Performance with SQL Server 2008 Indexed Views
    http://msdn.microsoft.com/en-us/library/dd171921.aspx

    Friday, July 8, 2011 7:08 AM
  • User-1011137159 posted

    View is faster then select query and table.

    Friday, July 8, 2011 7:15 AM
  • User-336511135 posted

    View is faster then select from table. and

    You should change your DataBase design accordingly. In which you should not need to search such data with like operator. You should use Primary Key for searching the data. Like Operator reduce the performance. It is usefull when the searchable data is small but it is not recommended for large data searching. Instead of Like operator you can also use FreeTextSearch functionality of SQL Server.

    Friday, July 8, 2011 7:41 AM
  • User455156504 posted

    Hi,

    if the table and the view have the same structure and data in it, a view is slower than a normaler query, because the view has to be generated from the table every time you query it. This is a step which is not necessary in a normal query. This is why a view has normally only a piece of data from a table. In this case a view is faster than a normal query.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 8, 2011 8:35 AM