Post reply

Can VIEWs bring better performance for our queries?

  • December 6, 2006 at 3:22 pm

    #170643

    Hi,

    We are facing some performance issues, but we have just came across VIEWS as one good candidate to solve our problems.

    Our case is, we have a table which increases about 50.000.000 rows everyday, and we have lots of queries being run over that table (that is the main table of the application)... but in fact we care about almost only the "last week data". The rest of the data HAS TO BE available for the application, but we thought maybe a VIEW holding only the last week (or the last 300.000.000 of records) would improve the queries performance... a friend of mine told me about that, but it seems unlikely, because the query will still run over the BIG TABLE... Will a VIEW (subset of a huge table) act as if we had a smaller table (considering the performance)??

    Thanks in advance

  • December 6, 2006 at 5:08 pm

    #676474

    You should explore table partitioning..

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp

     

    MohammedU
    Microsoft SQL Server MVP

  • December 6, 2006 at 10:13 pm

    #676507

    In SQL 2000 there are indexed views and non-indexed views , in other words indexed views are materialised and non-indexed are not they are materialised when the view is referred to .. , which means direct load on the server resources .. , so you really need to weigh out the performance impacts and maybe even consider some kind of replication of only that table which can then be queried... , independent of the main db..   

  • December 7, 2006 at 7:03 am

    #676581

    R U working on SQL 2000 or 2005?

    If on SQL 2005 - Then think about Table Partitioning. And straight forward see regarding Stored Procedures/Dynamic Stored Procedures. And most important Ur Indexing must be very good.

    There is some facts which improves your query performance.

    1. Hardware --> Processer/Multi-Processer/RAM

    2. Partitioning --> Table/Indexes

    3. Implementation of Raid System

    4. Parallal Query Execution Plan

    5. Proper Indexing

    6. Your Stored Procedure/Queries Quality.

  • December 7, 2006 at 7:03 am

    #676580

    Partitioning of some sort is definitely the way to go here for best performance.  Too bad you aren't using SQL2005 - much better support for that there.  But for the optimizer to only read part of the data if you are querying the last week, you have to have some form of constraint to 'bucketize' the data so that the query optimizer knows "oh, you only want the last two days of data?  well then, this constraint tells me that all of that data must be in this 'subset' of the table I can find in this partition so I will just read from there".

    I would also ensure you are updating your statistics and indexes regularly too.  If you have a billion plus rows in this table, adding 50M would not likely trigger an update to statistics and thus any query plans the optimizer creates would likely be suboptimal. 

    I assume you DO have some useful indexes too, right?

    You may want to consider using optimizer hints (index, join options, force order) to ensure the queries always have the best plans.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • December 7, 2006 at 7:54 am

    #676609

    We have used partinoined tables and performance is MUCH better than before. I think this is the best option you have.

  • December 7, 2006 at 9:06 am

    #676655

    Thanks for the asnwers!

    YES, We are using SQL Server 2005

Viewing 7 posts - 1 through 6 (of 6 total)

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