>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.
If it's not broken, fix it until it is.
My Blog