5

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.

CM

Improve this question

1 Answer 1

Reset to default
7

The SQLCat team ideas and articles are sometimes pitched at edge cases that most folk will never see given their load or database size.

Saying that, the ideas they give there are common techniques to improve performance (eg change OR to UNION)

Temp tables have some issues around stored procedure compilation etc, but don't confuse these with table variables (the SQLCat articles mentions this). Also, temp tables should be local not global to separate processes don't affect each other

Personally, I use temp tables quite often to break queries down: but not all the time. If I can do it in one SQL statement that runs well enough (for it's frequency of use) then I'll use that.

In your case, I'd identify a few problem queries and see if using temp tables suits these better. If you can't see any problem queries then do nothing.

Improve this answer
4
  • 3
    What about the effect of temp tables on tempDB? And what about using table variables instead of temp tables? Commented Jan 5, 2012 at 10:19
  • 3
    @Oded: intermediate results and aggregations will be spooled and processed in tempdb anyway so not much change, except for table allocation/deallocation. But that only matters when dozens or 100s of times per second (sqlskills.com/BLOGS/PAUL/post/…). Table variables have index and statistics issues: see SO (stackoverflow.com/search?q=temp+table+table+variable) and dba.se for comparision. The SQLCat article says not to use them too Commented Jan 5, 2012 at 10:26
  • Agreed. My experimentation with tables variables significantly slowed down the performance of the queries, hence shifting to nesting. Ill be rewrtting some queries soon and will let you know the results. Commented Jan 5, 2012 at 10:29
  • Testin showed that in some cases nesting was quicker, and in others splitting the queries into temp tables was quicker. Typical! Thanks for the help though. Commented Jan 5, 2012 at 11:28

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.