It currently takes @ 56 seconds to retrieve a single snapshot, 52 of which are spent querying data. I wanted to know if indexing would help.
use Snapshots;
declare @id int
set @id = 574;
select * from Snapshot where ID = @id;
select * from OverviewPivotView where SnapshotID = @id;
select * from ReservePivotView where SnapshotID = @id;
select * from DealPivotView where SnapshotID = @id and CommodityName = 'Energy' order by Market, TransactionType;
select * from DealPivotView where SnapshotID = @id and CommodityName = 'Transmission' order by Market, TransactionType;
Index may or may not help. You have five SELECT statements, so the first thing to look for is which of these if any is taking the most resources, or whether they are all about the same. Also, how many rows are returned? If it is returning a large number of rows (hundreds of thousands), then it very well could be the time required to read and send all that data over the wire.
From the naming convention, I am guessing that at least the last four are views. If you find that it is one of those that is the culprit, look at the definition of that view. It may be that they have complex joins or other constructs that are causing the poor performance.
Once you identify what the bottleneck is, then it is quite possible that an index can help with that.
Another thing that I would consider is replacing the SELECT * with selects that explicitly list only the columns you need.
If you do SELECT * and the APP doesn't actually need all the columns, then you are sending ALL the columns over the wire, and the APP is ignoring some of them.
If some/all of the ones ignored are large text columns that will slow down the transmission A LOT. And, worse, if someone adds a huge text column to that table in the future any query using SELECT * which used to preform well will become a dog. And it will take A LOT of effort to fix them all at that time. Moral: Don't use SELECT *, specifically name all columns required.
Might be that you just put SELECT *in the example code for the purposes of this post though
