HomeSQL ServerSometimes temp tables can be your best friend

Comments

Sometimes temp tables can be your best friend — 3 Comments

  1. I’ve had similar in the past, which I blogged about here: http://thelonedba.wordpress.com/2011/05/10/t-sql-tuesday-018-ctes-theyre-not-always-good-for-you/

    This time, it wasn’t a subquery such as you had, but a rather long-winded combination of CTEs and VIEWs, and I found that creating temporary tables from those caused the query behind a report to go from 2 hour run-time to 40 seconds.

    Remember, kids, CTEs & VIEWs aren’t always good for you!

  2. I’ve had a recent issue where the query had to process 20 million rows, read XML data and extract a particular tag’s value, which was later used to join to another table and using temporary tables was definitely the most optimal solution.
    thanks for sharing this though.

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags allowed in your comment: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>