Hi,

I'm testing a web site done in ASP.NET against a SQL Server 2008R2 database.

The live system runs nice and snappy but I have created a test database with millions of records to test the response and this has shown the site to have slowed down quite a bit. For example refreshing a page takes about a second against the smaller live database but takes between 10 and 15 seconds against the test database.

This particular page displays 25 records in a grid which is based on this SQL:

SELECT     TOP (25) GUID, DateImported, OID, PNR, StoredFare, RMQ, LowerFare, FareDiff, Timestamp, DatedMonth, DatedYear, DatedQuarter, DatedDay, 
                      DatedWeekday, DatedTime, MFRA, ScriptResult, AgentID, PNRStatus, Segments
FROM         dbo.viewTransactions
WHERE     (FareDiff > 0)
ORDER BY Timestamp DESC

This view is defined like this:

SELECT     GUID, DateImported, OID, PNR, StoredFare, RMQ, LowerFare, StoredFare - LowerFare AS FareDiff, Timestamp, DATENAME(Month, Timestamp) AS DatedMonth, 
                      DATEPART(yyyy, Timestamp) AS DatedYear, DATENAME(quarter, Timestamp) AS DatedQuarter, DATENAME(Day, Timestamp) AS DatedDay, DATENAME(weekday, 
                      Timestamp) AS DatedWeekday, REPLACE(CONVERT(CHAR(5), Timestamp, 108), ':', '') AS DatedTime, MFRA, ScriptResult, AgentID, PNRStatus, Segments
FROM         dbo.Transactions

I have an index on Timestamp defined like this:

CREATE NONCLUSTERED INDEX [IDX_Timestamp] ON [dbo].[Transactions]
(
	[Timestamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The query plan of the select from the view is attached.

The interesting thing is that when I manually run the query of the top 25 against the view in SSMS on the server, the results are obtained in 0 seconds!

So, is there any way to work out what exactly is slow about refreshing the page and can the queries/views be optimized more?