>>>>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.
>>
>>Thanks, the index on Timestamp DESC didn't help. Can you explain how to create an index on Storedfare > LowerFare, please?
>>
>>I can't create the index on FareDiff because "the view is not schema bound". Not sure how I can get around that.
>
>You can not create an index on expression. However, in SQL Server 2008 and up you can create filtered index. In the filter expression for TimeStamp desc try putting StoredFare > LowerFare.
>
>BTW, you can create your view with SCHEMA BINDING option which will allow you to create an index on the view.

Thanks I will try the filter. Regarding indexing the view I did the schemabinding and tried again, but then had to create a unique clustered index on it and that failed because:

Cannot create index on view "myview" because it contains the TOP keyword. Consider removing TOP or not indexing the view.