Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Indexed Views Expand / Collapse
Author
Message
Posted Tuesday, November 16, 2010 4:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 4:13 AM
Points: 6, Visits: 13
i am however a little dissappointed at the article. i mean its a bit short and uninformative. ive only been on this site a few months now, but i think if you have an audience of sql dev's/dba's reading, you want to be a bit more in depth. personally i only want to see advanced stuff, and i can understand posting beginner articles for the less experienced, but even still, be a bit more informative in your articles.

thats just me...
Post #1021853
Posted Tuesday, November 16, 2010 9:05 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 7, 2014 10:22 PM
Points: 1,126, Visits: 1,387
Good introductory article about Indexed view.

We need to be very careful while creating indexed view due to schema dependency. Once you have a indexed view, you can not modify the table structure which is referenced in the indexed view. In our product, we are giving patches whenever required or in new release, at that time, if we want to modify table schema due to whatever reason, we have to drop the indexed view first to modify the table structure.


Thanks
Post #1021897
Posted Wednesday, November 17, 2010 3:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:36 PM
Points: 6,002, Visits: 8,267
A disappointing article on an underrated feature.

Yes, indexed views can result in a huge savings on query execution times.
But indexed views also have their price. Someone who does not know that, might take this article as a pointer to create indexed views for every query ever used - and boy, would such a system crawl to slowdown when modifying data...

Each indexed view increases the cost of every data modification!

EDIT: I just noticed that there IS a short mention of this in the conclusion. But not as explicit as it should be. (The fact alone that I overlooked it at first is a tell-tale!)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1022015
Posted Wednesday, November 17, 2010 9:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 10:42 AM
Points: 96, Visits: 434
Indexed views and triggers: two things to avoid if at all possible. Next up: Cursors are good!

-------------------------------------------------------------------------------------------------
My SQL Server Blog
Post #1022234
Posted Wednesday, November 17, 2010 11:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:02 AM
Points: 989, Visits: 1,825
Here's a weird situation where having a clustered indexed view was a life saver --

At my last job, we had a table that stored notes for claims. This table was poorly designed, in that diary activities (i.e. things needing to be done on the claim) were mixed in with the notes. There were 40 million rows, of which only about 100,000 at any time were diary activities. I had no ability to change this as virtually everything the company did used that table and would have to be changed.

That table was clustered on claimid, which made sense for core application functionality, since the app brought up a claim and always looks at notes as an attribute of claims. No performance problems. However, over time, the business wanted a dashboard for adjusters that would show all of their diary actions in one view. This view would require looking at between several dozen to several hundred (maybe even a few thousand) rows in the notes table. However, those rows would be scattered throughout the table, and even though the index would tell you what rows you needed, the dashboard then required a crippling bookmark lookup to get the notes data needed. A covering index would in essence be a second copy of the whole table.

Solution: a clustered indexed view, clustered on adjusterid, where the query ONLY returned the diary activities. Yes, it stored the rows in full a second time on disk, but it was only 100,000 as opposed to 40 million rows. Performance wasn't ever a real problem because of how the business used them (typically only one user at a time in their notes) so no blocks or deadlocks, and update and delete time was acceptable. It was the only solution we could find to handle a situation where basically two clustered indexes were needed.

And no, starting from scratch, we'd have NEVER done that, at least not on my watch.
Post #1022371
Posted Wednesday, November 17, 2010 12:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:36 PM
Points: 6,002, Visits: 8,267
amenjonathan (11/17/2010)
Indexed views and triggers: two things to avoid if at all possible. Next up: Cursors are good!

Please explain.

Or, if we are just throwing terms without any backing, I'll offer:
* Indexed views, when used with care, are an invaluable performance tool.
* Triggers are great to guard consistency, but only when standard constraints fall short.
* Cursors, though heavily overused by new users, certainly have their place in the expert's tool box.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1022397
Posted Thursday, November 18, 2010 8:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 10:42 AM
Points: 96, Visits: 434
Hugo Kornelis (11/17/2010)
amenjonathan (11/17/2010)
Indexed views and triggers: two things to avoid if at all possible. Next up: Cursors are good!

Please explain.

Or, if we are just throwing terms without any backing, I'll offer:
* Indexed views, when used with care, are an invaluable performance tool.
* Triggers are great to guard consistency, but only when standard constraints fall short.
* Cursors, though heavily overused by new users, certainly have their place in the expert's tool box.


Every tool has its use. Some tools should not be used unless there is no other way, either because using these tools violates some larger no-no like data duplication or because they are resource hogs and there are generally better ways to get the same task accomplished. In fact, I would actually prefer changing the structure of the system to avoid their use altogether if that is possible. Indexed views are data duplication. It is to be avoided unless there is no other way.

What I meant in my original post is we're seeing all these articles lately about these crappy tools that should really be avoided. Hardly any nay-saying about them. They are only good for 1 or 2 very specific purposes and nothing else. If I were new to the industry I might run out and create a bunch of indexed views (data duplication NO NO NO!), triggers (Good luck changing that schema!), and cursors (I eat all your resources for lunch!). These three things are garbage, unless it truly is the only alternative. Cursors specifically I've never seen a solution using one that was faster or better performing than a set based solution or simply using a while loop. In fact they're so bad, I don't even know the syntax for how to create one!

A funny side note, I was asked in an interview once what I thought about cursors. I said well I feel they're so crappy I don't even know how to write one. There's always a better way than using a cursor. That was one of the reasons I was hired.

