0

In SQL Server while writing a query, I noticed that the data in inner query which is a derived table when joined with another table is taking long. The keys joined to the outer table is on the primary key. So I was surprised since the data was about 10,000 records and 15 columns.

But if we store the data from derived table in a temp table and then join the performance was less than 2 seconds. It made me wonder what the reason would be ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lex
  • 19
  • 2
  • 1
    In my opinion, derived table are usually faster than putting the data in to a temp table. If you want help with this question, I encourage you to show your code and also show the execution plan for the query. – George Mastros Aug 04 '15 at 13:02

1 Answers1

1

First, you should edit your question and show your query . . . or at least the structure of the query.

Your issue is probably due to optimization of the query. When you create a temporary table, then the resulting query has accurate statistics about the table during the compilation phase.

When you use a derived table, SQL Server has to guess at the size of the intermediate table and decide on an execution plan before knowing the actual. This would appear to be a situation where the guess is wrong.

If you don't want to use a temporary table, you can probably get the same effect using hints, probably for the join to use either a hash or merge sort algorithm (in my experience, the nested loops algorithm is usually the cause of poor performance).

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon. You hit the Nail ..Looks like the SQL Server Path seem to be different from a static temporary table vs Derived table. – Lex Aug 05 '15 at 22:22