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

migrated from programmers.stackexchange.com Jan 5 '12 at 12:03

This question came from our site for professionals, academics, and students working within the systems development life cycle.

6

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
  • 3
    What about the effect of temp tables on tempDB? And what about using table variables instead of temp tables? – Oded Jan 5 '12 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 – gbn Jan 5 '12 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. – CatchingMonkey Jan 5 '12 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. – CatchingMonkey Jan 5 '12 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.