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

It can help to have an index in DESC order and also using direct comparison, e.g.

where StoredFare > LowerFare.

See if this helps. If not, you may try creating an index on the view for the date DESC and FareDiff columns.