September 13, 2012 at 4:32 pm
CELKO (9/13/2012)
I was under the impression it's best to avoid temp tables if you can though. Is that not accurate?
It is accurate. We have derived tables and CTEs which the optimizer can use. The temp tables write out to disk which is slower than keeping the data in main storage. But it is a generalization, and ALL generalizations are untrue 🙂
And yet in practice, I have found that breaking things down and using temp tables instead of try to do everything in one query can in fact be more efficient and faster.
Comes down to developing solutions and testing, testing, and testing again.
September 13, 2012 at 4:38 pm
CELKO (9/13/2012)
I was under the impression it's best to avoid temp tables if you can though. Is that not accurate?
It is accurate. We have derived tables and CTEs which the optimizer can use. The temp tables write out to disk which is slower than keeping the data in main storage. But it is a generalization, and ALL generalizations are untrue 🙂
1. Are you sure 100% that "the temp tables write out to disk? Sorry, but that is not true!
SQL Server will not write anything (I mean data) out if it can perform required operation on the data in this table in memory.
2. "keeping the data in main storage"? What exactly do you mean? So, writing data out to disk where the write is performed into database specific file is faster than writing data in case where it needs to write it to tempdb file? Or do you mean that in case of CTE and, unknown beasts to me, "derived tables", data never will be written to the disk? That is also wrong. SQL Server may have not enough memory in its disposal to handle it, and it will write data to disk, funny enough, most likely, it will use tempdb 😀
September 14, 2012 at 8:27 am
Lynn Pettis (9/13/2012)
And yet in practice, I have found that breaking things down and using temp tables instead of try to do everything in one query can in fact be more efficient and faster.Comes down to developing solutions and testing, testing, and testing again.
I agree 100% with that last statement. Programs and databases are all so different, along with the systems they run on. Not to mention there is usually 5 different ways to write a SQL statement to get the same results! :hehe:
September 14, 2012 at 8:34 am
scogeb (9/14/2012)
...there is usually 5 different ways to write a SQL statement to get the same results! :hehe:
The same results but with different performance (most of the time). 😀
September 14, 2012 at 8:40 am
scogeb (9/14/2012)
... (Editted)I agree 100% with that last statement. Programs and databases are all so different, along with the systems they run on. Not to mention there is usually 5 different ways to write a SQL statement to get the same results! :hehe:
FYI, the way you editted the quote makes it seem I said something I didn't.
September 14, 2012 at 9:40 am
CELKO (9/12/2012)
SELECT A.column1, A.column2, MAX(A.column3)
FROM Alpha AS A
WHERE NOT EXISTS
(SELECT *
FROM Beta AS B
WHERE A.column1 = B.column1
AND A.column2 = B.column2)
FROM Beta)
AND A.column4 = 'Yes'
GROUP BY A.column1, A.column2;
Just to stress something in Celko's input that might otherwise remain unnoticed.
@scogeb: Your query would match ("A", "BC") with ("AB","C"). Are you ok with that?
September 14, 2012 at 9:50 am
Lynn Pettis (9/14/2012)
FYI, the way you editted the quote makes it seem I said something I didn't.
Sorry, that was a mess. I fixed it up.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply