1

Topic: It is a lot of with or temporary tables

Kind day;
There is a theoretical question.
At a forum did not find a similar subject, and on msdn - performance comparisons of approaches.
I write request.
In it there are some units which can be carried out in with before the main request or to collect in temporary tables and then already to use them.
If 100  in units and them it is more than data of the order 3, what will faster work?
With an as (), b as (), c as ()
select
Or
select
into #a
select
into #b
select
into #c
select

2

Re: It is a lot of with or temporary tables

holod-iinna wrote:

If 100  in units and them it is more than data of the order 3

Count demanded storage - if loading from such volumes on the server is explicitly insignificant, it is better to use CTE... Provided that it is not possible to receive a serious gain in speed for the account of an indexing of the temporary table. Well and if the temporary table can be used repeatedly within the limits of session, undoubtedly it is necessary to prefer it...
It is all, of course, in the very first approach.

3

Re: It is a lot of with or temporary tables

Developers often carry out repeating parts of request in cte.
The request turns out short, indicative and legible.
It is good until total request rather small, the server can select the fast plan, few repeated readings of the same data.
At complication of request the probability of a choice of the fast plan decreases, many repeated readings.
Then the variant with temporary tables can strongly benefit.