Immaculate

This query comes from general reporting perspective rather than focused on Cognos. If there was a need to report from very large view (say having 4000columns) for report having just 10 columns, would it be correct to say that the report processing would be undermined by view parsing for all the columns. This is taking into perspective that view is not a materialized one and works having select statement accessing multiple tables.

Issue crops from report taking long time to process even though has limited number of columns. Would it be correct to say that view is parsing the database for all 4000 columns instead of just 10 columns called from report.

Would be grateful for response on this from Reporting Guru's on this forum.



Re: Team Foundation Server - Reporting & Warehouse Reporting from large view

DougInGeorgia

I think you have the wrong forum. This forum is dedicated to TFS reporting.



Re: Team Foundation Server - Reporting & Warehouse Reporting from large view

Nick Ericson - MSFT

Are you asking this:

If you have a view that does a join on a large number of tables but you only select data from a couple of the tables, does SQL join all the tables before giving you the columns you requested

Then the answer is: If it has to. If it can determine the results without the join then no join is performed.

Say you have TableA with FieldA and TableB with FieldB and you have a view that joins TableA to TableB on FieldA=FieldB. Now you select FieldA from the view.

If SQL doesn't know if there is a 1:1 mapping between FieldA and FieldB, i.e. there is no Foreign Key set up, then SQL needs to do the join. Before doing the join SQL doesn't know if a row in TableA maps to two or more rows in TableB. However say FieldA FK's to FieldB. The following will not perform the join:

select FieldA from TableA left join FieldA = FieldB

because SQL knows that you want TableA.FieldA even if there are no corresponding rows in TableB and, if there is a row in TableB then there is at the most 1.

If you have SQL Server Management Studio you can view the Expected and Actual execution Plans.