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

The filter didn't help. :(