Welcome to weblogs.com.pk Sign in | Join | Help

To Indexed View Or Not?

This year; we computerized the attendance system of our company. And as the year is ending; everyone is expecting a yearly attendance report. When system was made; it was not stressed test; and today; when I evaluated the report to see it will work when people will view it tomorrow; it didn’t work (as expected </smile>)

The report is based on UDF “EmployeeInOuts” which is an abstraction on “Attendance” view. Attendance view uses “Employees” view and “Payroll_Attendances” table. “Employee” view again is an abstraction on “HR_Employees” and “HR_Departments” tables. This hierarchy of abstraction was required to integrate some of our enterprise applications

The first thing that came to my mind was to narrow down data retrieval between “EmployeeInOuts” and “Attendance”; doing so reduced execution time from 1:20-1:23 to 0:40-0:41

To make situation better; I thought to create index on the view. This is where problems started, for the index on view; the view must be schema bound; and for any view to be schema bound; all the underlying objects should be schema bound. Once things were updated; I came to know that one cannot create index unless there exists at least one clustered index. For the clustered index; the view should not use any other view or function, and view should be created with SET QUOTED_IDENTIFIER ON. And that’s not it; if you are INSERTING into any underlying table being used in any indexed view; you have to SET ARITHABORT ON before inserting in your client applications. (Microsoft KB)

Anyways after making all the required changes; when I evaluated the performance; it was same, 0:40-0:41 </grins>

I ended-up with setting up couple of DTS jobs (yes its not yet ported to SQL 2005) to store the calculations into “CalculatedYearlyReport” table and make that report with this calculated table.

Published Saturday, December 31, 2005 1:06 PM by khurram
Filed under: ,


No Comments

New Comments to this post are disabled