Can VIEWs bring better performance for our queries?

Question

Post reply

Can VIEWs bring better performance for our queries?

Jean Baro

SSC Enthusiast

Points: 144
More actions
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

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

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

MohammedU

SSCoach

Points: 19644

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

gcou SSC Veteran Points: 234 More actions · Answer 2

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..

Bhudev Prakash SSC-Addicted Points: 471 More actions · Answer 3

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

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