Katakanlah Anda memiliki tabel dengan sekitar 5 juta catatan dan kolom nvarchar (maks) yang diisi dengan data teks besar. Anda ingin mengatur kolom ini ke NULL jika SomeOtherColumn 1 dengan cara tercepat. Kekuatan kasar UPDATE tidak berjalan dengan baik di sini karena akan menciptakan transaksi implisit yang besar dan berlangsung selamanya. Melakukan update dalam batch kecil dari 50K record pada satu waktu bekerja namun masih membutuhkan waktu 47 jam untuk menyelesaikan server core64GB yang gemuk. Apakah ada cara untuk melakukan pembaruan ini lebih cepat Apakah ada petunjuk ajaib yang mengisyaratkan pilihan tabel yang mengorbankan sesuatu yang lain (seperti concurrency) dengan imbalan kecepatan CATATAN: Membuat kolom temp atau kolom temp bukan pilihan karena kolom nvarchar (max) ini melibatkan banyak Data dan begitu banyak menghabiskan banyak ruang PS: Ya, SomeOtherColumn sudah terindeks. Saya setuju, kami secara teratur melakukan update seperti ini pada tabel dengan catatan 50 juta atau bahkan 500 juta dan itu terjadi dalam hitungan detik. Saya akan menebak bahwa rencana kueri yang dipilih tidak terlalu optimal dan membutuhkan banyak waktu. Saya telah mengalami hal ini terjadi pada saya ketika ada batasan kunci asing di meja lain pada kolom yang tidak terindeks. Setelah melihat rencana kueri, kami menyadari bahwa itu harus memindai tabel lainnya untuk setiap penghapusan yang merupakan penyebabnya. Itu memiliki 23 Juta baris, mengindeks tabel lainnya yang dibawa hapus hingga kurang dari 5 detik. Ndash Cobusve 7 Jun 10 at 10:46 Dari semua hal yang saya dapat melihatnya tidak terlihat seperti masalah Anda terkait dengan indeks. Kuncinya nampaknya adalah fakta bahwa bidang nvarchar (max) Anda berisi banyak data. Pikirkan tentang apa yang harus dilakukan SQL untuk melakukan pembaruan ini. Karena kolom yang Anda update mungkin lebih dari 8000 karakter, itu disimpan di luar halaman, yang menyiratkan upaya tambahan dalam membaca kolom ini bila bukan NULL. Ketika Anda menjalankan batch 50000 update SQL harus menempatkan ini dalam transaksi implisit agar memungkinkan untuk melakukan rollback jika terjadi masalah. Agar bisa memutar ulang, ia harus menyimpan nilai asli kolom pada log transaksi. Dengan asumsi (untuk kesederhanaan) masing-masing kolom berisi rata-rata 10.000 byte data, berarti 50.000 baris akan berisi sekitar 500MB data, yang harus disimpan sementara (dalam mode pemulihan sederhana) atau permanen (dalam mode pemulihan penuh). Tidak ada cara untuk menonaktifkan log karena akan membahayakan integritas database. Saya menjalankan tes cepat pada komputer saya yang lamban, dan menjalankan batch bahkan 10.000 menjadi sangat lambat, namun membawa ukuran hingga 1000 baris, yang menyiratkan ukuran log sementara sekitar 10MB, bekerja dengan baik. Saya mengisi sebuah tabel dengan 350.000 baris dan menandai 50.000 di antaranya untuk update. Ini selesai dalam waktu sekitar 4 menit, dan karena skala secara linier Anda harus dapat memperbarui keseluruhan 5Million baris Anda di desktop lambat anjing saya sekitar 6 jam di desktop 2GB prosesor saya, jadi saya akan mengharapkan sesuatu yang lebih baik pada server gemuk Anda yang didukung. Oleh SAN atau sesuatu. Anda mungkin ingin menjalankan laporan mutakhir Anda sebagai pilih, hanya memilih primary key dan kolom nvarchar yang besar, dan pastikan ini berjalan secepat yang Anda harapkan. Tentu saja kemacetan mungkin pengguna lain mengunci sesuatu atau pertengkaran pada penyimpanan atau memori Anda di server, tapi karena Anda tidak menyebutkan pengguna lain, saya akan menganggap Anda memiliki DB dalam mode pengguna tunggal untuk ini. Sebagai pengoptimalan, Anda harus memastikan bahwa log transaksi berada pada kelompok disket fisik yang berbeda daripada data yang meminimalkan waktu pencarian. Ini sangat membantu saya. Saya pergi dari 2 jam sampai 20 menit dengan ini. Menurut pengalaman saya, bekerja di MSSQL 2005, bergerak setiap hari (secara otomatis) 4 Juta 46 byte (tidak ada nvarchar (maks) sekalipun) dari satu tabel dalam database ke tabel lain dalam basis data yang berbeda membutuhkan waktu sekitar 20 menit dalam QuadCore 8GB , Server 2Ghz dan itu tidak merugikan kinerja aplikasi. Dengan menggerakkan Maksud saya INSERT INTO SELECT lalu DELETE. Penggunaan CPU tidak pernah berjalan lebih dari 30, bahkan saat meja yang dihapus memiliki catatan 28M dan terus membuat sekitar 4K memasukkan per menit namun tidak ada pembaruan. Nah, itu kasus saya, mungkin berbeda tergantung pada beban server Anda. Menentukan bahwa pernyataan (pembaruan Anda) dapat membaca baris yang telah dimodifikasi oleh transaksi lain namun belum dilakukan. Dalam kasus saya, catatannya hanya bisa dibaca. Aku tidak tahu apa arti rg-tsql tapi di sini kamu akan menemukan info tentang tingkat isolasi transaksi di MSSQL. Selalu hati-hati dan pastikan Anda memahami implikasi dari membaca transaksi yang tidak disunat. Ya, proses Anda tidak perlu menunggu transaksi terbuka dilakukan sebelum menghapus item, tapi tentu saja jika transaksi tidak dilakukan setelah semua ini berarti Anda menghapus baris secara tidak benar. Ndash Cobusve 7 Jun 10 10:43 Jika Anda menjalankan lingkungan produksi dengan tidak cukup ruang untuk menduplikat semua tabel Anda, saya yakin Anda mencari masalah cepat atau lambat. Jika Anda memberikan beberapa info tentang jumlah baris dengan SomeOtherColumn1, mungkin kita bisa memikirkan cara lain, tapi saya sarankan: 0) Backup tabel Anda 1) Indeks kolom bendera 2) Tetapkan opsi tabel ke tidak ada log tranctions. Jika posible 3) menulis prosedur yang tersimpan untuk menjalankan update dijawab 2 Jun 10 at 3:17 BTW. Apakah Anda akan perlu menjalankan prosedur ini lebih dari satu kali dalam kehidupan ndash Dr. belisarius 2 Jun 10 at 3:24 Bagaimana Anda menetapkan pilihan tabel untuk log kuotasi tanpa kuota pengguna? , IncI perlu menghitung jumlah penggulir di kisaran tanggal. Sebagai ilustrasi, gunakan database contoh AdventureWorks. Sintaks hipotetis berikut akan melakukan persis apa yang saya butuhkan: Sayangnya, frame RANGE frame sejauh ini tidak mengizinkan interval di SQL Server. Saya tahu saya bisa menulis sebuah solusi menggunakan subkueri dan agregat biasa (non-window): Diberikan indeks berikut: Rencana eksekusi adalah: Meskipun tidak mengerikan, sepertinya memungkinkan untuk mengekspresikan query ini hanya dengan menggunakan jendela agregat. Dan fungsi analitik yang didukung di SQL Server 2012, 2014, atau 2016 (sejauh ini). Untuk kejelasan, saya mencari solusi yang melakukan single pass atas data. Dalam T-SQL ini kemungkinan berarti bahwa klausa OVER akan melakukan pekerjaan, dan rencana eksekusi akan menampilkan Window Spools dan Window Aggregates. Semua elemen bahasa yang menggunakan klausa OVER adalah fair game. Solusi SQLCLR dapat diterima, asalkan dijamin menghasilkan hasil yang benar. Untuk solusi T-SQL, semakin sedikit galur Hash, Sort, dan Window SpoolsAggregates dalam rencana eksekusi, semakin baik. Jangan ragu untuk menambahkan indeks, namun struktur terpisah tidak diijinkan (jadi tidak ada tabel pra-perhitungan yang tetap sinkron dengan pemicu, misalnya). Tabel referensi diijinkan (tabel angka, tanggal dll.) Idealnya, solusi akan menghasilkan hasil yang persis sama dengan urutan yang sama dengan versi subkueri di atas, namun ada yang bisa dibilang benar juga bisa diterima. Kinerja selalu menjadi pertimbangan, jadi solusi setidaknya harus cukup efisien. Ruang obrolan khusus: Saya telah membuat ruang obrolan umum untuk diskusi yang terkait dengan pertanyaan ini dan jawabannya. Setiap pengguna dengan setidaknya 20 poin reputasi dapat mengambil bagian secara langsung. Silakan ping saya di komentar di bawah ini jika Anda memiliki kurang dari 20 rep dan ingin ambil bagian. Tanya 7 September pukul 20:13 Pertanyaan yang bagus, Paul saya menggunakan beberapa pendekatan berbeda, satu di T-SQL dan satu di CLR. Pendekatan T-SQL dapat diringkas sebagai langkah-langkah berikut: Ambil produk silang dari produkdate Bergabunglah dalam data penjualan yang teramati Agregat data ke tingkat productdate Hitung jumlah bergulir selama 45 hari terakhir berdasarkan data agregat ini (yang berisi Hari hilang diisi) Saring hasilnya hanya pada pasangan productdate yang memiliki satu atau lebih penjualan Menggunakan SET STATISTIK IO ON. Pendekatan ini melaporkan Tabel TransactionHistory. Scan count 1, logical reads 484. yang mengkonfirmasikan single pass diatas meja. Sebagai referensi, laporan permintaan pencarian loop asli Tabel TransactionHistory. Scan count 113444, dibaca logis 438366. Seperti dilansir SET STATISTIK WAKTU ON. Waktu CPU adalah 514ms. Ini sebanding dengan 2231ms untuk kueri asli. Ringkasan CLR dapat diringkas sebagai langkah-langkah berikut: Membaca data ke dalam memori, dipesan berdasarkan produk dan tanggal Saat memproses setiap transaksi, tambahkan total biaya yang harus dikeluarkan. Setiap kali transaksi adalah produk yang berbeda dari transaksi sebelumnya, setel ulang total yang ada menjadi 0. Pertahankan pointer ke transaksi pertama yang memiliki transaksi (produk, tanggal) yang sama dengan transaksi saat ini. Kapan pun transaksi terakhir dengan itu (produk, tanggal) ditemukan, hitung jumlah penggalangan untuk transaksi itu dan menerapkannya ke semua transaksi dengan produk (tanggal, tanggal) yang sama. Kembalikan semua hasilnya ke pengguna Menggunakan STATISTIK SET IO ON. Pendekatan ini melaporkan bahwa tidak ada IO logis yang pernah terjadi Wow, solusi yang tepat (Sebenarnya, sepertinya SET STATISTIK IO tidak melaporkan IO yang terjadi dalam CLR. Tapi dari kodenya, mudah untuk melihat bahwa tepat satu pemindaian tabel dibuat Dan mengambil data sesuai dengan indeks yang disarankan Paul Seperti yang dilaporkan oleh SET STATISTIK WAKTU ON, waktu CPU sekarang 187ms Jadi ini cukup merupakan perbaikan dari pendekatan T-SQL Sayangnya, waktu berlalu secara keseluruhan dari kedua pendekatan adalah Sangat mirip sekitar setengah detik masing-masing. Namun, pendekatan berbasis CLR memang harus mengeluarkan 113K baris ke konsol (hanya 52K untuk pendekatan T-SQL yang dikelompokkan menurut productdate), jadi mengapa saya berfokus pada waktu CPU Keuntungan besar lainnya dari pendekatan ini adalah bahwa ia menghasilkan hasil yang sama persis dengan pendekatan loopseek asli, termasuk sebuah baris untuk setiap transaksi bahkan dalam kasus di mana produk dijual berkali-kali pada hari yang sama. (Di AdventureWorks, secara khusus saya membandingkan barisan - by-row re Sults dan mengkonfirmasi bahwa mereka mengikat dengan permintaan asli Paul.) Kerugian dari pendekatan ini, setidaknya dalam bentuknya saat ini, adalah membaca semua data di memori. Namun, algoritma yang telah dirancang hanya sangat membutuhkan bingkai jendela saat ini dalam memori pada waktu tertentu dan dapat diperbarui untuk digunakan pada kumpulan data yang melebihi memori. Paul telah menggambarkan hal ini dalam jawabannya dengan menghasilkan sebuah implementasi dari algoritma ini yang hanya menyimpan jendela geser di memori. Ini datang dengan mengorbankan pemberian izin yang lebih tinggi ke majelis CLR, namun pasti akan bermanfaat dalam menskalakan solusi ini sampai pada set data yang sewenang-wenang. T-SQL - satu pemindaian, dikelompokkan berdasarkan tanggal Rencana eksekusi Dari rencana pelaksanaan, kita melihat bahwa indeks asli yang diajukan oleh Paul cukup untuk memungkinkan kita melakukan pemindaian tunggal terhadap Production. TransactionHistory. Menggunakan gabungan bergabung untuk menggabungkan sejarah transaksi dengan setiap kombinasi productdate yang mungkin. Ada beberapa asumsi penting yang dipanggang dalam pendekatan ini. Saya kira akan tergantung pada Paul untuk memutuskan apakah bisa diterima :) Saya menggunakan tabel Production. Product. Tabel ini tersedia secara bebas di AdventureWorks2012 dan hubungannya ditegakkan dengan kunci asing dari Production. TransactionHistory. Jadi saya menafsirkan ini sebagai permainan yang adil. Pendekatan ini bergantung pada fakta bahwa transaksi tidak memiliki komponen waktu di AdventureWorks2012 jika memang benar, menghasilkan kombinasi productdate lengkap tidak akan mungkin lagi tanpa terlebih dahulu mengambil alih riwayat transaksi. Saya memproduksi rowset yang hanya berisi satu baris per pasangan productdate. Saya pikir ini bisa dibilang benar dan dalam banyak kasus hasil yang lebih diinginkan untuk kembali. Untuk setiap productdate, saya telah menambahkan kolom NumOrders untuk menunjukkan berapa banyak penjualan yang terjadi. Lihat tangkapan layar berikut untuk perbandingan hasil kueri asli vs. kueri yang diajukan dalam kasus di mana produk dijual berkali-kali pada tanggal yang sama (misalnya 319 2007-09-05 00: 00: 00.000) CLR - satu pindaian , Kumpulan hasil ungrouped lengkap Fungsi utama bodi Tidak ada satu ton untuk melihat di sini fungsi utama dari fungsi tersebut menyatakan masukan (yang harus sesuai dengan fungsi SQL yang sesuai), membuat koneksi SQL, dan membuka SQLReader. Saya telah memisahkan logika utama jadi lebih mudah untuk fokus pada: Logika berikut dapat ditulis secara inline, namun sedikit lebih mudah dibaca saat mereka terbagi menjadi metode mereka sendiri. Mengikat semuanya di SQL Segalanya sampai saat ini telah berada di C, jadi mari kita lihat SQL yang sebenarnya terlibat. (Sebagai alternatif, Anda dapat menggunakan skrip penyebaran ini untuk membuat perakitan secara langsung dari bit perakitan saya daripada mengkompilasi sendiri.) Pendekatan CLR memberi fleksibilitas lebih banyak untuk mengoptimalkan algoritme, dan mungkin bisa disetel lebih jauh oleh pakar. Di C. Namun, ada juga downsides strategi CLR. Beberapa hal yang perlu diingat: Pendekatan CLR ini menyimpan salinan kumpulan data di memori. Hal ini dimungkinkan untuk menggunakan pendekatan streaming, tapi saya mengalami kesulitan awal dan menemukan bahwa ada masalah Connect yang beredar yang mengeluh bahwa perubahan di SQL 2008 membuat lebih sulit untuk menggunakan pendekatan jenis ini. Ini masih mungkin (seperti yang ditunjukkan Paul), namun memerlukan tingkat izin yang lebih tinggi dengan menetapkan database sebagai TRUSTWORTHY dan memberikan EXTERNALACCESS ke majelis CLR. Jadi ada beberapa kerumitan dan potensi implikasi keamanan, namun hasilnya adalah pendekatan streaming yang dapat menghasilkan skala lebih besar dari kumpulan data yang jauh lebih besar daripada yang ada di AdventureWorks. CLR mungkin kurang dapat diakses oleh beberapa DBA, sehingga membuat fungsi semacam itu lebih dari kotak hitam yang tidak transparan, tidak mudah dimodifikasi, tidak mudah digunakan, dan mungkin tidak mudah didebit. Ini adalah kerugian yang cukup besar bila dibandingkan dengan pendekatan T-SQL. Bonus: T-SQL 2 - Id pendekatan praktis benar-benar digunakan Setelah mencoba memikirkan masalah secara kreatif untuk sementara, saya pikir Id juga mengemukakan cara praktis dan sederhana yang mungkin akan saya pilih untuk mengatasi masalah ini jika muncul di Pekerjaan sehari-hari saya Ini menggunakan fungsionalitas jendela SQL 2012, namun tidak dengan jenis cara terobosan yang diharapkan oleh pertanyaan ini: Ini benar-benar menghasilkan rencana permintaan keseluruhan yang cukup sederhana, bahkan ketika melihat kedua dari dua rencana kueri yang relevan tersebut bersama-sama: Beberapa Alasan saya menyukai pendekatan ini: Ini menghasilkan hasil lengkap yang diminta dalam pernyataan masalah (berlawanan dengan sebagian besar solusi T-SQL lainnya, yang mengembalikan versi hasil berkelompok). Mudah untuk dijelaskan, dimengerti, dan debug saya tidak akan kembali setahun kemudian dan bertanya-tanya bagaimana saya bisa melakukan perubahan kecil tanpa merusak kebenaran atau kinerjanya. Ini berjalan sekitar 900ms pada kumpulan data yang disediakan, daripada 2700ms dari Pencarian loop asli Jika data jauh lebih padat (lebih banyak transaksi per hari), kompleksitas komputasi tidak tumbuh secara kuadratik dengan jumlah transaksi di jendela geser (seperti pada query asli) Saya rasa ini membahas sebagian dari Paul Kekhawatiran tentang ingin menghindari banyak pemindaian Ini berakibat pada dasarnya tidak ada tempdb IO dalam pembaruan terbaru SQL 2012 karena fungsionalitas penulisan tempdb yang baru malas Untuk kumpulan data yang sangat besar, sepele untuk membagi pekerjaan menjadi batch terpisah untuk setiap produk jika tekanan memori Untuk menjadi perhatian Beberapa peringatan potensial: Meskipun secara teknis memindai Production. TransactionHistory sekali saja, pendekatan pemindaiannya tidak benar-benar satu sama lain karena tabel temponya dengan ukuran yang sama dan perlu dilakukan. Lakukan logika tambahan IO di meja itu juga. Namun, saya tidak melihat ini terlalu berbeda dari tabel kerja sehingga kita memiliki kontrol manual lebih banyak karena kita telah menentukan strukturnya yang tepat Bergantung pada lingkungan Anda, penggunaan tempdb dapat dipandang sebagai sesuatu yang positif (misalnya pada rangkaian terpisah dari SSD drive) atau negatif (konkurensi tinggi di server, banyak pertengkaran tempdb sudah) dijawab 8 Sep 15 15:41. Ini adalah jawaban yang panjang, jadi saya memutuskan untuk menambahkan ringkasan di sini. Awalnya saya menyajikan solusi yang menghasilkan hasil yang persis sama dengan urutan yang sama seperti pada pertanyaan. Ini memindai tabel utama 3 kali: untuk mendapatkan daftar ProductID dengan rentang tanggal untuk masing-masing Produk, untuk meringkas biaya setiap hari (karena ada beberapa transaksi dengan tanggal yang sama), untuk mengikuti hasil dengan baris asli. Selanjutnya saya membandingkan dua pendekatan yang menyederhanakan tugas dan menghindari pemindaian terakhir dari tabel utama. Hasilnya adalah ringkasan harian, yaitu jika beberapa transaksi pada Produk memiliki tanggal yang sama, mereka digulirkan menjadi satu baris. Pendekatan saya dari langkah sebelumnya memindai tabel dua kali. Pendekatan oleh Geoff Patterson memindai tabel sekali, karena dia menggunakan pengetahuan eksternal tentang rentang tanggal dan daftar Produk. Akhirnya saya menyajikan satu solusi pass yang kembali menghasilkan ringkasan harian, tapi tidak memerlukan pengetahuan eksternal tentang rentang tanggal atau daftar ProductID. Saya akan menggunakan database AdventureWorks2014 dan SQL Server Express 2014. Perubahan pada database asli: Jenis Produksi yang diubah. TransactionHistory. TransactionDate dari datetime sampai saat ini. Komponen waktu tetap nol. Ditambahkan tabel kalender dbo. Calendar Ditambahkan indeks untuk Production. TransactionHistory MSDN artikel tentang klausa OVER memiliki link ke posting blog yang sangat baik tentang fungsi jendela oleh Itzik Ben-Gan. Di pos itu dia menjelaskan bagaimana OVER bekerja, perbedaan antara pilihan ROWS dan RANGE dan menyebutkan masalah ini untuk menghitung jumlah penggulir dalam rentang tanggal. Dia menyebutkan bahwa versi SQL Server saat ini tidak menerapkan RANGE secara penuh dan tidak menerapkan tipe data interval temporal. Penjelasannya tentang perbedaan antara ROWS dan RANGE memberi saya sebuah ide. Tanggal tanpa kesenjangan dan duplikat Jika tabel TransactionHistory berisi tanggal tanpa celah dan tanpa duplikat, maka kueri berikut akan menghasilkan hasil yang benar: Memang, sebuah jendela dengan 45 baris akan mencakup persis 45 hari. Tanggal dengan kesenjangan tanpa duplikat Sayangnya, data kami memiliki kesenjangan dalam kurun waktu. Untuk mengatasi masalah ini, kita dapat menggunakan tabel Kalender untuk menghasilkan kumpulan tanggal tanpa celah, lalu KIRI JOIN data asli ke himpunan ini dan gunakan kueri yang sama dengan ROWS BETWEEN 45 PRECEDING AND CURRENT ROW. Ini akan menghasilkan hasil yang benar hanya jika tanggal tidak berulang (dalam ProductID yang sama). Tanggal dengan kesenjangan dengan duplikat Sayangnya, data kami memiliki kedua kesenjangan pada tanggal dan tanggal dapat diulang dalam ProductID yang sama. Untuk mengatasi masalah ini, kami dapat mengelompokkan data asli oleh ProductID, TransactionDate untuk menghasilkan serangkaian tanggal tanpa duplikat. Kemudian gunakan tabel Kalender untuk menghasilkan serangkaian tanggal tanpa celah. Kemudian kita bisa menggunakan query dengan ROWS BETWEEN 45 PRECEDING AND CURRENT ROW untuk menghitung rolling SUM. Ini akan menghasilkan hasil yang benar. Lihat komentar dalam query di bawah ini. Saya mengonfirmasi bahwa kueri ini menghasilkan hasil yang sama dengan pendekatan dari pertanyaan yang menggunakan subkueri. Permintaan pertama menggunakan subkueri, kedua - pendekatan ini. Anda dapat melihat bahwa durasi dan jumlah pembacaannya jauh lebih sedikit dalam pendekatan ini. Mayoritas estimasi biaya dalam pendekatan ini adalah ORDER BY akhir. Lihat di bawah. Pendekatan subquery memiliki rencana sederhana dengan loop bersarang dan kompleksitas O (nn). Rencanakan pendekatan ini memindai TransactionHistory beberapa kali, tapi tidak ada loop. Karena Anda dapat melihat lebih dari 70 perkiraan biaya adalah Sortir untuk ORDER BY akhir. Hasil teratas - subkueri. Bawah - OVER Menghindari pemindaian ekstra Indeks terakhir Scan, Merge Join dan Sortir dalam rencana di atas disebabkan oleh INNER JOIN terakhir dengan tabel asli untuk membuat hasil akhir sama persis dengan pendekatan yang lambat dengan subkueri. Jumlah baris yang dikembalikan sama seperti tabel TransactionHistory. Ada baris dalam TransactionHistory ketika beberapa transaksi terjadi pada hari yang sama untuk produk yang sama. Jika tidak apa-apa untuk hanya menampilkan ringkasan harian hasilnya, maka JOIN terakhir ini dapat dihapus dan kueri menjadi sedikit lebih sederhana dan sedikit lebih cepat. Indeks terakhir Scan, Gabung Bergabung dan Urutkan dari rencana sebelumnya diganti dengan Filter, yang menghapus baris yang ditambahkan oleh Kalender. Meski begitu, TransactionHistory dipindai dua kali. Satu pemindaian ekstra diperlukan untuk mendapatkan rentang tanggal untuk setiap produk. Saya tertarik untuk melihat bagaimana perbandingannya dengan pendekatan lain, di mana kita menggunakan pengetahuan eksternal tentang rentang tanggal di TransactionHistory global. Ditambah meja ekstra Produk yang memiliki semua ProductIDs untuk menghindari pemindaian ekstra itu. Saya menghapus perhitungan jumlah transaksi per hari dari query ini agar perbandingan valid. Hal ini dapat ditambahkan dalam kedua pertanyaan, tapi Id ingin tetap sederhana untuk perbandingan. Saya juga harus menggunakan tanggal lain, karena saya menggunakan versi 2014 dari database. Kedua kueri tersebut mengembalikan hasil yang sama dengan urutan yang sama. Berikut adalah statistik waktu dan IO. Dua varian scan sedikit lebih cepat dan lebih sedikit dibaca, karena varian one-scan harus banyak menggunakan Worktable. Selain itu, varian satu pindaian menghasilkan lebih banyak baris daripada yang dibutuhkan seperti yang dapat Anda lihat dalam rencananya. Ini menghasilkan tanggal untuk setiap ProductID yang ada di tabel Produk, bahkan jika ProductID tidak memiliki transaksi apa pun. Ada 504 baris dalam tabel Produk, namun hanya 441 produk yang melakukan transaksi di TransactionHistory. Juga, ia menghasilkan rentang tanggal yang sama untuk setiap produk, yang lebih dari yang dibutuhkan. Jika TransactionHistory memiliki sejarah keseluruhan yang lebih panjang, dengan setiap produk individual memiliki sejarah yang relatif singkat, jumlah baris yang tidak dibutuhkan lebih tinggi akan lebih tinggi lagi. Di sisi lain, adalah mungkin untuk mengoptimalkan varian dua-scan sedikit lebih jauh dengan menciptakan indeks lain yang lebih sempit hanya pada (ProductID, TransactionDate). Indeks ini akan digunakan untuk menghitung tanggal mulai setiap produk (CTEProducts) dan indeks halamannya kurang dari indeks dan akibatnya kurang banyak dibaca. Jadi, kita bisa memilih, punya pemindaian sederhana eksplisit ekstra, atau punya Worktable implisit. BTW, jika tidak apa-apa hasilnya dengan hanya ringkasan harian, maka lebih baik buat indeks yang tidak termasuk ReferenceOrderID. Ini akan mengurangi halaman kurang IO. Solusi pass tunggal menggunakan CROSS APPLY Ini menjadi jawaban yang sangat panjang, namun inilah satu varian lagi yang hanya mengembalikan ringkasan harian lagi, namun hanya ada satu pemindaian data dan tidak memerlukan pengetahuan eksternal tentang rentang tanggal atau daftar ProductID. Itu tidak melakukan menengah Sorts juga. Kinerja keseluruhannya mirip dengan varian sebelumnya, meski nampaknya sedikit lebih buruk. Ide utamanya adalah menggunakan tabel angka untuk menghasilkan baris yang akan mengisi kesenjangan pada tanggal. Untuk setiap tanggal yang ada gunakan LEAD untuk menghitung ukuran gap dalam hitungan hari dan kemudian gunakan CROSS APPLY untuk menambahkan jumlah baris yang dibutuhkan ke dalam kumpulan hasil. Awalnya saya mencobanya dengan tabel angka tetap. Rencananya menunjukkan sejumlah besar terbaca di tabel ini, meskipun durasi sebenarnya hampir sama, seperti ketika saya menghasilkan angka dengan cepat menggunakan CTE. Rencananya ini lebih panjang, karena query menggunakan dua fungsi window (LEAD dan SUM). Solusi SQLCLR alternatif yang dijalankan lebih cepat dan membutuhkan lebih sedikit memori: Itu memerlukan set izin EXTERNALACCESS karena menggunakan koneksi loopback ke server target dan database dan bukan koneksi konteks yang lambat. Ini adalah cara memanggil fungsi: Menghasilkan hasil yang persis sama, dalam urutan yang sama, sebagai pertanyaan. Profiler logis berbunyi: 481 Keuntungan utama dari implementasi ini adalah bahwa ia lebih cepat daripada menggunakan koneksi konteks, dan menggunakan sedikit memori. Ini hanya menyimpan dua hal dalam memori pada satu waktu saja: Setiap baris duplikat (produk dan tanggal transaksi yang sama). Hal ini diperlukan karena sampai produk atau tanggal berubah, kami tidak tahu berapa jumlah penjumlahan akhir. Pada data sampel, ada satu kombinasi produk dan tanggal yang memiliki 64 baris. Sebuah sliding 45 hari kisaran biaya dan tanggal transaksi saja, untuk produk saat ini. Hal ini diperlukan untuk menyesuaikan jumlah berjalan sederhana untuk baris yang meninggalkan jendela geser 45-hari. Caching minimal ini harus memastikan metode ini dinilai dengan baik tentunya lebih baik daripada mencoba menahan seluruh input yang ada dalam memori CLR. Jika Anda menggunakan edisi Enterprise 64-bit, Developer, atau Evaluation dari SQL Server 2014, Anda dapat menggunakan OLTP dalam memori. Solusinya tidak akan menjadi pemindaian tunggal dan dan hampir tidak akan menggunakan fungsi jendela sama sekali tapi mungkin menambahkan beberapa nilai pada pertanyaan ini dan algoritma yang digunakan dapat digunakan sebagai inspirasi untuk solusi lainnya. Pertama, Anda perlu mengaktifkan OLTP Dalam Memori di database AdventureWorks. Parameter ke prosedur adalah variabel tabel In-Memory dan yang harus didefinisikan sebagai tipe. ID tidak unik dalam tabel ini, unik untuk setiap kombinasi ProductID dan TransactionDate. Ada beberapa komentar dalam prosedur yang memberitahu Anda apa yang dilakukannya tapi secara keseluruhan menghitung total berjalan dalam satu lingkaran dan untuk setiap iterasi, pencarian untuk total berjalan seperti 45 hari yang lalu (atau lebih). Total berjalan saat ini dikurangi total berjalan seperti 45 hari yang lalu adalah jumlah 45 hari yang kami cari. Ajukan prosedur seperti ini. Menguji ini di komputer saya Statistik Klien melaporkan waktu eksekusi total sekitar 750 milidetik. Untuk perbandingan versi sub-kueri membutuhkan 3,5 detik. Algoritma ini juga bisa digunakan oleh T-SQL biasa. Hitung total running, gunakan range not rows, dan simpan hasilnya di temp table. Kemudian Anda dapat query tabel dengan diri bergabung ke total berjalan seperti itu 45 hari yang lalu dan menghitung jumlah rolling. Namun, penerapan rentang dibandingkan dengan deretan cukup lambat karena kenyataan bahwa perlu penanganan duplikat pesanan secara klausa berbeda sehingga saya tidak mendapatkan semua kinerja yang baik dengan pendekatan ini. Solusi untuk itu bisa menggunakan fungsi jendela lain seperti lastvalue () melebihi total total yang dihitung dengan menggunakan baris untuk mensimulasikan rentang yang berjalan total. Cara lain adalah dengan menggunakan max () over (). Keduanya memiliki beberapa masalah. Menemukan indeks yang sesuai untuk digunakan untuk menghindari jenis dan menghindari kelopak dengan versi max () over (). Saya menyerah mengoptimalkan hal-hal itu tapi jika Anda tertarik dengan kode yang saya miliki sejauh ini, tolong beritahu saya. Jawab 15 Sep 15 di 12:38 Nah itu menyenangkan :) Solusi saya sedikit lebih lambat dari GeoffPattersons tapi bagian dari itu adalah kenyataan bahwa saya mengikat kembali ke tabel asli untuk menghilangkan salah satu asumsi Geoffs (yaitu satu baris per Pasangan productdate). Saya pergi dengan asumsi ini adalah versi sederhana dari kueri terakhir dan mungkin memerlukan informasi tambahan dari tabel aslinya. Catatan: Saya meminjam tabel kalender Geoffs dan sebenarnya berakhir dengan solusi yang sangat mirip: Inilah kueri itu sendiri: Pada dasarnya saya memutuskan bahwa cara termudah untuk mengatasinya adalah dengan menggunakan opsi untuk klausa ROWS. Tapi yang dibutuhkan itu saya hanya punya satu baris per ProductID. Kombinasi TransactionDate dan tidak hanya itu, tapi saya harus memiliki satu baris per ProductID dan tanggal yang mungkin. Saya melakukannya dengan menggabungkan tabel Product, calendar dan TransactionHistory di CTE. Lalu aku harus membuat CTE lain untuk menghasilkan informasi bergulir. Saya harus melakukan ini karena jika saya bergabung kembali dengan meja asli secara langsung saya mendapat penghapusan baris yang membuang hasil saya. Setelah itu, adalah masalah sederhana untuk bergabung dengan CTE kedua saya kembali ke meja semula. Saya menambahkan kolom TBE (untuk dihapuskan) untuk menyingkirkan baris kosong yang dibuat di CTE. Saya juga menggunakan CROSS APPLY di CTE awal untuk menghasilkan batasan untuk tabel kalender saya. Saya kemudian menambahkan indeks yang direkomendasikan: Dan dapatkan rencana pelaksanaan akhir: EDIT: Pada akhirnya saya menambahkan sebuah indeks di tabel kalender yang mempercepat kinerja dengan selisih yang wajar. Jawab 10 Sep 16 pada 16:34 Saya memiliki beberapa solusi alternatif yang tidak menggunakan indeks atau tabel referensi. Mungkin mereka bisa berguna dalam situasi di mana Anda tidak memiliki akses ke tabel tambahan dan tidak dapat membuat indeks. Tampaknya ada kemungkinan untuk mendapatkan hasil yang benar saat mengelompokkan oleh TransactionDate hanya dengan satu celah data dan hanya satu fungsi jendela tunggal. Namun, saya tidak dapat menemukan cara untuk melakukannya hanya dengan satu fungsi jendela saat Anda tidak dapat berkelompok dengan TransactionDate. Untuk memberikan kerangka acuan, pada mesin saya, solusi asli yang ada dalam pertanyaan memiliki waktu CPU 2808 ms tanpa indeks penutup dan 1950 ms dengan indeks penutup. Saya sedang menguji dengan database AdventureWorks2014 dan SQL Server Express 2014. Mari kita mulai dengan solusi untuk kapan kita bisa berkelompok dengan TransactionDate. Jumlah yang berjalan selama hari X terakhir juga dapat dinyatakan dengan cara berikut: Menjalankan jumlah untuk satu baris yang berjalan dari semua baris sebelumnya - menjalankan jumlah semua baris sebelumnya yang tanggalnya berada di luar jendela tanggal. Di SQL, salah satu cara untuk mengungkapkannya adalah dengan membuat dua salinan data Anda dan untuk salinan kedua, mengalikan biaya dengan -1 dan menambahkan X1 hari ke kolom tanggal. Menghitung jumlah yang berjalan di atas semua data akan menerapkan rumus di atas. Saya tunjukkan ini untuk beberapa contoh data. Berikut adalah beberapa tanggal sampel untuk ProductID tunggal. Saya mewakili tanggal sebagai angka untuk membuat perhitungan menjadi lebih mudah. Data awal: Tambahkan salinan data kedua. Salinan kedua memiliki 46 hari ditambahkan ke tanggal dan biaya dikalikan dengan -1: Mengambil jumlah berjalan yang diperintahkan oleh Date ascending dan CopiedRow descending: Menyaring baris yang disalin untuk mendapatkan hasil yang diinginkan: SQL berikut adalah salah satu cara untuk menerapkannya. Algoritma di atas: Pada mesin saya, waktu CPU 702 ms dengan indeks penutup dan waktu CPU 734 ms tanpa indeks. Rencana kueri dapat ditemukan di sini: brentozarpastastasetplanidSJdCsGVSl Salah satu kelemahan dari solusi ini adalah bahwa tampaknya ada jenis yang tidak dapat dihindari saat memesan kolom TransactionDate yang baru. Saya tidak berpikir bahwa hal ini dapat diselesaikan dengan menambahkan indeks karena kita perlu menggabungkan dua salinan data sebelum melakukan pemesanan. Saya bisa menyingkirkan semacam di akhir kueri dengan menambahkan kolom yang berbeda ke ORDER BY. Jika saya memesan oleh FilterFlag, saya menemukan bahwa SQL Server akan mengoptimalkan kolom itu dari jenisnya dan akan melakukan sort yang eksplisit. Solusi untuk kapan kita perlu mengembalikan hasil yang ditetapkan dengan nilai TransactionDate duplikat untuk ProductId yang sama jauh lebih rumit. Saya akan meringkas masalah tersebut bersamaan dengan kebutuhan untuk partisi by dan order oleh kolom yang sama. Sintaks yang disediakan oleh Paul memecahkan masalah itu sehingga tidak mengherankan bahwa sangat sulit untuk diungkapkan dengan fungsi jendela saat ini yang tersedia di SQL Server (jika tidak sulit untuk mengungkapkan tidak akan perlu untuk memperluas sintaksnya). Jika saya menggunakan query di atas tanpa mengelompokkan maka saya mendapatkan nilai yang berbeda untuk jumlah penggalangan saat ada beberapa baris dengan ProductId dan TransactionDate yang sama. Salah satu cara untuk mengatasi hal ini adalah dengan melakukan perhitungan jumlah run yang sama seperti di atas tetapi juga untuk menandai baris terakhir di partisi. Hal ini dapat dilakukan dengan LEAD (dengan asumsi ProductID tidak pernah NULL) tanpa tambahan. Untuk nilai run run final, saya menggunakan MAX sebagai fungsi jendela untuk menerapkan nilai pada baris terakhir dari partisi ke semua baris di partisi. Pada mesin saya, waktu CPU 2464ms tanpa indeks penutup. Seperti sebelumnya tampaknya ada semacam yang tidak dapat dihindari. Rencana kueri dapat ditemukan di sini: brentozarpastetheplanidHyWxhGVBl Saya berpikir bahwa ada ruang untuk perbaikan pada kueri di atas. There are certainly other ways to use windows functions to get the desired result. Most people are familiar with the phrase, quotthis will kill two birds with one stonequot. If you39re not, the phase refers to an approach that addresses two objectives in one action. (Unfortunately, the expression itself is rather unpleasant, as most of us don39t want to throw stones at innocent animals) Today I39m going to cover some basics on two great features in SQL Server: the Columnstore index (available only in SQL Server Enterprise) and the SQL Query Store . Microsoft actually implemented the Columnstore index in SQL 2012 Enterprise, though they39ve enhanced it in the last two releases of SQL Server. Microsoft introduced the Query Store in SQL Server 2016. So, what are these features and why are they important Well, I have a demo that will introduce both features and show how they can help us. Before I go any further, I also cover this (and other SQL 2016 features) in my CODE Magazine article on new features SQL 2016. As a basic introduction, the Columnstore index can help speed up queries that scanaggregate over large amounts of data, and the Query Store tracks query executions, execution plans, and runtime statistics that you39d normally need to collect manually. Trust me when I say, these are great features. For this demo, I39ll be using the Microsoft Contoso Retail Data Warehouse demo database. Loosely speaking, Contoso DW is like quota really big AdventureWorksquot, with tables containing millions of rows. (The largest AdventureWorks table contains roughly 100,000 rows at most). You can download the Contoso DW database here: microsoften-usdownloaddetails. aspxid18279. Contoso DW works very well when you want to test performance on queries against larger tables. Contoso DW contains a standard data warehouse Fact table called FactOnLineSales, with 12.6 million rows. That39s certainly not the largest data warehouse table in the world, but it39s not child39s play either. Suppose I want to summarize product sales amount for 2009, and rank the products. I might query the fact table and join to the Product Dimension table and use a RANK function, like so: Here39s a partial result set of the top 10 rows, by Total Sales. On my laptop (i7, 16 GB of RAM), the query takes anywhere from 3-4 seconds to run. That might not seem like the end of the world, but some users might expect near-instant results (the way you might see near-instant results when using Excel against an OLAP cube). The only index I currently have on this table is a clustered index on a sales key. If I look at the execution plan, SQL Server makes a suggestion to add a covering index to the table: Now, just because SQL Server suggests an index, doesn39t mean you should blindly create indexes on every quotmissing indexquot message. However, in this instance, SQL Server detects that we are filtering based on year, and using the Product Key and Sales Amount. So, SQL Server suggests a covering index, with the DateKey as the index key field. The reason we call this a quotcoveringquot index is because SQL Server will quotbring along the non-key fieldsquot we used in the query, quotfor the ridequot. That way, SQL Server doesn39t need to use the table or the clustered index at all the database engine can simply use the covering index for the query. Covering indexes are popular in certain data warehousing and reporting database scenarios, though they do come at a cost of the database engine maintaining them. Note: Covering indexes have been around for a long time, so I haven39t yet covered the Columnstore index and the Query Store. So, I will add the covering index: If I re-execute the same query I ran a moment ago (the one that aggregated the sales amount for each product), the query sometimes seems to run about a second faster, and I get a different execution plan, one that uses an Index Seek instead of an Index Scan (using the date key on the covering index to retrieve sales for 2009). So, prior to the Columnstore Index, this could be one way to optimize this query in much older versions of SQL Server. It runs a little faster than the first one, and I get an execution plan with an Index Seek instead of an Index Scan. However, there are some issues: The two execution operators quotIndex Seekquot and quotHash Match (Aggregate)quot both essentially operate quotrow by rowquot. Imagine this in a table with hundreds of millions of rows. Related, think about the contents of a fact table: in this case, a single date key value andor a single product key value might be repeated across hundreds of thousands of rows (remember, the fact table also has keys for geography, promotion, salesman, etc.) So, when the quotIndex Seekquot and quotHash Matchquot work row by row, they are doing so over values that might be repeated across many other rows. This is normally where I39d segue to the SQL Server Columnstore index, which offers a scenario to improve the performance of this query in amazing ways. But before I do that, let39s go back in time. Let39s go back to the year 2010, when Microsoft introduced an add-in for Excel known as PowerPivot. Many people probably remember seeing demos of PowerPivot for Excel, where a user could read millions of rows from an outside data source into Excel. PowerPivot would compress the data, and provide an engine to create Pivot Tables and Pivot Charts that performed at amazing speeds against the compressed data. PowerPivot used an in-memory technology that Microsoft termed quotVertiPaqquot. This in-memory technology in PowerPivot would basically take duplicate business keyforeign key values and compress them down to a single vector. The in-memory technology would also scanaggregate these values in parallel, in blocks of several hundred at a time. The bottom line is that Microsoft baked a large amount of performance enhancements into the VertiPaq in-memory feature for us to use, right out of the proverbial box. Why am I taking this little stroll down memory lane Because in SQL Server 2012, Microsoft implemented one of the most important features in the history of their database engine: the Columnstore index. The index is really an index in name only: it is a way to take a SQL Server table and create a compressed, in-memory columnstore that compresses duplicate foreign key values down to single vector values. Microsoft also created a new buffer pool to read these compressed vector values in parallel, creating the potential for huge performance gains. So, I39m going to create a columnstore index on the table, and I39ll see how much better (and more efficiently) the query runs, versus the query that runs against the covering index. So, I39ll create a duplicate copy of FactOnlineSales (I39ll call it FactOnlineSalesDetailNCCS), and I39ll create a columnstore index on the duplicated table that way I won39t interfere with the original table and the covering index in any way. Next, I39ll create a columnstore index on the new table: Note several things: I39ve specified several foreign key columns, as well as the Sales Amount. Remember that a columnstore index is not like a traditional row-store index. There is no quotkeyquot. We are simply indicating which columns SQL Server should compress and place in an in-memory columnstore. To use the analogy of PowerPivot for Excel when we create a columnstore index, we39re telling SQL Server to essentially do the same thing that PowerPivot did when we imported 20 million rows into Excel using PowerPivot So, I39ll re-run the query, this time using the duplicated FactOnlineSalesDetailNCCS table that contains the columnstore index. This query runs instantly in less than a second. And I can also say that even if the table had hundreds of millions of rows, it would still run at the proverbial quotbat of an eyelashquot. We could look at the execution plan (and in a few moments, we will), but now it39s time to cover the Query Store feature. Imagine for a moment, that we ran both queries overnight: the query that used the regular FactOnlineSales table (with the covering index) and then the query that used the duplicated table with the Columnstore index. When we log in the following morning, we39d like to see the execution plan for both queries as they took place, as well as the execution statistics. In other words, we39d like to see the same statistics that we39d be able to see if we ran both queries interactively in SQL Management Studio, turned in TIME and IO Statistics, and viewed the execution plan right after executing the query. Well, that39s what the Query Store allows us to do we can turn on (enable) Query Store for a database, which will trigger SQL Server to store query execution and plan statistics so that we can view them later. So, I39m going to enable the Query Store on the Contoso database with the following command (and I39ll also clear out any caching): Then I39ll run the two queries (and quotpretendquot that I ran them hours ago): Now let39s pretend they ran hours ago. According to what I said, the Query Store will capture the execution statistics. So how do I view them Fortunately, that39s quite easy. If I expand the Contoso DW database, I39ll see a Query Store folder. The Query Store has tremendous functionality and I39ll try to cover much of it in subsequent blog posts. But for right now, I want to view execution statistics on the two queries, and specifically examine the execution operators for the columnstore index. So I39ll right-click on the Top Resource Consuming Queries and run that option. That gives me a chart like the one below, where I can see execution duration time (in milliseconds) for all queries that have been executed. In this instance, Query 1 was the query against the original table with the covering index, and Query 2 was against the table with the columnstore index. The numbers don39t lie the columnstore index outperformed the original tablecovering index by a factor of almost 7 to 1. I can change the metric to look at memory consumption instead. In this case, note that query 2 (the columnstore index query) used far more memory. This demonstrates clearly why the columnstore index represents quotin-memoryquot technology SQL Server loads the entire columnstore index in memory, and uses a completely different buffer pool with enhanced execution operators to process the index. OK, so we have some graphs to view execution statistics can we see the execution plan (and execution operators) associated with each execution Yes, we can If you click on the vertical bar for the query that used the columnstore index, you39ll see the execution plan below. The first thing we see is that SQL Server performed a columnstore index scan, and that represented nearly 100 of the cost of the query. You might be saying, quotWait a minute, the first query used a covering index and performed an index seek so how can a columnstore index scan be fasterquot That39s a legitimate question, and fortunately there39s an answer. Even when the first query performed an index seek, it still executed quotrow by rowquot. If I put the mouse over the columnstore index scan operator, I see a tooltip (like the one below), with one important setting: the Execution Mode is BATCH (as opposed to ROW . which is what we had with the first query using the covering index). That BATCH mode tells us that SQL Server is processing the compressed vectors (for any foreign key values that are duplicated, such as the product key and date key) in batches of almost 1,000, in parallel. So SQL Server is still able to process the columnstore index much more efficiently. Additionally, if I place the mouse over the Hash Match (Aggregate) task, I also see that SQL Server is aggregating the columnstore index using Batch mode (although the operator itself represents such a tiny percent of the cost of the query) Finally, you might be asking, quotOK, so SQL Server compresses the values in the data, treats the values as vectors, and read them in blocks of almost a thousand values in parallel but my query only wanted data for 2009. So is SQL Server scanning over the entire set of dataquot Again, a good question. The answer is, quotNot reallyquot. Fortunately for us, the new columnstore index buffer pool performs another function called quotsegment eliminationquot. Basically, SQL Server will examine the vector values for the date key column in the columnstore index, and eliminate segments that are outside the scope of the year 2009. I39ll stop here. In subsequent blog posts I39ll cover both the columnstore index and Query Store in more detail. Essentially, what we39ve seen here today is that the Columnstore index can significantly speed up queries that scanaggregate over large amounts of data, and the Query Store will capture query executions and allow us to examine execution and performance statistics later. In the end, we39d like to produce a result set that shows the following. Notice three things: The columns essentially pivot all of the possible Return Reasons, after showing the sales amount The result set contains subtotals by the week ending (Sunday) date across all clients (where the Client is NULL) The result set contains a grand total row (where the Client and Date are both NULL) First, before I get into the SQL end we could use the dynamic pivotmatrix capability in SSRS. We would simply need to combine the two result sets by one column and then we could feed the results to the SSRS matrix control, which will spread the return reasons across the columns axis of the report. However, not everyone uses SSRS (though most people should). But even then, sometimes developers need to consume result sets in something other than a reporting tool. So for this example, let39s assume we want to generate the result set for a web grid page and possibly the developer wants to quotstrip outquot the subtotal rows (where I have a ResultSetNum value of 2 and 3) and place them in a summary grid. So bottom line, we need to generate the output above directly from a stored procedure. And as an added twist next week there could be Return Reason X and Y and Z. So we don39t know how many return reasons there could be. We simple want the query to pivot on the possible distinct values for Return Reason. Here is where the T-SQL PIVOT has a restriction we need to provide it the possible values. Since we won39t know that until run-time, we need to generate the query string dynamically using the dynamic SQL pattern. The dynamic SQL pattern involves generating the syntax, piece by piece, storing it in a string, and then executing the string at the end. Dynamic SQL can be tricky, as we have to embed syntax inside a string. But in this case, it our only true option if we want to handle a variable number of return reasons. I39ve always found that the best way to create a dynamic SQL solution is by figuring out what the quotidealquot generated-query would be at the end (in this case, given the Return reasons we know about).and then reverse-engineering it by piecing it together one part at a time. And so, here is the SQL we need if we knew those Return Reasons (A through D) were static and would not change. The query does the following: Combines the data from SalesData with the data from ReturnData, where we quothard-wirequot the word Sales as an Action Type form the Sales Table, and then use the Return Reason from the Return Data into the same ActionType column. That will give us a clean ActionType column on which to pivot. We are combining the two SELECT statements into a common table expression (CTE), which is basically a derived table subquery that we subsequently use in the next statement (to PIVOT) A PIVOT statement against the CTE, that sums the dollars for the Action Type being in one of the possible Action Type values. Note that this isn39t the final result set. We are placing this into a CTE that reads from the first CTE. The reason for this is because we want to do multiple groupings at the end. The final SELECT statement, that reads from the PIVOTCTE, and combines it with a subsequent query against the same PIVOTCTE, but where we also implement two groupings in the GROUPING SETS feature in SQL 2008: GROUPING by the Week End Date (dbo. WeekEndingDate) GROUPING for all rows () So if we knew with certainty that we39d never have more return reason codes, then that would be the solution. However, we need to account for other reason codes. So we need to generate that entire query above as one big string where we construct the possible return reasons as one comma separated list. I39m going to show the entire T-SQL code to generate (and execute) the desired query. And then I39ll break it out into parts and explain each step. So first, here39s the entire code to dynamically generate what I39ve got above. There are basically five steps we need to cover. Step 1 . we know that somewhere in the mix, we need to generate a string for this in the query: SalesAmount, Reason A, Reason B, Reason C, Reason D0160016001600160 What we can do is built a temporary common table expression that combines the hard wired quotSales Amountquot column with the unique list of possible reason codes. Once we have that in a CTE, we can use the nice little trick of FOR XML PATH(3939) to collapse those rows into a single string, put a comma in front of each row that the query reads, and then use STUFF to replace the first instance of a comma with an empty space. This is a trick that you can find in hundreds of SQL blogs. So this first part builds a string called ActionString that we can use further down. Langkah 2 . we also know that we39ll want to SUM the generatedpivoted reason columns, along with the standard sales column. So we39ll need a separate string for that, which I39ll call SUMSTRING. I39ll simply use the original ActionString, and then REPLACE the outer brackets with SUM syntax, plus the original brackets. Step 3: Now the real work begins. Using that original query as a model, we want to generate the original query (starting with the UNION of the two tables), but replacing any references to pivoted columns with the strings we dynamically generated above. Also, while not absolutely required, I39ve also created a variable to simply any carriage returnline feed combinations that we want to embed into the generated query (for readability). So we39ll construct the entire query into a variable called SQLPivotQuery. Step 4 . We continue constructing the query again, concatenating the syntax we can quothard-wirequot with the ActionSelectString (that we generated dynamically to hold all the possible return reason values) Step 5 . Finally, we39ll generate the final part of the Pivot Query, that reads from the 2 nd common table expression (PIVOTCTE, from the model above) and generates the final SELECT to read from the PIVOTCTE and combine it with a 2 nd read against PIVOTCTE to implement the grouping sets. Finally, we can quotexecutequot the string using the SQL system stored proc spexecuteSQL So hopefully you can see that the process to following for this type of effort is Determine what the final query would be, based on your current set of data and values (i. e. built a query model) Write the necessary T-SQL code to generate that query model as a string. Arguably the most important part is determining the unique set of values on which you39ll PIVOT, and then collapsing them into one string using the STUFF function and the FOR XML PATH(3939) trick So whats on my mind today Well, at least 13 items Two summers ago, I wrote a draft BDR that focused (in part) on the role of education and the value of a good liberal arts background not just for the software industry but even for other industries as well. One of the themes of this particular BDR emphasized a pivotal and enlightened viewpoint from renowned software architect Allen Holub regarding liberal arts. Ill (faithfully) paraphrase his message: he highlighted the parallels between programming and studying history, by reminding everyone that history is reading and writing (and Ill add, identifying patterns), and software development is also reading and writing (and again, identifying patterns). And so I wrote an opinion piece that focused on this and other related topics. But until today, I never got around to either publishingposting it. Every so often Id think of revising it, and Id even sit down for a few minutes and make some adjustments to it. But then life in general would get in the way and Id never finish it. So what changed A few weeks ago, fellow CoDe Magazine columnist and industry leader Ted Neward wrote a piece in his regular column, Managed Coder , that caught my attention. The title of the article is On Liberal Arts. and I highly recommend that everyone read it. Ted discusses the value of a liberal arts background, the false dichotomy between a liberal arts background and success in software development, and the need to writecommunicate well. He talks about some of his own past encounters with HR personnel management regarding his educational background. He also emphasizes the need to accept and adapt to changes in our industry, as well as the hallmarks of a successful software professional (being reliable, planning ahead, and learning to get past initial conflict with other team members). So its a great read, as are Teds other CoDe articles and blog entries. It also got me back to thinking about my views on this (and other topics) as well, and finally motivated me to finish my own editorial. So, better late than never, here are my current Bakers Dozen of Reflections: I have a saying: Water freezes at 32 degrees . If youre in a trainingmentoring role, you might think youre doing everything in the world to help someone when in fact, theyre only feeling a temperature of 34 degrees and therefore things arent solidifying for them. Sometimes it takes just a little bit more effort or another ideachemical catalyst or a new perspective which means those with prior education can draw on different sources. Water freezes at 32 degrees . Some people can maintain high levels of concentration even with a room full of noisy people. Im not one of them occasionally I need some privacy to think through a critical issue. Some people describe this as you gotta learn to walk away from it. Stated another way, its a search for the rarefied air. This past week I spent hours in half-lit, quiet room with a whiteboard, until I fully understood a problem. It was only then that I could go talk with other developers about a solution. The message here isnt to preach how you should go about your business of solving problems but rather for everyone to know their strengths and what works, and use them to your advantage as much as possible. Some phrases are like fingernails on a chalkboard for me. Use it as a teaching moment is one. (Why is it like fingernails on a chalkboard Because if youre in a mentoring role, you should usually be in teaching moment mode anyway, however subtly). Heres another I cant really explain it in words, but I understand it. This might sound a bit cold, but if a person truly cant explain something in words, maybe they dont understand. Sure, a person can have a fuzzy sense of how something works I can bluff my way through describing how a digital camera works but the truth is that I dont really understand it all that well. There is a field of study known as epistemology (the study of knowledge). One of the fundamental bases of understanding whether its a camera or a design pattern - is the ability to establish context, to identify the chain of related events, the attributes of any components along the way, etc. Yes, understanding is sometimes very hard work, but diving into a topic and breaking it apart is worth the effort. Even those who eschew certification will acknowledge that the process of studying for certification tests will help to fill gaps in knowledge. A database manager is more likely to hire a database developer who can speak extemporaneously (and effortlessly) about transaction isolation levels and triggers, as opposed to someone who sort of knows about it but struggles to describe their usage. Theres another corollary here. Ted Neward recommends that developers take up public speaking, blogging, etc. I agree 100. The process of public speaking and blogging will practically force you to start thinking about topics and breaking down definitions that you might have otherwise taken for granted. A few years ago I thought I understood the T-SQL MERGE statement pretty well. But only after writing about it, speaking about, fielding questions from others who had perspectives that never occurred to me that my level of understanding increased exponentially. I know a story of a hiring manager who once interviewed an authordeveloper for a contract position. The hiring manager was contemptuous of publications in general, and barked at the applicant, So, if youre going to work here, would you rather be writing books or writing code Yes, Ill grant that in any industry there will be a few pure academics. But what the hiring manager missed was the opportunities for strengthening and sharpening skill sets. While cleaning out an old box of books, I came across a treasure from the 1980s: Programmers at Work. which contains interviews with a very young Bill Gates, Ray Ozzie, and other well-known names. Every interview and every insight is worth the price of the book. In my view, the most interesting interview was with Butler Lampson. who gave some powerful advice. To hell with computer literacy. Its absolutely ridiculous. Study mathematics. Learn to think. Read. Write. These things are of more enduring value. Learn how to prove theorems: A lot of evidence has accumulated over the centuries that suggests this skill is transferable to many other things. Butler speaks the truth . Ill add to that point learn how to play devils advocate against yourself. The more you can reality-check your own processes and work, the better off youll be. The great computer scientistauthor Allen Holub made the connection between software development and the liberal arts specifically, the subject of history. Here was his point: what is history Reading and writing. What is software development Among other things, reading and writing . I used to give my students T-SQL essay questions as practice tests. One student joked that I acted more like a law professor. Well, just like Coach Donny Haskins said in the movie Glory Road, my way is hard. I firmly believe in a strong intellectual foundation for any profession. Just like applications can benefit from frameworks, individuals and their thought processes can benefit from human frameworks as well. Thats the fundamental basis of scholarship. There is a story that back in the 1970s, IBM expanded their recruiting efforts in the major universities by focusing on the best and brightest of liberal arts graduates. Even then they recognized that the best readers and writers might someday become strong programmersystems analysts. (Feel free to use that story to any HR-type who insists that a candidate must have a computer science degree) And speaking of history: if for no other reason, its important to remember the history of product releases if Im doing work at a client site thats still using SQL Server 2008 or even (gasp) SQL Server 2005, I have to remember what features were implemented in the versions over time. Ever have a favorite doctor whom you liked because heshe explained things in plain English, gave you the straight truth, and earned your trust to operate on you Those are mad skills . and are the result of experience and HARD WORK that take years and even decades to cultivate. There are no guarantees of job success focus on the facts, take a few calculated risks when youre sure you can see your way to the finish line, let the chips fall where they may, and never lose sight of being just like that doctor who earned your trust. Even though some days I fall short, I try to treat my client and their data as a doctor would treat patients. Even though a doctor makes more money There are many clichs I detest but heres one I dont hate: There is no such thing as a bad question. As a former instructor, one thing that drew my ire was hearing someone criticize another person for asking a supposedly, stupid question. A question indicates a person acknowledges they have some gap in knowledge theyre looking to fill. Yes, some questions are better worded than others, and some questions require additional framing before they can be answered. But the journey from forming a question to an answer is likely to generate an active mental process in others. There are all GOOD things. Many good and fruitful discussions originate with a stupid question. I work across the board in SSIS, SSAS, SSRS, MDX, PPS, SharePoint, Power BI, DAX all the tools in the Microsoft BI stack. I still write some code from time to time. But guess what I still spend so much time doing writing T-SQL code to profile data as part of the discovery process. All application developers should have good T-SQL chops. Ted Neward writes (correctly) about the need to adapt to technology changes. Ill add to that the need to adapt to clientemployer changes. Companies change business rules. Companies acquire other companies (or become the target of an acquisition). Companies make mistakes in communicating business requirements and specifications. Yes, we can sometimes play a role in helping to manage those changes and sometimes were the fly, not the windshield. These sometimes cause great pain for everyone, especially the I. T. people. This is why the term fact of life exists we have to deal with it. Just like no developer writes bug-free code every time, no I. T. person deals well with change every single time. One of the biggest struggles Ive had in my 28 years in this industry is showing patience and restraint when changes are flying from many different directions. Here is where my prior suggestion about searching for the rarified air can help. If you can manage to assimilate changes into your thought process, and without feeling overwhelmed, odds are youll be a significant asset. In the last 15 months Ive had to deal with a huge amount of professional change. Its been very difficult at times, but Ive resolved that change will be the norm and Ive tried to tweak my own habits as best I can to cope with frequent (and uncertain) change. Its hard, very hard. But as coach Jimmy Duggan said in the movie A League of Their Own: Of course its hard. If it wasnt hard, everyone would do it. The hard, is what makes it great . A powerful message. Theres been talk in the industry over the last few years about conduct at professional conferences (and conduct in the industry as a whole). Many respected writers have written very good editorials on the topic. Heres my input, for what its worth. Its a message to those individuals who have chosen to behave badly: Dude, it shouldnt be that hard to behave like an adult. A few years ago, CoDe Magazine Chief Editor Rod Paddock made some great points in an editorial about Codes of Conduct at conferences. Its definitely unfortunate to have to remind people of what they should expect out of themselves. But the problems go deeper. A few years ago I sat on a five-person panel (3 women, 2 men) at a community event on Women in Technology. The other male stated that men succeed in this industry because the Y chromosome gives men an advantage in areas of performance. The individual who made these remarks is a highly respected technology expert, and not some bozo making dongle remarks at a conference or sponsoring a programming contest where first prize is a date with a bikini model. Our world is becoming increasingly polarized (just watch the news for five minutes), sadly with emotion often winning over reason. Even in our industry, recently I heard someone in a position of responsibility bash software tool XYZ based on a ridiculous premise and then give false praise to a competing tool. So many opinions, so many arguments, but heres the key: before taking a stand, do your homework and get the facts . Sometimes both sides are partly rightor wrong. Theres only one way to determine: get the facts. As Robert Heinlein wrote, Facts are your single clue get the facts Of course, once you get the facts, the next step is to express them in a meaningful and even compelling way. Theres nothing wrong with using some emotion in an intellectual debate but it IS wrong to replace an intellectual debate with emotion and false agenda. A while back I faced resistance to SQL Server Analysis Services from someone who claimed the tool couldnt do feature XYZ. The specifics of XYZ dont matter here. I spent about two hours that evening working up a demo to cogently demonstrate the original claim was false. In that example, it worked. I cant swear it will always work, but to me thats the only way. Im old enough to remember life at a teen in the 1970s. Back then, when a person lost hisher job, (often) it was because the person just wasnt cutting the mustard. Fast-forward to today: a sad fact of life is that even talented people are now losing their jobs because of the changing economic conditions. Theres never a full-proof method for immunity, but now more than ever its critical to provide a high level of what I call the Three Vs (value, versatility, and velocity) for your employerclients. I might not always like working weekends or very late at night to do the proverbial work of two people but then I remember there are folks out there who would give anything to be working at 1 AM at night to feed their families and pay their bills. Always be yourselfyour BEST self. Some people need inspiration from time to time. Heres mine: the great sports movie, Glory Road. If youve never watched it, and even if youre not a sports fan I can almost guarantee youll be moved like never before. And Ill close with this. If you need some major motivation, Ill refer to a story from 2006. Jason McElwain, a high school student with autism, came off the bench to score twenty points in a high school basketball game in Rochester New York. Heres a great YouTube video. His mother said it all . This is the first moment Jason has ever succeeded and is proud of himself. I look at autism as the Berlin Wall. He cracked it. To anyone who wanted to attend my session at todays SQL Saturday event in DC I apologize that the session had to be cancelled. I hate to make excuses, but a combination of getting back late from Detroit (client trip), a car thats dead (blown head gasket), and some sudden health issues with my wife have made it impossible for me to attend. Back in August, I did the same session (ColumnStore Index) for PASS as a webinar. You can go to this link to access the video (itll be streamed, as all PASS videos are streamed) The link does require that you fill out your name and email address, but thats it. And then you can watch the video. Feel free to contact me if you have questions, at kgoffkevinsgoff November 15, 2013 Getting started with Windows Azure and creating SQL Databases in the cloud can be a bit daunting, especially if youve never tried out any of Microsofts cloud offerings. Fortunately, Ive created a webcast to help people get started. This is an absolute beginners guide to creating SQL Databases under Windows Azure. It assumes zero prior knowledge of Azure. You can go to the BDBI Webcasts of this website and check out my webcast (dated 11102013). Or you can just download the webcast videos right here: here is part 1 and here is part 2. You can also download the slide deck here. November 03, 2013 Topic this week: SQL Server Snapshot Isolation Levels, added in SQL Server 2005. To this day, there are still many SQL developers, many good SQL developers who either arent aware of this feature, or havent had time to look at it. Hopefully this information will help. Companion webcast will be uploaded in the next day look for it in the BDBI Webcasts section of this blog. October 26, 2013 Im going to start a weekly post of T-SQL tips, covering many different versions of SQL Server over the years Heres a challenge many developers face. Ill whittle it down to a very simple example, but one where the pattern applies to many situations. Suppose you have a stored procedure that receives a single vendor ID and updates the freight for all orders with that vendor id. create procedure dbo. UpdateVendorOrders update Purchasing. PurchaseOrderHeader set Freight Freight 1 where VendorID VendorID Now, suppose we need to run this for a set of vendor IDs. Today we might run it for three vendors, tomorrow for five vendors, the next day for 100 vendors. We want to pass in the vendor IDs. If youve worked with SQL Server, you can probably guess where Im going with this. The big question is how do we pass a variable number of Vendor IDs Or, stated more generally, how do we pass an array, or a table of keys, to a procedure Something along the lines of exec dbo. UpdateVendorOrders SomeListOfVendors Over the years, developers have come up with different methods: Going all the way back to SQL Server 2000, developers might create a comma-separated list of vendor keys, and pass the CSV list as a varchar to the procedure. The procedure would shred the CSV varchar variable into a table variable and then join the PurchaseOrderHeader table to that table variable (to update the Freight for just those vendors in the table). I wrote about this in CoDe Magazine back in early 2005 (code-magazinearticleprint. aspxquickid0503071ampprintmodetrue. Tip 3) In SQL Server 2005, you could actually create an XML string of the vendor IDs, pass the XML string to the procedure, and then use XQUERY to shred the XML as a table variable. I also wrote about this in CoDe Magazine back in 2007 (code-magazinearticleprint. aspxquickid0703041ampprintmodetrue. Tip 12)Also, some developers will populate a temp table ahead of time, and then reference the temp table inside the procedure. All of these certainly work, and developers have had to use these techniques before because for years there was NO WAY to directly pass a table to a SQL Server stored procedure. Until SQL Server 2008 when Microsoft implemented the table type. This FINALLY allowed developers to pass an actual table of rows to a stored procedure. Now, it does require a few steps. We cant just pass any old table to a procedure. It has to be a pre-defined type (a template). So lets suppose we always want to pass a set of integer keys to different procedures. One day it might be a list of vendor keys. Next day it might be a list of customer keys. So we can create a generic table type of keys, one that can be instantiated for customer keys, vendor keys, etc. CREATE TYPE IntKeysTT AS TABLE ( IntKey int NOT NULL ) So Ive created a Table Typecalled IntKeysTT . Its defined to have one column an IntKey. Nowsuppose I want to load it with Vendors who have a Credit Rating of 1..and then take that list of Vendor keys and pass it to a procedure: DECLARE VendorList IntKeysTT INSERT INTO VendorList SELECT BusinessEntityID from Purchasing. Vendor WHERE CreditRating 1 So, I now have a table type variable not just any table variable, but a table type variable (that I populated the same way I would populate a normal table variable). Its in server memory (unless it needs to spill to tempDB) and is therefore private to the connectionprocess. OK, can I pass it to the stored procedure now Well, not yet we need to modify the procedure to receive a table type. Heres the code: create procedure dbo. UpdateVendorOrdersFromTT IntKeysTT IntKeysTT READONLY update Purchasing. PurchaseOrderHeader set Freight Freight 1 FROM Purchasing. PurchaseOrderHeader JOIN IntKeysTT TempVendorList ON PurchaseOrderHeader. VendorID Te mpVendorList. IntKey Notice how the procedure receives the IntKeysTT table type as a Table Type (again, not just a regular table, but a table type). It also receives it as a READONLY parameter. You CANNOT modify the contents of this table type inside the procedure. Usually you wont want to you simply want to read from it. Well, now you can reference the table type as a parameter and then utilize it in the JOIN statement, as you would any other table variable. Jadi begitulah. A bit of work to set up the table type, but in my view, definitely worth it. Additionally, if you pass values from , youre in luck. You can pass an ADO data table (with the same tablename property as the name of the Table Type) to the procedure. For developers who have had to pass CSV lists, XML strings, etc. to a procedure in the past, this is a huge benefit. Finally I want to talk about another approach people have used over the years. SQL Server Cursors. At the risk of sounding dogmatic, I strongly advise against Cursors, unless there is just no other way. Cursors are expensive operations in the server, For instance, someone might use a cursor approach and implement the solution this way: DECLARE VendorID int DECLARE dbcursor CURSOR FASTFORWARD FOR SELECT BusinessEntityID from Purchasing. Vendor where CreditRating 1 FETCH NEXT FROM dbcursor INTO VendorID WHILE FETCHSTATUS 0 EXEC dbo. UpdateVendorOrders VendorID FETCH NEXT FROM dbcursor INTO VendorID The best thing Ill say about this is that it works. And yes, getting something to work is a milestone. But getting something to work and getting something to work acceptably are two different things. Even if this process only takes 5-10 seconds to run, in those 5-10 seconds the cursor utilizes SQL Server resources quite heavily. Thats not a good idea in a large production environment. Additionally, the more the of rows in the cursor to fetch and the more the number of executions of the procedure, the slower it will be. When I ran both processes (the cursor approach and then the table type approach) against a small sampling of vendors (5 vendors), the processing times where 260 ms and 60 ms, respectively. So the table type approach was roughly 4 times faster. But then when I ran the 2 scenarios against a much larger of vendors (84 vendors), the different was staggering 6701 ms versus 207 ms, respectively. So the table type approach was roughly 32 times faster. Again, the CURSOR approach is definitely the least attractive approach. Even in SQL Server 2005, it would have been better to create a CSV list or an XML string (providing the number of keys could be stored in a scalar variable). But now that there is a Table Type feature in SQL Server 2008, you can achieve the objective with a feature thats more closely modeled to the way developers are thinking specifically, how do we pass a table to a procedure Now we have an answer Hope you find this feature help. Feel free to post a comment.
No comments:
Post a Comment