| Author |
 Topic  |
|
|
grmnsplx
Starting Member
8 Posts |
 Posted - 04/16/2012 : 16:10:09
|
hello all. I am new to the forum and fairly new to SQL server.
I have had about 3 years using oracle which is probably one reason why my issue has come up.
So my question is regarding the use of WITH statements vs temp tables.
My task was to re-create a somewhat complicated report. I greatly improved the efficiency by being very selective and asertive in the data I was after.
It was pretty straight forward. It looked something like :
<declare stuff>
WITH
effective_dates as (
Select effective_date, ...
from...
)
different_dates as (
select from_date, thru_date, ...
from ...
)
Select ...
from TABLE_X, effective_dates, different dates,
where ...
The above two WITH statements are really light and just generate some dates. TABLE_X is the table with all the data I care about in it. The select joins up the WITHS to TABLE_X
In my past Oracle life I would have thought this was a pretty good approach. And, this was a drastic improvement to what existed before. (5 minutes vs 40 minutes)
Some on my team were not used to with statments and out of curiosity rewrote my code. The logic was identical. The difference was that my team mate replaced the WITHs with temp tables:
<declare stuff>
<drop the temp tables if they exist>
Select effective_date, ...
into #effective_dates
from...
select from_date, thru_date, ...
into #different_dates
from ...
Select ...
from TABLE_X, #effective_dates, #different dates
where ...
To my surprise. This was faster still. Significantly so.
And I'm confused as to why. How could creating a table and then selecting data from it be faster than using the data that has been materialized in the with statement.
Maybe something completely different is happening behind the scenes. I am more of an Oracle person than SQL server.
Any insight would be greatly appreciated.
thanks in advance.
josh |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 04/16/2012 : 16:23:49
|
did you have a chance to compare execution plans of two queries?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 04/16/2012 : 16:27:07
|
Is the whole process; loading the temp tables AND selecting out faster than the CTE version?
I can think of many reasons why this might be the case. As Tara suggested indexes might come into play, but you didn't say anything about that. Another guess, is that it might have to statistics as SQL creates statistics on temp tables, and thus could significantly improve performance.
Would you happen to have the execution plans or some statistical differences between the two queries that you can share?
|
|
|
|
grmnsplx
Starting Member
8 Posts |
Posted - 04/16/2012 : 16:49:47
|
No the temp tables are not indexed.
They are dropped and recreated eachtime the procedure is called.
The tables are not explicitly created (create table... )
They are implicitly created vis SELECT ... INTO #TEMP_TABLE_X
Yes, I'll get some ececution plans and post them.
I would really appreciate your input as this seems rather strange to me. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
|
|
grmnsplx
Starting Member
8 Posts |
Posted - 04/16/2012 : 17:36:49
|
quote:
Originally posted by tkizer
I mean, are there create index statements in the procedure itself for the temp tables?
No indexes anywhere. |
|
|
|
grmnsplx
Starting Member
8 Posts |
|
|
jeffw8713
Aged Yak Warrior
USA
693 Posts |
Posted - 04/17/2012 : 09:28:54
|
I am not sure about Oracle, but in SQL Server CTE's are not materialized - they are incorporated into the SQL and then optimized. Basically, it is the same as using a derived table.
What probably is happening is that the CTE version ends up using different indexes from the temp table version which are not as selective.
SQL Server will build statistics over a temp table - and is able to use those statistics to determine what indexes are available on TABLE_X. In this case, a better execution plan is generated and the query performs better with temp tables.
I will generally check both version - with CTE and with temp table to see which one performs better. With that said, I will use the CTE versions in most cases because that version can be called in SSIS/SSRS with no issues - whereas using temp tables cause problems. As long as I get acceptable performance - that is...
Jeff |
|
|
|
grmnsplx
Starting Member
8 Posts |
Posted - 04/17/2012 : 10:57:00
|
So what does everyone want to see in the execution plan? What exactly should I post?
The execution plans for the first WITH version is easy to retrieve. the second is harder. It fails on the select because it doesn't recognize the temp tables so i have to create those first and then run the explain plain for the select. |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 04/17/2012 : 11:05:25
|
| Generate the Actual Query Plan, then save it as XML (right-click) and post that. |
|
|
|
soumitraghosh
Starting Member
India
1 Posts |
Posted - 07/03/2012 : 04:44:08
|
| I understand that tmp tables will be faster when storing large datasets, and that can explicitly create suitable indexes on them. But in this example the resultsets were small, so this behavior is counter-intuitive. I will like to know why this happens. |
|
|
| |
Topic |
|
With statement VS temp tables
