I have a view that is comprised of another view and a table. It has 13,493 records. Not that big.
When I do a select ALL records, it takes about 3 seconds to return the dataset.
When I add a WHERE (LoggedInDate >= '1/1/2010') AND (LoggedInDate <= '12/31/2010') it takes about 3 seconds to return the dataset of 11,172 records.
When I add a WHERE (LoggedInDate >= '1/1/2011') AND (LoggedInDate <= '12/31/2011') it takes about 15 seconds to return the dataset of 2,316 records. At the bottom where it shows # of records its pulling it looks like it is chunking results in sets of 256 at a time.
I traced it back month by month and it looks like it starts this 'chunking' data results for anything loggedindate > 12/15/2010. The LoggedInDate is a datetime field. I checked other views, tables AND even those in different databases and realized that it was happenning to all of them.
Any ideas? Is there a process that was started that is stuck that I may not be seeing?