Working with Reporting DB
Microsoft did a nice job of creating the reporting database in Project Server. The data is so conveniently available that developers can easily create complicate reports. The downside of this is that, for large instance of Project Server, if the developers are not careful about the their queries, the reports may have a time-out problem. I come across one scenario today: It is a typical query built from project view, task view, resource view, and time-phased assignment view with a sub-query. the query structure looks like this: select table1.* ,other tables...... from (select .... from .....) as table1 inner join [other tables]...... where table1.aValue > 0 and other filters.... If I move the filter (table11.aValue > 0) into the sub query, the query will 6 time faster!