CTE ve Temp Table arasındaki fark nedir?


150

Bir Ortak Tablo İfadesi (CTE) ile geçici tablo arasındaki fark nedir?Birini diğerine ne zaman kullanmalıyım?

CTE

WITH cte (Column1, Column2, Column3)
AS
(
     SELECT Column1, Column2, Column3
     FROM SomeTable
)

SELECT * FROM cte

Sıcaklık tablosu

SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable

SELECT * FROM #tmpTable
+3

Ayrıca bkz. [SQL Server'da geçici tablo ve tablo değişkeni arasındaki fark nedir?] (Http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table -Değişken-in-sQL-sunucu) 15 şub. 122012-02-15 17:02:17

+1

SO ile ilgili: [Hangi performans, CTE veya geçici tablolar?] (Http://stackoverflow.com/a/698634/27535) 16 şub. 122012-02-16 07:52:38

178

Bu oldukça geniş, ancak size olabildiğince genel bir cevap vereceğim.

CTEs ...

  • İndekslenebilir değiller (ancak başvurulan nesnelerdeki mevcut dizinleri kullanabilir
  • Kısıtlamaları olamaz
  • Aslında tek kullanımlık vardırVIEWs
  • Sadece bir sonraki sorgu çalıştırılana kadar devam
  • Özyineli olabilir
  • Özel istatistiklere sahip değilsiniz (alttaki nesnelerdeki istatistiklere dayanın)

#Temp Masaları ...

  • Tempdb içinde var olan gerçek materyalize tablolar
  • Endekslenebilir
  • Kısıtlamaları olabilir
  • Mevcut BAĞLANTI yaşamı için devam
  • Diğer sorgular veya alt işlemler tarafından referans alınabilir
  • Motor tarafından oluşturulan özel istatistikler

Her birinin ne zaman kullanılacağı kadarıyla, çok farklı kullanım durumları vardır.Çok büyük bir sonuç kümesine sahip olacaksanız veya bir kereden fazla başvuru yapmanız gerekiyorsa,#temptablo.Özyinelemeli olması gerekiyorsa, kullanılıp atılabilir olması veya yalnızca mantıksal olarak bir şeyi basitleştirmesi için,CTEtercih edilir.

Ayrıca birCTEmeliasla performans için kullanılamaz.Bir CTE kullanarak işleri neredeyse hiç hızlandırmazsınız, çünkü yine de tek kullanımlık bir görüntü.Onlarla bazı güzel şeyler yapabilirsiniz ama sorguyu hızlandırmak gerçekten onlardan biri değil.


23

DÜZENLE:

Lütfen Martin'in yorumlarına bakınız:

CTE, bellekte bir tablo olarak gerçekleşmez.Bu sadece bir sorgu tanımını içine almanın bir yoludur.OP durumunda, satır içi olacak ve sadece yaptığınız gibi olacakSELECT Column1, Column2, Column3 FROM SomeTable.Çoğu zaman önden gerçekleşmezler, bu yüzden hiç satır döndürmez.WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X, ayrıca yürütme planlarını kontrol edin.Rağmen bazen bir makara almak için planı kesmek mümkündür.Bunun için ipucu isteyen bir bağlantı maddesi var.- Martin Smith 15 Şubat'ta saat 17: 08'de


Orijinal cevap

CTE

Read more on MSDN

Bir CTE bellekte kullanılan tabloyu yaratır, ancak yalnızca onu takip eden belirli sorgu için geçerlidir.Özyinelemeyi kullanırken, bu etkili bir yapı olabilir.

Bir tablo değişkeni kullanmayı da düşünebilirsiniz.Bu kullanılırgibiBir temp tablosu kullanılır ve her bir birleşme için tekrar malzemeye ihtiyaç duyulmadan defalarca kullanılabilir.Ayrıca, şimdi birkaç kayıt tutmanız gerekiyorsa, bir sonraki seçimden sonra bir kaç kayıt daha ekleyin, başka bir işlemden sonra bir kaç kayıt daha ekleyin, sonra sadece kayıtların bir avuçını döndürün, o zaman bu çok kullanışlı İnfazdan sonra düşürülmeye gerek yok.Çoğunlukla sadece sözdizimsel şeker.Ancak, satır sayısını düşük tutarsanız, hiçbir zaman diske aktarılmaz.GörmekWhat's the difference between a temp table and table variable in SQL Server?daha fazla ayrıntı için.

Sıcaklık tablosu

Read more on MSDN - Scroll down about 40% of the way

Temp tablo tam anlamıyla diskte oluşturulan bir tablodur, sadece herkesin silinebileceğini bildiği belirli bir veritabanında.Artık ihtiyaç duyulmadığında bu tabloları imha etmek iyi bir dev'ın sorumluluğudur, ancak bir DBA da onları silebilir.

Geçici tablolar iki çeşittir: Yerel ve küresel.MS SQL Server açısından bir#tableNameyerel atama ve##tableNameglobal için tanım (tanımlayıcı karakteristik olarak tek veya çift # kullanımına dikkat edin).

Tablo değişkenleri veya CTE'nin yerine temp tablolarıyla kelimenin normal anlamındaki meşru tabloları olduğu için indeksleri ve benzerlerini uygulayabildiğinizi unutmayın.


Genelde temp tablolarını daha uzun veya daha büyük sorgular için ve CTE'leri veya tablo değişkenlerini daha önce küçük bir veri kümesine sahip olsaydım ve küçük bir şey için hızlıca biraz kod yazmak istemiştim.Tecrübe ve başkalarının tavsiyesi, ondan az sayıda satırın geldiği CTE'leri kullanmanız gerektiğini gösterir.Çok sayıda numaranız varsa, büyük olasılıkla temp tablosundaki indeksleme özelliğinden yararlanabilirsiniz.

+11

CTE, bellekte bir tablo olarak gerçekleşmez.Bu sadece bir sorgu tanımını içine almanın bir yoludur.OP durumunda, satır içi olacak ve sadece 'SELECT Sütun1, Sütun2, Sütun3'DEN Bazı Tablodan' yapacağınız 15 şub. 122012-02-15 16:55:26

+4

Çoğu zaman önden gerçekleşmezler, bu yüzden hiçbir satır döndürmezler. 'NEW T (X) AS (NEWID()) SELECT * T T1 BİRLEŞTİRMEDEN T T2 ÜZERİNE T1X = T2.X', ayrıca uygulama planlarını da kontrol edin.Gerçi bazen bir makara almak için [planı kesmek] (http://explainextended.com/2009/05/28/generating-xml-in-subqueries/) mümkündür.Bir [connect öğesi] var (https://connect.microsoft.com/SQLServer/feedback/details/218968/provide-a-hint-to-force-intermediate-materialization-of-ctes-or-derived-tables) var Bunun için bir ipucu istemek. 15 şub. 122012-02-15 17:08:33


12

Bir CTE bir sorgu içinde tekrar tekrar çağrılabilir ve her başvurulan zaman değerlendirilir - bu süreç özyinelemeli olabilir.Eğer sadece bir kez belirtilirse, CTE'ler parametrelendirilebilse de, bir alt-sorguya çok benzer şekilde davranır.

Geçici bir masa fiziksel olarak ısrar edilir ve indekslenebilir.Uygulamada sorgu optimizatörü ayrıca, biriktirme işlemlerinde olduğu gibi, sahnelerin arkasındaki ara birleştirme veya alt-sorgulama sonuçlarını da sürdürebilir, dolayısıyla CTE'lerin sonuçlarının hiçbir zaman diske dayanmadığı kesin olarak doğru değildir.

IIRC tablo değişkenleri (diğer taraftan) daima hafıza içi yapılardır.

+4

CTE'ler parametrelendirilebilir mi?Nasıl?Ayrıca, tablo değişkenleri * her zaman * bellekteki yapılar değildir.İlgili bir soruya Martin'in [mükemmel cevabı] (http://dba.stackexchange.com/a/16386/1192) bakın. 30 eyl. 162016-09-30 06:14:36


10

Temp tablo tempdb'de gerçek bir nesnedir, ancak cte, organize yinelemenin sözdizimini bir adımda basitleştirmek için yalnızca karmaşık sorgu etrafında bir sarıcıdır.


12

accepted answer , "CTE hiçbir zaman performans için kullanılmamalıdır" diyor - ancak bu yanlış yönlendirebilir.CTE'lerin temp tablolarına karşı bağlamında, bazı doofus'ların temp tablolarını kullanmanın çok az veya hiç olmadığını düşünmüş olması gerekirken, saklanan bir procs paketinden bir hurda çöpünü çıkarmayı yeni bitirdim.İşlem boyunca meşru bir şekilde tekrar kullanılacak olanlar dışında , CTE'lere çok şey verdim.Tüm ölçümlerle% 20 performans elde ettim.Daha sonra özyinelemeli işlem yapmaya çalışan tüm imleçleri kaldırmaya başladım.Burası en büyük kazancı gördüm.Tepki sürelerini 10 kat azaltarak bitirdim.

CTE'ler ve geçici tablolar çok farklı kullanım durumlarına sahiptir.Sadece, her derde deva olmasa da, CTE'lerin anlaşılmasının ve doğru kullanımının hem kod kalitesi/sürdürülebilirlik hem de hızda bazı gerçekten mükemmel gelişmelere yol açabileceğini vurgulamak istiyorum.Onlarla başa çıktığım için temp tabloları ve imleçleri SQL işlemenin en büyük kötülükleri olarak görüyorum.Hemen hemen her şey için tablo değişkenleri ve CTE'ler ile gayet iyi geçebiliyorum.Kodum daha temiz ve hızlı.

  0

Şimdi, adil olalım - imleçler * büyük * kötülük;temp tabloları en kötü durumdadır * daha az * kötü.:-) Onları sizin gördüğünüz gibi aynı seviyeye getirmek gerçekten de haksızlık. 22 oca. 182018-01-22 20:50:17

  0

@RDFozz haklı, [cehennemin hepimizin bildiği gibi 9 dairesi var] (https://en.wikipedia.org/wiki/Inferno_ (Dante) #Nine_circles_of_Hell).2. sicaklik masalari ve imleçleri 7'ye koyalım.;) 22 oca. 182018-01-22 21:52:08


6

CTE'leri kullanmanın birincil nedeni, aşağıdaki gibi Pencere İşlevlerine erişmektir.row_number()ve çeşitli diğerleri.

Bu, ÇOK ÇOK ÇOK hızlı ve verimli bir şekilde grup başına ilk veya son sırayı almak gibi şeyleri yapabileceğiniz anlamına gelir -more efficiently than other means in most practical cases.

with reallyfastcte as (
select *, 
row_number() over (partition by groupingcolumn order by sortingcolumn) as rownum
from sometable
)
select *
from reallyfastcte
where rownum = 1;

Benzer bir sorguyu, ilişkili bir alt sorgu kullanarak veya bir alt sorgu kullanarak yukarı çalıştırabilirsiniz, ancak CTE hemen hemen tüm senaryolarda daha hızlı olacaktır.

Ek olarak, CTE'ler kodunuzu gerçekten basitleştirmenize yardımcı olabilir.Bu, performans artışlarına yol açabilir, çünkü sorguyu daha iyi anlarsınız ve en iyi duruma getiricinin daha seçici olmasına yardımcı olmak için daha fazla iş mantığı sunar.

Ek olarak, iş mantığınızı anlarsanız ve sorgunun hangi bölümlerinin önce çalıştırılması gerektiğini biliyorsanız, CTE'ler performansı artırabilir - tipik olarak, bir sonraki birleşimlerinde bir dizin kullanabilen sonuç kümelerine yol açan ve ilk ekleme ekleyen en seçici sorgularınızıoption(force order)sorgu ipucu

Son olarak, CTE'ler varsayılan olarak tempdb'yi kullanmazlar, böylece kullanımlarındaki bu tıkanıklığa karşı çekişmeyi azaltırsınız.

Verileri birden çok kez sorgulamanız gerekiyorsa geçici olarak veya alternatif olarakölçmekSorgularınızı ve geçici bir tabloya ekleyerek ve sonra performansınızı artırıcı bir dizin ekleyerek keşfedebilirsiniz.

  0

tüm iyi puanlar ... +1 30 oca. 182018-01-30 13:05:25


5

CTE'lere karşı burada biraz olumsuzluk var gibi görünüyor.

Bir CTE'yi anlıyorum, temelde bu tür bir anlık görüş.SQL hem bildirimsel hem de temelli bir dildir.CTE'ler bir set ilan etmenin harika bir yoludur!Bir CTE'yi endeksleyememek aslında iyi bir şey çünkü gerek yok!Sorgunun okunmasını/yazılmasını kolaylaştırmak gerçekten bir çeşit sözdizimsel şeker.İyi bir düzenleyici, temel tablolardaki dizinleri kullanarak en iyi erişim planını uygular.Bu, temel tablolarda indeks tavsiyesine uyarak CTE sorgunuzu etkin bir şekilde hızlandırabileceğiniz anlamına gelir.

Ayrıca, bir kümesi CTE olarak tanımladığınız için, kümedeki tüm satırların işlenmesi gerektiği anlamına gelmez.Sorguya bağlı olarak en iyi duruma getirici, sorguyu karşılamak için "yeterli" satırı işleyebilir.Belki de ekranınız için yalnızca ilk 20'ye ihtiyacınız vardı.Eğer bir temp tablosu oluşturduysanız, o zaman tüm bu satırları okumanız/yazmanız gerekir!

Buna dayanarak CTE'lerin SQL'in mükemmel bir özelliği olduğunu ve sorgunun okunmasını kolaylaştıracak her yerde kullanılabileceğini söyleyebilirim.Sadece her bir rekoru işlemesi gereken bir toplu iş süreci için geçici bir tablo düşünürdüm.O zaman bile, afaik gerçekten tavsiye edilmez çünkü geçici bir tabloda, veritabanının önbellekleme ve dizinler konusunda size yardımcı olması çok daha zordur.İşleminize özgü bir PK alanı olan kalıcı bir tabloya sahip olmak daha iyi olabilir.

Tecrübelerimin ağırlıklı olarak DB2 ile olduğunu itiraf etmeliyim, bu yüzden CTE'nin her iki üründe de benzer şekilde çalıştığını farz ediyorum.Eğer CTE'ler bir şekilde SQL server'da daha düşükse mutlu bir şekilde düzeltilmiş duracağım.;)