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)