I routinely see queries indicating millions of rows of data are being read when refreshing even if the final result for the data model may be less than 1,000 records. I’m wondering if I’m causing a heavier load when actually trying to lighten it.
As an example, say I need to reference data from table A for many queries in the same report. Is it better to make the ODBC connection to table A only once, select all of the columns which may be needed by various queries, filter where possible to include only the minimum number of rows needed, and then use that as the source for other queries rather than repeating the same process for each query individually?
In an attempt to explain it better, which is better below? Or is there a better answer I don’t mention?
Table A is used by queries numbered, 1,2,3,4,5.
Should I navigate via ODBC to Table A only once and then reference that connection in the 5 queries, or should each of the 5 queries navigate directly to Table A in my Oracle database.
We do not have a data warehouse but make connections to multiple Oracle tables via ODBC. This report references more than 30 separate Oracle tables, and each table may be used by anywhere from 1 to 10 queries which may in turn be merged in various combinations to create the data model used for the report. I wind up with a total of 24 fact or dimension tables used by one report when trying to reduce it to the minimum needed.
I’m basically trying to follow the mindset of referencing data tables as needed rather than duplicating them.
