I'm pretty sure there's no optimization occurring in the SQL that CUIC executes. It should be executing exactly the SQL statement that you see when you click the "SQL" button in a report.
Having fewer Report Definitions is much, much simpler from an administrative standpoint. If you make multiple for the same data, you run into all sorts of hassles if you later find out you built a field wrong, or weren't specific enough in a JOIN statement, or whatever. With the maximum output for reports set at 8000 rows for historical and 3000 rows for real-time, I've found that I rarely ever have a useful report that takes very long to run. What I would recommend, if you're concerned about server resources, is to do "SELECT TOP 8001" in your historical queries, and "SELECT TOP 3001" in your real-time queries... this keeps most reports from getting out of control, and on any filters that provide tons of data, the user will still see the "More than 8000 rows, please narrow your filter criteria" message.
One of the benefits of having Report Definitions that bring in all/most fields from a set of tables is that you can build Reports with multiple useful Views on them, reducing the number of entries in your Reports folder structure. I find that my users just get confused if there are too many Reports for them to choose from. It's easier as a user to just pick "Call Type Historical Daily", select the days and Call Types to report on, and flip through the different Views to get the data you're looking for.
I only create new Definitions if I need significantly different output... different "GROUP BY", summarizations, bringing in completely different tables, or providing different Filters.
-Jameson
