Post reply

temp table vs subquery

  • December 1, 2008 at 4:57 am

    #905494

    If you do some performance tests comparing table variables with #temp tables, be aware that relative performance can change depending on the volume of data involved.

    I have seen examples where a table variable consistantly out-performed the use of a #temp table when dealing with a few hundred rows, but was consistantly slower when scaled up to production volumes of a few million rows.

    The answer is mainly down to how table variables can be indexed compared to #temp tables. Table variables are a light-weight tool for handling light-weight data. #temp tables are a full-feature tool that can handle any amount of data, but may not be the fastest tool for light-weight data. CTEs have their own strong points, outside the scope of this post. The important thing is to use the right tool for the job, and avoid a one-size-fits-all approach.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • December 2, 2008 at 1:04 am

    #906061

    GilaMonster (12/1/2008)

    Depends what you're doing.

    Usually derived tables are less intensive because there's no interim storage and no need to insert stuff somewhere and then query it out. If you're doing a query that's a page or 2 long, it probably needs to be broken up, but for simpler stuff, temp tables are going to add complexity and time.

    Thanks Gail.

    "Keep Trying"

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply