I have written a web application which aggregates and presents data from a SQL Server. The data involved is large, and there is a lot of aggregation involved. Currently, under advice from an 'expert' I have used heavily nested queries including the aggregations, however I have just been reading here about optimising large complex queries by breaking them down into temp (#) tables. This seems to conflict with a lot of literature suggesting that using temp tables is not the best practice.
The web app itself is to be used by quite a few people, so ideally I wouldn't want the temp table, if they need indexing, to conflict with each other.
What is the best way forward here for query optimisation, and best practice for a widely used web app?
Thanks in advance.