However, a colleague has brought to my attention a query that reliably runs faster with a temp table, and there isn't much I can do about it.
It would appear that SQL Server's optimiser uses the estimated rowcount when setting up/executing a sort. So that if the estimated rowcount is much higher than the actual, the sort becomes slightly more expensive than the same sort with an accurate estimation. This is quite intuitive to me, since a very large sort might use a different algorithm than a very small sort. (In the old mainframe days we always had to tell the sort program how many records it was going to sort.)
In my query without a temp table, SQL Server estimates 26,000 rows will be sorted, while the actual number of rows to be sorted is 36. Of course the sort after the temp table quite accurately estimates 36 rows. So, by inserting the 36 rows into a temp table before sorting, the optimiser will know quite accurately how many records it will sort. To further support this hypothesis, I included a "top 36" in the query without a temp table, and low and behold, it estimated 36 rows in the sort and ran faster (in the same time as the temp table query).
I feel that this performance effect is more of a curiosity than a valid justification to use temp tables in queries with Order By clauses. Also, it is very likely that in a future version of SQL Server the Optimiser team will enhance the product to dynamically determine what sort will be employed during query execution. Then the temp table query will be slower.
The original query was from a customer database, but here is an equivalent query that I have written on my home Perfmon database. If you want to reproduce the effect, the key is to have a query that estimates a very large number of rows in the sort, while the actual number is very small.
The picture shows the execution plan for the two queries. The first query (no temp table) took 53% of the total resources, while the second and third queries (with temp table) only used 47% of the total resources. This was further evidenced from the temp table queries using 10% less cpu than the original query. 328 ms of cpu versus 297 ms for the temp table queries.
DECLARE @date DATETIME
DECLARE @value real
select @date = '2010-01-15 15:50:00', @value = 700
--select * from (
select --top 53
MAX(CounterDateTime) CounterDateTime,
SUM(CounterID) CounterID,
CounterValue CounterValue
FROM CounterDatafacts with (nolock)
WHERE CounterValue between @value and @value+2
and CounterDateTime between @date and DATEADD(hh,1,@date)
Group By CounterValue --) as x
ORDER BY CounterDateTime
--Same query query using temp table
select MAX(CounterDateTime) CounterDateTime,
SUM(CounterID) CounterID,
CounterValue CounterValue
INTO #TEMP
FROM CounterDatafacts with (nolock)
WHERE CounterValue between @value and @value+2
and CounterDateTime between @date and DATEADD(hh,1,@date)
Group By CounterValue
SELECT * FROM #TEMP ORDER BY CounterDateTime
DROP TABLE #Temp
10 comments:
Richard, I spent many years designing and building large mixed workload databases that support 5000+ tps. Temp tables were used often. This is for a few reasons:
1) Often concurrency is more important than individual query performance. Temp tables allow you to isolate the data of high transaction tables to improve concurrency while running complex queries. While that query might itself take longer continually blocking high transaction tables for long periods is much more of an issue.
2) Query predictability can be improved for complex queries by breaking them up and using temp tables. For the reasons you have shown above and more, cost based optimization can’t be perfect and it gets less perfect the complicated the query gets. Also complex plans are much more susceptible to change through minor variation in data distributions resulting which can result in poor performance predictability. A slower but more robust plan is again a better alternative in some situations.
3) Optimization itself through indexing can be simpler when breaking up the queries and using temporary tables. While I am not suggesting it isn’t possible to optimize complicated queries, when you are dealing with a few thousand queries, pre-filtering large datasets into temp tables allows you to more easily see the access patterns across large groups of queries and allows you to identify more “middle of the road” index strategies (not perfect for all queries but useful for many queries) which is important when trying to avoid heavy indexing on highly transaction tables.
I never advise anyone to start with a temp table, I always start by writing the query I need then only break it up if I have concurrency or predictable concerns (or issues post implementation).
Cheers
TB
Tony, I fear that you have been spending too much time with Oracle. SQL Server's optimiser rarely needs temp tables to help performance. Same goes with optimiser hints (directives) that are common place in Oracle.
I accept that on rare occasions you might find the need for a temp table, but I would argue that this should be unusual in a SQL Server application.
Perhaps we can agree that temp tables are OK in the right place, but the right place is quite rare.
Thanks Richard, but I have not worked hands on with Oracle in over 15 years!
Anyway, I think you misread what I wrote as I didn't suggest temp tables commonly helped individual query performance. But they can help concurrency especially when you are performing lots of complex queries on tables that are also highly transactional.
This can be mitigated in other ways such as using the snapshot isolation level, but this is effectivley the same thing (copies of data kept in tempdb).
BUT - I do agree that _most_ SQL Server queries don't have a need, and most of the time they are used incorrectly and hurt performance. My point was simply, sometimes you do need them.
Happy to show you a bunch of clear examples.
Example 1 – We have a billion row table. Every 5 minutes we need to delete approximately 150,000 rows but also insert ~150,000 rows from individual insert transactions. If we deleted 150,000 rows all at once thousands of transactions would be blocked for several minutes while the delete runs. If we used a cursor to delete row by row it would take too long and never keep up with the insert rate. Instead we throw the keys for the rows to delete into a temp table and delete 1000 at a time, repeating this process 150 times. This means the blocking period for inserting transactions is low so concurrency is improved, the delete itself may take longer but the perceived performance of the system is much better.
You could argue we could use a correlated sub query to get the keys instead of using the temp table, in this example we found the impact of the a sub-select running 150 times was much greater (like a factor of 10 or more greater) than if it ran once and retrieving 150 times the volume of data (as the select to find the rows that require deletion have many joins and query predicates) into a temp table.
Or you could just issue a
DELETE TOP (1000) FROM BillionRowTable
WHERE same predicates as insert into temp table
This should be even faster than having to read the data, write it into a temp table, just to read it again.
Well no, as this is the same problem, the high impacting predicate would be run 150 times instead of once as with a temp table. I will get the guys to gather the numbers for comparison.
I agree with Tony on that one. I also use temp table in this particular scenario.
Question to Richard: Do you think the following is actually bad?
If it is, please explain. I use this approach wherever i need to join multiple times with a large transaction table. I would be glad to correct a fundamental mistake.
--getting all matching records from the huge transaction tables (tt1 and tt2) and storing them in temp table
insert into #temptable
select id from
transactiontable1 tt1 join
transactiontable2 tt2
on tt1.id = tt2.id
where condition
--Here I don't have to join with tt1 and tt2. Instead I join with the temp table. I usually create non clustered index on the temp table on id column
select tt3.*
from
transactiontable3 tt3
join #temptable tt
on tt.id = tt3.id
select tt4.*
from
transactiontable4 tt4
join #temptable tt
on tt.id = tt4.id
select tt5.*
from
transactiontable5 tt5
join #temptable tt
on tt.id = tt5.id
I appreciate your comments. However I hold by my remarks that temp tables are overused, particularly by developers with low SQL skills.
There are times when temp tables are more efficient than a single query, but only because the optimiser hasn't found the best path. Unless the optimiser misses the best path, it is best to avoid temp tables. In this way, your query is more likely to take advantage of future upgrades, or to index changes in your schema.
Thanks Richard, I will recheck the indexes.
- arjun
What do you mean by performance? actual speed it takes for the data to be returned? or resources consumed?
And agree in theory, but I'll choose whichever is faster if that's a primary requirement... I was working some some DW ETL extract scripts recently & wrote each as a single query. I thought large datasets, keep it all in memory/allow the sql engine to choose how to do it best.
The driving table was a 50mil row fact table joining a reporting batch dim table. Filtered on a monthid in the dim table which cuts fact table down to 5mil. All predicates indexed (clustered index in dim table). It was taking a bit long so I split off the dim table into a 700 row temp table and used that to join to the fact tbl. x100 fold performance increase! The exec plan shows that the first query was running as a single thread while the second was parallel. I realise the first query is more 'environment friendly' as the exec plan shows 22% resources vs 78% but all the BI here works off cubes and I have the DW to myself for doing these extracts. I'm surprised the engine wasn't choosing a parallel query plan for q1, maybe it's to do with the version (sql2k8sp1)
Post a Comment