none
Temp table vs CTE vs derived table

    问题

  • I have one complex query which works fast. It returns 2000 rows.

    For sake of simplicity, lets say:
    SELECT col1, col2 from table1


    If I add order to this select, it works very slow:
    SELECT col1, col2 from table1 ORDER BY col2

    If I add select into #tmp table, than it works fast:

    SELECT col1, col2 INTO #tmp from table1
    SELECT * FROM #tmp ORDER BY col2

    Instead of explicit #tmp table, I tried with derived table or CTE, which both should be similar
    (since derived table and CTE are some kind of temp table in memory).

    But it works slow. Any idea why?

    WITH CTE(SELECT col1, col2 from table1)
    SELECT * FROM CTE ORDER BY col2

    OR

    SELECT Tk.* FROM(SELECT col1, col2 from table1) ORDER BY Tk.col2

    Both works slow, since #tmp works fast.

    br, Simon

    2012年7月3日 12:18

答案

  • CTE has more similarities with derived table, I think.

    Correct. A CTE is a derived table with a name, so you can reuse the name in multiple places in the query. (But note that in SQL Server it is likely to be recomputed every time.)

    Calling SP and make @izdelek as parameter then stores execution plan for each value of @izdelek? I will check.

    No, the plan is stored based from the value of the first execution, which may not be optimal for other values. Or be perfectly applicable.

    So, OPTION(RECOMPILE) is the best choice here. Recompile is fast and the plan is always optimal(we don't have plan in cache but this part is fast anyway).

    Yes, OPTION (RECOMPILE) as it works in SQL 2008 SP2 and later is very powerful. But keep in mind that it is always a tradeoff. If compilation is more expensive than running the query, OPTION (RECOMPILE) query. But for queries where the plan is very dependent on the input and not are executed that frequently, it is certainly a good choice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月6日 9:14

全部回复

  • If you put Clustered Index on col2 the first query probably will run fast.

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    2012年7月3日 12:21
    答复者
  • Instead of explicit #tmp table, I tried with derived table or CTE, which both should be similar
    (since derived table and CTE are some kind of temp table in memory).

    But it works slow. Any idea why?

    I would expect the CTE to be faster with the simple queries you posted.  The actual complex query, which presumably includes joins to other tables and/or a WHERE clause, is another matter.  Make sure stats are up-to-date and check the execution plan for query and index tuning opportunities.  If the optimizer still doesn't generate an adequate plan, the temp table technique is a workaround.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    2012年7月3日 12:31
  • In fact, there is clustered index on that column. Real query is much more complex.
    If I add select into #tmp table and than execute order by on that table it works fast without changing the query or adding indexes (which has it's own cost).

    But I thought that CTE would work similar.

    The execution plan is totally the same in all cases only with CTE it uses two hash joins instead of two merge join (but cost of all this joins is 0% anyway).

    The slowest parts of query, one clustered index scan which takes 58% of cost and one merge join, which takes 32% of cost, are the same for all versions of query
    (CTE, derived or #tmp). Also the estimates and actual values are all the same.

    And that is what bothers me, why the difference in execution time is so great since the execution plans are all the same (only CTE and derived table have the last element in plan SORT - which takes only 1% and should not be problematic).

    br, Simon

    2012年7月3日 12:43
  • Without the full query, there isn't enough information to go on.  Table1 could even be a view. 

    However, you're also doing something the right way:  You are experimenting.  Surprises happen all the time, where no matter how much you know (or how much you THINK you know), queries sometimes run in ways you don't expect.  One of the benefits of knowing SQL well enough that you can do what you're doing (rewrite a query different ways, in a somewhat timely manner), is that you can use that skill to your benefit, to sometimes uncover speed differences. 

    Just playing devil's advocate here too, though:  We've all forgotten to include how long it took us to create our temp tables when comparing execution speeds... so with that said... How long did it take to run the temp table creation?  (so the true comparison will be "create temp-table time + query temp-table time", vs. any of the other speeds.

    2012年7月3日 12:58
  • I have SELECT INTO #tmp and then order by #tmp. The complete execution time of #tmp is 20 times faster then CTE or derived table.
    The execution plan differs in types of join(hash(or nested loop) vs merge), otherwise is totally the same.

    Here are both queries, with execution plans and statistics.
    https://dl.dropbox.com/u/16984080/sqlSample.zip

    What is interesting is, that order by is executed in last step of execution plan (the same as with #tmp, just that here is separated from main query), but it so differs in execution time. Why optimizer chooses merge join in fast query and nested loop(hash) in slow query, while everything else is the same?
    What should I change that query will be fast also with CTE(or derived) table.

    thanks,
    Simon


    • 已编辑 simonxy 2012年7月4日 8:20
    2012年7月4日 8:19
  • I don't have the time to look at the zip file right now. (Well, since I'm vacation, I do have the time, but it's brilliant sunshine outside, and I'm giving priority to outdoors activities.)

    But I like to clear out a misunderstand of your part. A CTE is nowhere close to a temp table. A temp table is physical thing just like any other table. A CTE is a logical thing. SQL Server expands the CTE into the query, and the optimizer works with the expanded query. Which means that if the CTE is referred to multiple times in the query, it is typically computed multiple times.

    Since I did not look at the queries, I can't say what is happening, but it could be outdated statistics, or simply bad estimates. When you add a temp table as an intermediate step, you are shaking up the presumptions big time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月4日 8:39
  • If I use table variable instead of physical temp table, it works also fast. And table variable is the similar as derived table - both are logical tables in memory.

    And table variable is fast while derived table is slow.

    Order by is the very last step in all cases (with separated query in table variable or temp table case) vs last step in execution plan of the query(with CTE or derived table case). So, it is not fully intermediate step, it is just different last step. If it shakes presumptions big time, I would expect totally different execution plan, but it is all the same steps, just with couple of different join types.

    I guess in many cases it is better to put results in intermediate table(#tmp or @tbl) and than do order by there, as adding the indexes(with its overhead) or changing the query (which otherwise works fast).

    Have a nice vacation. If I could i would send you one cold drink :)

    2012年7月4日 9:09
  • So you have SQL 2008 SP2 or SQL 2008 R2 SP1? In such case, what happens if you add OPTION(RECOMPILE) at the end?

    What happens if you make this a stored procedure and make @izdelek a parameter?

    The way the scripts looks now, SQL Server has no knowledge about the value, but makes a standard assumption. The result of this is that estimates are wrong.

    In the fast plan, it estimates that there will be 1.6 million rows from the indexed view, but the actual value is only 400000 rows.

    Incorrect estimates often lead to poor plans.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月4日 21:59
  • I have:

    Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86)   Microsoft Corporation  Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

    If i put the code in SP and add @izdelek as parameter of this procedure, than it works fast.
    With recompile it also works fast.

    In both cases the estimates and actual values for indexed view are 1.6 milion rows and also for slow example the estimates are ok in case of indexed view.
    https://dl.dropbox.com/u/16984080/plan.zip

    I thought if i clear all plans from cache(so, the optimizer must create a new one) and execute the procedure with or without recompile, it should be the same ?
    (since recompile creates new execution plan).

    br, Simon

    2012年7月5日 15:08
  • I would like to add some more point.

    1. CTE : though the cte work same as temp table / variable but the main purpose of CTE is some thing different. it is more effective for recursion purpose.

    2. Temp table Vs variable table : both are used to store the temporary data. but in generally temp variable workes better when no of records are huge and also it better to table variable if we used join or order by clause etc.

    3. table vriable does not support statistic so generally if we use where clause / order by it performance degraded compared to temp table. 

    But is is totally depends on the data and specific situation.

    2012年7月5日 15:43
  • 1. CTE : though the cte work same as temp table

    No, they don't work the same. Logically there are some similarities. Physically, they are worlds apart.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月5日 22:07
  • Thus, we can conclude that the problem was that you did not give the optimizer enough information.

    Yes, you can clear the cache and this will force a recompile. But it is not going to help if the value is hidden in a variable. OPTION(RECOMPILE) helps if you are on SQL 2008 SP2 or later, because SQL Server optimizes the query as if the variable is a constant.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月5日 22:10
  • CTE has more similarities with derived table, I think.

    I din't know that forcing the recompile with clearing the cache works different as forcing the recompile with OPTION(RECOMPILE).

    Calling SP and make @izdelek as parameter then stores execution plan for each value of @izdelek? I will check.

    So, OPTION(RECOMPILE) is the best choice here. Recompile is fast and the plan is always optimal(we don't have plan in cache but this part is fast anyway).

    br, Simon

    2012年7月6日 7:49
  • CTE has more similarities with derived table, I think.

    Correct. A CTE is a derived table with a name, so you can reuse the name in multiple places in the query. (But note that in SQL Server it is likely to be recomputed every time.)

    Calling SP and make @izdelek as parameter then stores execution plan for each value of @izdelek? I will check.

    No, the plan is stored based from the value of the first execution, which may not be optimal for other values. Or be perfectly applicable.

    So, OPTION(RECOMPILE) is the best choice here. Recompile is fast and the plan is always optimal(we don't have plan in cache but this part is fast anyway).

    Yes, OPTION (RECOMPILE) as it works in SQL 2008 SP2 and later is very powerful. But keep in mind that it is always a tradeoff. If compilation is more expensive than running the query, OPTION (RECOMPILE) query. But for queries where the plan is very dependent on the input and not are executed that frequently, it is certainly a good choice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月6日 9:14