It's good to have a solid set of generalizations and things to avoid if at all possible. That would be a great article. The past two articles on indexed views and triggers sound like they come from a viewpoint that these are generally acceptable solutions without any conditions on use. But in reality these two things are tools that are last on the list of alternative solutions, not for use in every day development. Cursors I truly believe can be avoided altogether.


-------------------------------------------------------------------------------------------------
My SQL Server Blog
Post #1022968
Posted Thursday, November 18, 2010 9:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 2:44 PM
Points: 55, Visits: 430
>>>Cursors I truly believe can be avoided altogether.

An example of when a cursor is necessary: when interacting with a SQL-based API that encapsulates business logic (Microsoft Dynamics GP for example). Am I going to directly insert inventory adjustment transaction data into base tables in a set-based fashion? Not likely. Instead, I'll call the vendor's stored proc to insert a single transaction--even if I need to loop through a thousand source rows to call the procedure one at a time. (The benefits of encapsulation sometimes outweigh the performance and elegance of set-based operations.)

Another example would be complex recursive operations where the outcome is affected by earlier passes in the recursion (i.e. things that you can't do with a simple recursive CTE). Sometimes you do in fact need to walk through data a row at a time.

By all means, cursors should be avoided, set-based processing is the way to go, and for the vast majority of data operations you can avoid cursors. But I'd argue that cursors cannot always be avoided.

The same principle is true of indexed views. Yes, they duplicate data. Yes, they can often be avoided. Yes, they can be abused. But they are very helpful when used properly, and they can solve problems that you can't easily solve other ways.
Post #1022992
Posted Thursday, November 18, 2010 1:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 10:42 AM
Points: 96, Visits: 434
I always use a while loop if I need to process RBAR.

-------------------------------------------------------------------------------------------------
My SQL Server Blog
Post #1023156
Posted Thursday, November 18, 2010 5:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:36 PM
Points: 6,002, Visits: 8,267
amenjonathan (11/18/2010)
Indexed views are data duplication. It is to be avoided unless there is no other way.

Indexes are data duplication as well. Do you avoid them as well?
In both cases, you duplicate some data under total control of the DBMS, and accept the performance hit for modifications in exchange for the performance gain for retrieval.

There are many scenarios where indexed views are an invaluable tool. Frequently retrieved aggregates is the prime, but not sole, example. Sure, people should no more go overboard with indexed views as with indexes; I already made a comment about this in one of my first posts in this topic. But your telling people to avoid them if at all possible is eequally as bad as the article suggesting to use them for every other query. The truth is somewhere in the middle - know about their existence; use them when appropriate.

Cursors specifically I've never seen a solution using one that was faster or better performing than a set based solution or simply using a while loop. In fact they're so bad, I don't even know the syntax for how to create one!

Yes, it shows. If you had ever bothered to check the syntax, and to actually study the subject, you would know that the default options for cursors are cappy, but that a cursor with the correct options will always be faster than the WHILE loops you use for RBAR.

There's always a better way than using a cursor.

Really?
Here's my challenge, for an online auction database with auto-bid functionality.
CREATE TABLE Auctions
(AuctionID int NOT NULL,
OpeningPrice decimal(9,2) NOT NULL,
MinimumIncrement decimal(9,2) NOT NULL DEFAULT 0.01,
CloseTime datetime NOT NULL,
PRIMARY KEY (AuctionID)
);
CREATE TABLE Bidders
(BidderID int NOT NULL,
PRIMARY KEY (BidderID)
);
CREATE TABLE AutoBids
(BidderID int NOT NULL,
AuctionID int NOT NULL,
StartBid decimal(9,2) NOT NULL DEFAULT 1.00,
Increment decimal(9,2) NOT NULL DEFAULT 1.00,
MaxBid decimal(9,2) NOT NULL,
PlacedAt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
StartTime datetime NULL,
PRIMARY KEY (BidderID, AuctionID),
FOREIGN KEY (BidderID) REFERENCES Bidders,
FOREIGN KEY (AuctionID) REFERENCES Auctions
);
CREATE TABLE Bids
(BidderID int NOT NULL,
AuctionID int NOT NULL,
PlacedAt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
Amount decimal(9,2) NOT NULL,
ComesFromAutoBid char(1) NOT NULL DEFAULT 'N'
PRIMARY KEY (BidderID, AuctionID, PlacedAt),
FOREIGN KEY (BidderID) REFERENCES Bidders,
FOREIGN KEY (AuctionID) REFERENCES Auctions,
CHECK (ComesFromAutoBid IN ('Y', 'N'))
);

Bidders can enter a bid directly in the Bids table, or they can place a bidding instruction in the auto-bid table. The system will then automatically bid on their behalf - every minute (but not before StartTime, if specified), the system will bet the lowest of starting amount, current highest bid plus the auto-bids increment, or current highest bid plus the auctions minimum increment. Except, of course, if the bidder is already the highest bidder, or if the amount to be bid would exceed the maximum bid.

To support the auto-bid process, a procedure is run once every minute. This procedure should check each auto-bids once, in order of the moment they were placed, and generate a bid if the requirements are met. During the processing of auto-bids, no manual bids are accepted (this requirement is implemented by placing a table lock on the Bids table) - but we don't want customers to notice this, so processing time should be as short as possible.

I would choose a cursor here. I'm curious to see your better solution.

(EDIT: Added a missing closing parenthesis in the code block)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1023241
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse