The problem with management reports is that they never run well against OLTP databases. That's because the indexing is configured for a different use. This is why some use datawarehouse solutions. For example if your RDBMS supports partioning you set up your partitions accordingly. For example by months or quarters etc. This substantially speeds up these type of queries.
Alternative strategies include creating an index specifically for the query which you can drop afterwards. There is an overhead in creating the index but overall it may be faster.
Temp tables only help in some situations. If your temp table is not signficantly smaller than your original table it may not help much. You need to experiment. But reading large tables without hitting good indexes is always going to be painful.
Alternative strategies include creating an index specifically for the query which you can drop afterwards. There is an overhead in creating the index but overall it may be faster.
Temp tables only help in some situations. If your temp table is not signficantly smaller than your original table it may not help much. You need to experiment. But reading large tables without hitting good indexes is always going to be painful.
