In one previous post Permanent Link to Fighting against ACS queries I mentioned I found a way to speed up ACS queries, but I was not allowed to share my findings with the community until yesterday (thanks Joe). A word of caution what I’m about to blog about requires an ACS database schema change, this schema change is easily reversible and won’t touch your data nevertheless it won’t be supported by PSS.
This speed up applies if:
- you’re keeping many partitions (days of data) on line
- the average time window selection for your reports is relatively small compared to the number of days kept in the ACS database
- your reports are using the CreationTime field as one of the selection parameters
The hack consists in adding a constraint to every closed partition, the constraint mandates the time period contained in the partition. Without this mod the data you’re interested in is searched in every partition, with the mod just the partitions that contain data in the selected time window are searched. This can make a huge difference in query execution time.
The sample statement for a generic partition is similar to the following.
ALTER TABLE [dbo].[dtEvent_074837bc_7b93_4930_a799_7fcb6778819e] WITH CHECK ADD CONSTRAINT [CK_dtEvent_074837bc_7b93_4930_a799_7fcb6778819e] CHECK (([CreationTime]>=’2009-10-30 01:00:25.000′ AND [CreationTime]<=’2009-10-31 01:02:17.000′))
To make it a permanent change you just need to iterate (for example one a day) the partition table for closed partitions and then add such a constraint based on the actual data contained in the partition. Due to the sensitivity of the mod I won’t post the TSQL needed, just drop me a line if you need it.
This posting is provided "AS IS" with no warranties, and confers no rights.