Untuk jenis tertentu dari query sql, tambahan tabel angka bisa sangat berguna. Hal ini dapat dibuat sebagai sebuah tabel dengan banyak baris yang anda butuhkan untuk tugas tertentu atau sebagai user defined function yang mengembalikan jumlah baris yang diperlukan dalam setiap query.
Apa adalah cara optimal untuk membuat fungsi tersebut?
Heh... maaf saya'm jadi terlambat menanggapi sebuah posting lama. Dan, ya, aku harus menjawab karena jawaban yang paling populer (pada saat itu, Rekursif CTE menjawab dengan link ke 14 metode yang berbeda) di thread ini, ummm... kinerja ditantang terbaik.
Pertama, artikel dengan 14 solusi yang berbeda baik-baik saja untuk melihat metode yang berbeda untuk menciptakan Angka/Penghitungan tabel pada terbang tapi seperti yang ditunjukkan dalam artikel dan di yang dikutip benang, ada's sangat penting kutipan...
"saran mengenai efisiensi dan kinerja yang seringkali bersifat subyektif. Terlepas dari bagaimana query yang yang digunakan, pelaksanaan fisik menentukan efisiensi dari suatu query. oleh Karena itu, daripada mengandalkan bias pedoman, sangat penting bahwa anda menguji dan menentukan query yang memiliki kinerja baik."
Ironisnya, artikel itu sendiri banyak mengandung pernyataan subjektif dan "bias pedoman" seperti "rekursif CTE dapat menghasilkan daftar nomor cukup efisien" dan "Ini adalah metode yang efisien menggunakan WHILE loop dari newsgroup posting oleh Itzik Ben-Gen" (yang saya'm yakin dia memposting hanya untuk tujuan perbandingan). C'mon orang-orang... Hanya menyebutkan Itzik's nama baik dapat menyebabkan beberapa jorok miskin menjadi benar-benar menggunakan yang mengerikan metode. Penulis harus mempraktekkan apa yang (s)ia berkhotbah dan harus melakukan pengujian kinerja sebelum membuat seperti ridiculously pernyataan yang tidak benar terutama dalam menghadapi setiap scalablility.
Dengan pemikiran benar-benar melakukan beberapa pengujian sebelum membuat klaim subjektif tentang apa yang setiap kode atau apa yang orang "seperti", di sini's beberapa kode yang dapat anda lakukan pengujian anda sendiri dengan. Setup profiler untuk SPID anda're menjalankan tes dan check it out untuk diri sendiri... hanya melakukan "Pencarian'n'Ganti" dari jumlah 1000000 untuk anda "favorit" nomor dan melihat...
--===== Test for 1000000 rows ==================================
GO
--===== Traditional RECURSIVE CTE method
WITH Tally (N) AS
(
SELECT 1 UNION ALL
SELECT 1 + N FROM Tally WHERE N < 1000000
)
SELECT N
INTO #Tally1
FROM Tally
OPTION (MAXRECURSION 0);
GO
--===== Traditional WHILE LOOP method
CREATE TABLE #Tally2 (N INT);
SET NOCOUNT ON;
DECLARE @Index INT;
SET @Index = 1;
WHILE @Index <= 1000000
BEGIN
INSERT #Tally2 (N)
VALUES (@Index);
SET @Index = @Index + 1;
END;
GO
--===== Traditional CROSS JOIN table method
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
INTO #Tally3
FROM Master.sys.All_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2;
GO
--===== Itzik's CROSS JOINED CTE method
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT N
INTO #Tally4
FROM cteTally
WHERE N <= 1000000;
GO
--===== Housekeeping
DROP TABLE #Tally1, #Tally2, #Tally3, #Tally4;
GO
Sementara kita're pada itu, di sini's angka-angka yang saya dapatkan dari SQL Profiler untuk nilai 100, 1000, 10000, 100000, dan 1000000...
SPID TextData Dur(ms) CPU Reads Writes
---- ---------------------------------------- ------- ----- ------- ------
51 --===== Test for 100 rows ============== 8 0 0 0
51 --===== Traditional RECURSIVE CTE method 16 0 868 0
51 --===== Traditional WHILE LOOP method CR 73 16 175 2
51 --===== Traditional CROSS JOIN table met 11 0 80 0
51 --===== Itzik's CROSS JOINED CTE method 6 0 63 0
51 --===== Housekeeping DROP TABLE #Tally 35 31 401 0
51 --===== Test for 1000 rows ============= 0 0 0 0
51 --===== Traditional RECURSIVE CTE method 47 47 8074 0
51 --===== Traditional WHILE LOOP method CR 80 78 1085 0
51 --===== Traditional CROSS JOIN table met 5 0 98 0
51 --===== Itzik's CROSS JOINED CTE method 2 0 83 0
51 --===== Housekeeping DROP TABLE #Tally 6 15 426 0
51 --===== Test for 10000 rows ============ 0 0 0 0
51 --===== Traditional RECURSIVE CTE method 434 344 80230 10
51 --===== Traditional WHILE LOOP method CR 671 563 10240 9
51 --===== Traditional CROSS JOIN table met 25 31 302 15
51 --===== Itzik's CROSS JOINED CTE method 24 0 192 15
51 --===== Housekeeping DROP TABLE #Tally 7 15 531 0
51 --===== Test for 100000 rows =========== 0 0 0 0
51 --===== Traditional RECURSIVE CTE method 4143 3813 800260 154
51 --===== Traditional WHILE LOOP method CR 5820 5547 101380 161
51 --===== Traditional CROSS JOIN table met 160 140 479 211
51 --===== Itzik's CROSS JOINED CTE method 153 141 276 204
51 --===== Housekeeping DROP TABLE #Tally 10 15 761 0
51 --===== Test for 1000000 rows ========== 0 0 0 0
51 --===== Traditional RECURSIVE CTE method 41349 37437 8001048 1601
51 --===== Traditional WHILE LOOP method CR 59138 56141 1012785 1682
51 --===== Traditional CROSS JOIN table met 1224 1219 2429 2101
51 --===== Itzik's CROSS JOINED CTE method 1448 1328 1217 2095
51 --===== Housekeeping DROP TABLE #Tally 8 0 415 0
Seperti yang anda lihat, Rekursif CTE metode adalah yang terburuk kedua hanya untuk Sementara Loop untuk Durasi dan CPU dan memiliki 8 kali memori tekanan dalam bentuk membaca logis dari Loop While. It's RBAR pada steroid dan harus dihindari di semua biaya, untuk setiap baris tunggal perhitungan yang sama seperti While Loop harus dihindari. Ada tempat-tempat di mana rekursi cukup berharga tapi ini BUKAN'T salah satu dari mereka.
Sebagai side bar, Pak Denny adalah benar-benar tempat di... yang benar berukuran permanen Angka atau Penghitungan tabel adalah cara untuk pergi untuk sebagian besar hal-hal. Apa benar berukuran berarti? Nah, kebanyakan orang menggunakan Penghitungan tabel untuk menghasilkan jasa atau untuk melakukan perpecahan pada VARCHAR(8000). Jika anda membuat sebuah 11,000 baris Penghitungan tabel dengan benar clustered index pada "N" anda'll memiliki cukup baris untuk membuat lebih dari 30 tahun senilai tanggal (saya bekerja dengan hipotek sedikit adil sehingga 30 tahun adalah nomor kunci untuk saya) dan tentunya cukup untuk menangani VARCHAR(8000) split. Mengapa "right sizing" begitu penting? Jika Penghitungan tabel banyak digunakan, mudah cocok di cache yang membuatnya blazingly cepat tanpa banyak tekanan pada memori pada semua.
Last but not least, setiap orang tahu bahwa jika anda membuat permanen Penghitungan meja, itu doesn't banyak masalah metode yang anda gunakan untuk membangun karena 1) it's hanya akan dilakukan sekali, dan 2) jika itu's sesuatu seperti 11,000 baris tabel, semua metode yang akan dijalankan, "cukup baik". Jadi mengapa semua indigination di bagian saya tentang metode mana yang harus digunakan???
Jawabannya adalah bahwa beberapa orang miskin/gal yang doesn't tahu apa-apa dan hanya perlu untuk mendapatkan nya atau pekerjaan yang dilakukan mungkin akan melihat sesuatu seperti Rekursif CTE metode dan memutuskan untuk menggunakannya untuk sesuatu yang jauh lebih besar dan jauh lebih sering digunakan dari bangunan permanen Penghitungan tabel I'm mencoba untuk melindungi orang-orang, server mereka kode yang berjalan pada, dan perusahaan yang memiliki data-data pada server-server tersebut. Ya... itu's yang besar. Itu harus untuk orang lain, juga. Mengajarkan cara yang benar untuk melakukan hal-hal yang bukan "cukup baik". Melakukan beberapa pengujian sebelum posting atau menggunakan sesuatu dari posting atau buku... kehidupan yang anda simpan mungkin, pada kenyataannya, menjadi anda sendiri, terutama jika anda berpikir rekursif CTE adalah cara untuk pergi untuk sesuatu seperti ini. ;-)
Terima kasih untuk mendengarkan...
Yang paling optimal fungsi akan menggunakan tabel alih fungsi. Menggunakan fungsi menyebabkan tambahan beban CPU untuk menciptakan nilai-nilai data yang akan dikembalikan, terutama jika nilai yang dikembalikan mencakup rentang yang sangat besar.
Artikel ini memberikan 14 berbagai solusi yang mungkin dengan pembahasan masing-masing. Yang penting adalah bahwa:
saran mengenai efisiensi dan kinerja yang seringkali bersifat subyektif. Terlepas dari bagaimana query yang yang digunakan, pelaksanaan fisik menentukan efisiensi dari suatu query. oleh Karena itu, daripada mengandalkan bias pedoman, sangat penting bahwa anda menguji dan menentukan query yang memiliki kinerja baik.
Saya pribadi menyukai:
WITH Nbrs ( n ) AS (
SELECT 1 UNION ALL
SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )
Pandangan ini super cepat dan berisi semua positif int
nilai-nilai.
CREATE VIEW dbo.Numbers
WITH SCHEMABINDING
AS
WITH Int1(z) AS (SELECT 0 UNION ALL SELECT 0)
, Int2(z) AS (SELECT 0 FROM Int1 a CROSS JOIN Int1 b)
, Int4(z) AS (SELECT 0 FROM Int2 a CROSS JOIN Int2 b)
, Int8(z) AS (SELECT 0 FROM Int4 a CROSS JOIN Int4 b)
, Int16(z) AS (SELECT 0 FROM Int8 a CROSS JOIN Int8 b)
, Int32(z) AS (SELECT TOP 2147483647 0 FROM Int16 a CROSS JOIN Int16 b)
SELECT ROW_NUMBER() OVER (ORDER BY z) AS n
FROM Int32
GO
Menggunakan SQL Server 2016+
untuk menghasilkan nomor tabel, anda bisa menggunakan OPENJSON
:
-- range from 0 to @max - 1
DECLARE @max INT = 40000;
SELECT rn = CAST([key] AS INT)
FROM OPENJSON(CONCAT('[1', REPLICATE(CAST(',1' AS VARCHAR(MAX)),@max-1),']'));
edit: lihat Conrad's komentar di bawah ini.
Jeff Moden's jawaban yang bagus ... tapi saya menemukan di Postgres bahwa Itzik metode gagal kecuali anda menghapus E32-turut.
Sedikit lebih cepat pada postgres (40ms vs 100ms) adalah metode lain yang saya temukan di sini diadaptasi untuk postgres:
WITH
E00 (N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),
E01 (N) AS (SELECT a.N FROM E00 a CROSS JOIN E00 b),
E02 (N) AS (SELECT a.N FROM E01 a CROSS JOIN E01 b ),
E03 (N) AS (SELECT a.N FROM E02 a CROSS JOIN E02 b
LIMIT 11000 -- end record 11,000 good for 30 yrs dates
), -- max is 100,000,000, starts slowing e.g. 1 million 1.5 secs, 2 mil 2.5 secs, 3 mill 4 secs
Tally (N) as (SELECT row_number() OVER (ORDER BY a.N) FROM E03 a)
SELECT N
FROM Tally
Karena saya bergerak dari SQL Server Postgres dunia, mungkin telah terjawab dengan cara yang lebih baik untuk melakukan penghitungan tabel pada platform yang ... INTEGER()? URUTAN()?
Masih banyak kemudian, aku'd ingin berkontribusi sedikit berbeda 'tradisional' CTE (tidak menyentuh dasar tabel untuk mendapatkan volume baris):
--===== Hans CROSS JOINED CTE method
WITH Numbers_CTE (Digit)
AS
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
SELECT HundredThousand.Digit * 100000 + TenThousand.Digit * 10000 + Thousand.Digit * 1000 + Hundred.Digit * 100 + Ten.Digit * 10 + One.Digit AS Number
INTO #Tally5
FROM Numbers_CTE AS One CROSS JOIN Numbers_CTE AS Ten CROSS JOIN Numbers_CTE AS Hundred CROSS JOIN Numbers_CTE AS Thousand CROSS JOIN Numbers_CTE AS TenThousand CROSS JOIN Numbers_CTE AS HundredThousand
Ini CTE melakukan lebih banyak Membaca maka Itzik's CTE tapi kurang Tradisional CTE. Namun, secara konsisten melakukan kurang MENULIS maka pertanyaan lain. Seperti yang anda tahu, Menulis secara konsisten cukup jauh lebih mahal kemudian Membaca.
Durasi sangat bergantung pada jumlah core (MAXDOP) tapi, saya 8core, melakukan secara konsisten lebih cepat (kurang durasi ms) kemudian pertanyaan lain.
Saya menggunakan:
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
pada Windows Server 2012 R2, 32 GB, Xeon X3450 @2.67 Ghz, 4 cores HT diaktifkan.