I am running a simple SQL view that obtains data from two tables joined together to return all activity for a specific address for a specified time period. When I run, I receive the following run times for different time periods (all else is the same):

Month of October < 1 second
Month of September < 1 second
Both together > 45 seconds
9/1/2004 > 10/5/2006 < 1 second
9/1/2004 > 10/6/2004 > 45 seconds
9/2/2004 > 10/6/2004 < 1 second
9/3/2004 > 10/7/2004 > 45 seconds

Fast server, 4 processors, 2+ gig of memory, less than 5 active users.
Above times are consistant (multiple runs give same results).
Same results from program using view or through sql manager.

Small amount of data, only about 30 records are returned.
Run times go crazy once a certain threshold is reached.
Database has about 14 million records.
Indexes exist and have been recently updated for both address and date.
Server has not been rebooted in over a month.






Sephus S

I left off one piece of information, using SQL Server 2000.

Sephus S

It would be much clear to me if u give the Query inside the View.

If are sure that Index and statistics are updated and also IndexDefragmentation is done,Usage are updated (DBCC UPDATEUSAGE) then I like to drop the Index and recreate. If still problem, I feel a need of index on the View itself.

Certain limitations are there on indexed view but in your case I feel that those limitations are not barrier.

Create a Clustered Index on the view itself.

Good luck...

B. Anant

The view is:

SELECT tblCfsEvent.chrEventNumber, tblCfsEvent.dtDateTimeStart, tblCfsStreetName.chvStreetName
FROM tblCfsStreetName LEFT OUTER JOIN
                  tblCfsEvent ON dbo.tblCfsStreetName.ID =
                              tblCfsEvent.FK_insCfsStreetName
WHERE (tblCfsEvent.dtDateTimeStart > CONVERT(DATETIME, '2004-09-01 00:00:00', 102) AND tblCfsEvent.dtDateTimeStart < CONVERT(DATETIME,
                      '2004-10-15 00:00:00', 102)) AND (tblCfsStreetName.chvStreetName = 'Flora')


About 30 records are returned. If I use 10/5/2004 as the ending date, it runs in <1 second, if 10/6/2004 is used then it runs over 45 seconds and no additional records are returned.

As I just finished adding 7,000,000 records to the table a few weeks ago, I rebuilt all of the indexes. Since then, only about 10,000 records have been added to the file. The problem only started about a week back and it is very consistant.

The table holding the street names only has a table key and a street name and the table has about 10,000 entries.

I have run the same request in both the query analyzer and from SQL Manager and receive the same results. Requests continue to be very fast until I exceed some maximum size. The problem seems to be occurring on any requests using this database that exceed a certain size. At present, I do not know of any problems in any other of the databases on the server but all of the other applications have only a small percentage of as many records. As an example, I have a program run procedure, that searches three text fields in 60,000 records for the occurrance of specified text and it runs in under 2 seconds.

I will run DBCC UPDATEUSAGE when I get back to the office.

What do you mean by setting up a clustered index on the view?


S Cephus


Sephus S