Pemecahan masalah kinerja Dynamics CRM 02032015 Jika Anda memiliki Dynamics CRM 2011 2013 2015 pada instalasi premis yang tidak melakukan cara yang Anda (atau pengguna Anda) pikirkan, sebaiknya artikel ini memberi Anda informasi tentang bagaimana mendiagnosis di mana masalahnya berbohong dan beberapa Solusi yang disarankan Beberapa tip dan trik ini berlaku untuk CRM Online juga, jadi baca di cloud customers. Menyatakan hal yang jelas Hal pertama yang perlu Anda pahami adalah apa yang lamban tentang CRM Anda dan bagaimana Anda mengukur bahwa Anda akan perlu mendapatkan pegangan pada aspek kinerja apa yang dilaporkan bermasalah jika Anda akan menyelesaikannya. . Seringkali ada banyak faktor yang menyebabkan kinerja buruk sehingga penting bagi Anda untuk membuat semacam pengukuran awal dari kinerja buruk saat ini, memahami kinerja yang dapat diterima dan kemudian menentukan apa yang menyebabkan perbedaan tersebut. Apakah getaran yang dilaporkan oleh pengguna atau dikenal sebagai persepsi pengguna terhadap kinerja, saat mengakses UI web Apakah ia menemukan data Apakah mengedit dan membuat data Apakah itu panggilan layanan web dari beberapa integrasi kustom Apakah kinerja yang buruk konsisten (mudah direproduksi), Atau intermiten Apakah kinerja menjadi lebih buruk dari waktu ke waktu - Mungkinkah terkait dengan peningkatan jumlah pengguna, atau peningkatan data Alat Diagnostik Berikut adalah beberapa alat yang menurut saya berguna saat memecahkan masalah kinerja dengan instalasi CRM dan seharusnya sangat familiar bagi sebagian besar pengguna. Pengembang dan infralopers layak garam mereka. Fiddler untuk menangkap lalu lintas antara browser dan server SOAP-UI untuk mereproduksi panggilan layanan web bekerja pada layanan web SOAP dan REST CRMs Built-in alat diagnostik untuk mengukur performa web browser UI, dan metrik jaringan antara browser dan server SQL Server Management Studio untuk Memeriksa kinerja SQL Server yang mendasari, mengeksekusi kueri, memeriksa rencana pelaksanaan yang saya suka ilmiah dalam pendekatan saya dan menangkap waktu untuk operasi menggunakan alat yang memungkinkan. Beberapa tes berjalan, menggunakan rata-rata sebagai ukuran (tapi hati-hati keajaiban caching pada secondary run). Masalah rumit persepsi pengguna akhir tentang kinerja sering kali diukur dalam feelpinions. Anda perlu agar pengguna mencoba dan memanfaatkan waktu operasi mereka jika memungkinkan. Kabar baiknya adalah ada alat built-in yang bisa membantu Anda dengan ini. Pemeriksaan Kesehatan Pertama, mintalah Anda menjalankan Microsoft Dynamics CRM 2013 Best Practices Analyzer pada pemasangan Anda Jika menyoroti kesalahan yang harus Anda hadapi, pertanyaan tersebut mungkin tidak berdampak langsung pada kinerja sistem Anda, namun sebaiknya Anda menghilangkannya sebelum Anda melakukannya. Terlalu jauh Juga saat Anda melakukannya, seberapa up to date patch Anda yang saya tahu ada saatnya Anda membiarkan hamstrung dan cant menerapkan patch terbaru karena beberapa alasan mengapa tapi jika Anda tidak menghentikan Anda, pastikan Anda up to date. Jika Anda tidak yakin apa tingkat patch Anda, periksa Nomor Bangun CRM di sini. Antarmuka Web Bahkan sebelum Anda mencoba, apakah Anda menggunakan browser yang didukung Dari perspektif UI Web ada beberapa alat built-in hebat yang dapat membantu Anda mendiagnosis masalah dan mengukur kinerjanya. Diagnostik Browser CRM Jika Anda pergi ke toolsdiagnosticsdiag. aspx titlecrm2015webtoolsdiagnosticsdiag. aspx toolsdiagnosticsdiag. aspx toolsdiagnosticsdiag. aspx Anda akan melihat halaman di bawah ini. Klik tombol Run dan Anda akan mendapatkan hasil dari beberapa tes yang dijalankan dari browser yang mengungkapkan masalah antara browser dan server, atau dengan browser itu sendiri. Perhatikan bahwa URL adalah akar dari situs web CRM, bukan untuk organisasi tertentu. Ini adalah cara yang sangat praktis untuk mendapatkan pengguna akhir untuk menangkap kinerja CRM dari akhir mereka dan mengirimkannya kepada Anda. Juga membantu menjalankan ini pada waktu yang berbeda dalam sehari jika skenario Anda melibatkan kinerja yang berbeda pada waktu yang berbeda dalam sehari. Seperti pada CRM 2013 SP1 dan seterusnya ada alat diagnostik browser baru di kota. Tekan Ctrl Shift Q di IE dan Anda akan melihat yang berikut. Sekarang klik tombol Enable dan muatkan formulir yang ingin Anda analisis. Tekan Ctrl Shift Q lagi dan sekarang Anda sudah mengalami kerusakan penampilan yang sangat baik. Ini berguna untuk membandingkan kinerja dari pengguna akhir browser yang berbeda, dan juga untuk melihat dampak desain bentuk Anda. Dimuat dengan satu miliar sub-grid. Thats a dayung. Form Design III tetaplah ulasan singkat desain formulir ini dengan saksama. Pertimbangkan pengguna akhir Anda dan menyesuaikan tata letak formulir dengan tepat, Anda mungkin tidak perlu memiliki semua bidang sepanjang waktu mereka. Mungkin mendesain bentuk ringan yang digunakan 90 dari waktu dan memungkinkan pengguna untuk beralih ke bentuk detil 10 daun sisa pengguna yang lebih produktif dibandingkan dengan 1 bentuk raksasa yang berusaha menjadi segalanya bagi semua orang. Ingat juga untuk mempertimbangkan peran pengguna akhir dan memisahkan tata letak bentuk yang berbeda dengan cara itu. Ya, ini mengarah pada sedikit pengembangan dan pemeliharaan ekstra, tetapi jika itu mengarah pada bentuk yang lebih berguna bagi pengguna yang berkinerja lebih baik, sistem ini lebih mungkin digunakan daripada bentuk lamban-molase yang dimuat dengan satu miliar sub-grid yang Mungkin berguna. Layanan Web CRM hadir dengan API layanan web hebat yang memungkinkan integrasi oleh sistem lain. Pola yang sering saya lihat melibatkan pengembang untuk menulis serangkaian layanan web yang disederhanakan yang sesuai dengan model data spesifik organisasi, yang bertindak sebagai pembungkus CRM. Ini menyederhanakan integrasi dan mengubah objek CRM menjadi model yang dibutuhkan, namun juga memberikan sedikit abstraksi sehingga Anda dapat meminimalkan gangguan jika Anda mengupgrade instalasi CRM nanti. Kedengarannya mengagumkan, dan Anda bisa mengeluarkan beberapa kode dengan cukup cepat sehingga memberi Anda hasil yang diinginkan dengan menggunakan LINQ. Seperti Peter Parkers Paman Ben mengatakan dengan kekuatan besar datang tanggung jawab besar. Mendapatkan kode-lengkap dengan cepat tidak berarti Anda telah menulis sebuah sistem yang efisien. Dengan asumsi Anda menulis pertanyaan melawan layanan OData: (Update September 2016) Gunakan PFE Core Library untuk menghindari menemukan kembali roda Menguji pertanyaan Anda menggunakan SOAP-UI secara langsung terhadap layanan CRM OData Gunakan Perancang Permintaan OData di Alat Dinamis XRM. Atau menjadi berani dan hanya membuat format URL sendiri Sekarang uji layanan web khusus yang melakukan layanan yang sama ini perbedaannya adalah biaya overhead dari layanan web khusus Anda (yaitu 200ms) Pahami bahwa kueri LINQ Anda dapat menghasilkan beberapa panggilan layanan web OData. Yang terjadi secara berurutan. Yang menambahkan hingga waktu yang hilang. Periksa log IIS dari server CRM untuk melihat jumlah permintaan yang masuk ke layanan web OData Dapatkah Anda melakukan refactor query untuk mengurangi jumlah panggilan Hanya kembalikan atribut dan tautan yang Anda perlukan Teman jangan biarkan teman menulis kueri SELECT, dan juga Anda tidak boleh memuat lebih banyak atribut dalam entitas CRM daripada yang Anda butuhkan Tentukan hanya atribut yang Anda butuhkan dan kemudian jalankan query Additional atribut yang tidak perlu hanya menghasilkan overhead tambahan untuk serialising de-serialising. Bandingkan hasilnya dengan SQL Server Filtered Views coba T-SQL di SQL Server Management Studio yang mendapatkan hasil-set serupa, bagaimana cara melakukan perbandingan? Salah satu pilihan untuk membaca data adalah terhubung ke SQL Server Filtered Views langsung ke jantung buruk rupa. Jangan beralih ke ini tanpa mempertimbangkan implikasi masa depan yang akan dilakukan di dunia CRM Online misalnya, namun jika sebagian besar operasi untuk layanan web Anda dapat dibaca, mungkin perlu diperiksa. Cara mudah untuk mencatat waktu layanan web kustom Anda adalah memastikan waktu yang dibutuhkan masuk ke IIS (dengan asumsi layanan web ASP). Anda kemudian dapat menganalisis ini untuk kueri melebihi target waktu Anda. Pekerjaan Pemeliharaan CRM CRM memiliki sejumlah pekerjaan terkait pemeliharaan built-in yang melakukan hal-hal seperti perhitungan ulang indeks. Secara default ini dimulai sekali sehari kira-kira sekitar waktu pemasangan CRM. Yang biasanya jam kerja. Alat yang sangat baik untuk meninjau jadwal ini dan mengubahnya menjadi sedikit gangguan pada pengguna adalah Editor Tugas CRM dengan edisi untuk CRM 2011 2013 2015. Jaringan dan Infrastruktur Memiliki server Windows yang mendasari diberi jumlah RAM dan CPU Check yang sesuai. Panduan Penerapan 2013 2013 2013 untuk nilai yang disarankan. Apa yang tampaknya menjadi penggunaan memori rata-rata dan penggunaan CPU di perfmon (atau untuk lebih banyak manajer ramah grafik Resource Monitor) Jika Anda memiliki beberapa server CRM yang ditempatkan di belakang penyeimbang beban, apakah Anda dapat menyamping untuk menyeimbangkan beban dan menjelajahi server CRM secara langsung (Dari lingkungan desktop pengguna akhir), dan apakah ini membuat perbedaan pada kinerja Jika ya, periksa konfigurasi NLB untuk masalah apa pun. Apa kinerja UI Web CRM seperti dari salah satu server itu sendiri (via Remote Desktop) Bagaimana cara membandingkannya dengan Windows Event Logs yang berisi kesalahan atau informasi lain yang menunjukkan adanya masalah pada disk, jaringan (konektivitas, DNS), otentikasi Atau hal penting lainnya Apa topologi jaringan Ketika Anda menjelajahi UI Web CRM dan mendapatkan nuansa kinerja sistem Anda sendiri, apakah Anda melakukannya hanya beberapa meter dari pusat data, sementara pengguna yang mengeluhkan kinerja ada di beberapa kantor regional yang terhubung melalui Potongan string yang basah Jika gejala kinerja yang dikeluhkan tampaknya bersifat geo-spesifik, tirulah pengujian dari akhir mereka sebanyak mungkin (lihat alat diagnostik bawaan di bagian Antarmuka Web). Sudahkah Anda mendapat masalah latency antara pengguna akhir dan server CRM Anda CRM bisa sedikit cerewet dan ini bisa menyebabkan Anda sakit karena koneksi dengan latency tinggi (misalnya berpikir London ke Canberra). Di beberapa organisasi, saya melihat kinerja yang lebih baik melalui Citrix karena browser ke server CRM bersifat chattiness terjadi secara lokal di dalam pusat data yang sama. Jarak tempuh Anda akan bervariasi dan demikian pula alat yang Anda inginkan untuk mengatasi hal ini. Pengaturan IIS Periksa kembali apakah Dynamic Compression diaktifkan untuk situs CRM Anda di IIS 8 8.5. Setelah Anda selesai melakukannya, periksa pengaturan outputCache untuk menghilangkan VaryStar sesuai Tip CRM of the Day ini. Ya itu berlaku untuk IIS 8.5 juga, dan bukan hanya masalah CRM yang mempengaruhi semua situs yang dihosting di IIS. Tanpa pengaturan ini, Anda mungkin mendapati bahwa output tidak di-cache oleh peramban dengan benar sehingga menyebabkan seret kinerja dengan mengajukan permintaan tambahan untuk konten pada pemuatan halaman. Pastikan dengan yang satu ini untuk diuji sebelumnya, setelah dengan browser yang menyimpan temboloknya (kemudian memuat beberapa bentuk yang berbeda) untuk mengukur perbedaan kinerja. SQL Server Tentu saja Dinamika kinerja CRM sangat bergantung pada kinerja instalasi SQL Server yang mendasari. Jadi, apakah Anda menjalankan SQL Server Best Practices Analzyer (edisi 2012) Memori dan CPU adalah SQL yang menangis untuk mengetahui salah satu dari lokasi Fisik File Data dan Log ini adalah data dan file log pada disk fisik yang terpisah Max Degree of Parallelism (MAXDOP) dianjurkan agar ini diatur ke 1. Ini mempengaruhi keseluruhan contoh, dan perubahan terjadi segera. Tapak hati-hati sebelum melakukan perubahan ini. Tempdb umumnya direkomendasikan untuk memiliki jumlah file fisik yang sama untuk data tempdb sebagai jumlah CPU pada server. Secara default akan ada 1 file. Pertumbuhan file database memeriksa pengaturan pertumbuhan otomatis dari file database dan pra-tumbuh mereka ke ukuran yang lebih besar jika database Anda tumbuh secara teratur. Hal ini dapat mengurangi jumlah disk grabs yang dibuat SQL saat memperluas basis data. Manajemen Data Views dmvs SQL memiliki beberapa statistik bagus yang terus berhubungan dengan query mahal, penggunaan indeks dan hal-hal terkait tuning lainnya. Dari perspektif CRM, ini bisa membantu mengungkapkan apa pertanyaan paling mahal Anda. Artikel dari Nuno Costa ini melakukan pekerjaan yang jauh lebih baik untuk menjelaskan hal-hal tersebut daripada yang saya bisa, jadi periksalah. Update 18 Okt 2015 Performance Analyzer untuk Microsoft Dynamics (alias DynamicsPerf) adalah toolset yang dikembangkan oleh Microsoft Premier Field Engineering. Ini adalah satu set skrip SQL untuk mengumpulkan data DMV SQL Server dan data produk spesifik Microsoft Dynamics (CRM, AX, GP, NAV, SL) untuk penyelesaian cepat masalah kinerja pada produk Microsoft Dynamics. Jika Anda melakukan banyak kueri yang melibatkan klausa WHERE dengan atribut khusus atau ORDER BY dengan atribut kustom, kemungkinan Anda bisa mendapatkan keuntungan dari indeks pada atribut tersebut terutama jika jumlah record besar. Menambahkan INDEKS ke Tabel SQL adalah satu-satunya hal yang didukung yang dapat Anda ubah di Database SQL. Hal-hal yang perlu Anda perhatikan bagaimana uniknya data Seberapa sering Anda membaca dari situ vs menulis untuk itu (sisipan, update) Karena biayanya akan masuk dalam hitungan perhitungan indeks saat Anda melakukan perubahan. Akhir-akhir ini perhitungan ini terjadi secara online dan tidak memblok tapi masih pajak CPU dan Memory tentunya. Tapi bagaimana Anda tahu atribut mana yang membutuhkan indeks Jalankan query yang mirip dengan yang tampil lambat, langsung di SQL Server Management Studio dan pastikan untuk menyertakan rencana eksekusi. SQL akan memberi tahu Anda biaya komponen kueri dan mengungkapkan apakah indeks akan menguntungkan kueri tersebut. Bagaimana jika Im pelanggan CRM Online Jika Anda memasukkan panggilan dukungan ke Microsoft, Anda dapat meminta mereka menambahkan indeks untuk Anda. Informasi lebih lanjut Im belum menemukan update di luar versi CRM 2011, namun ada banyak pemutihan dan optimalisasi whitepaper dari Microsoft dan banyak prinsip yang sama masih berlaku. Update 2 September 2015 Microsoft merilis beberapa informasi spesifik CRM 2015 tentang Dynamics CRM terukur, periksalah Kesimpulan Ada banyak komponen yang bergerak ke Dynamics CRM. Setiap analisis kinerja akan berbeda karena konteksnya adalah segalanya untuk pemasangan di tempat. Namun saya harap Anda telah menemukan ini gambaran yang sangat membantu mengenai beberapa hal penting yang harus diperhatikan berkaitan dengan optimalisasi kinerja dan pemecahan masalah Dynamics CRM. Tolong aktifkan JavaScript untuk melihat komentar yang diberdayakan oleh Disqus. I perlu menghitung jumlah penggiliran di atas rentang 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. The plan showed large number of reads in this table, though actual duration was pretty much the same, as when I generated numbers on the fly using CTE. This plan is longer, because query uses two window functions ( LEAD and SUM ). An alternative SQLCLR solution that executes faster and requires less memory: That requires the EXTERNALACCESS permission set because it uses a loopback connection to the target server and database instead of the (slow) context connection. This is how to call the function: Produces exactly the same results, in the same order, as the question. Profiler logical reads: 481 The main advantage of this implementation is that it is faster than using the context connection, and it uses less memory. It only keeps two things in memory at any one time: Any duplicate rows (same product and transaction date). This is required because until either the product or date changes, we do not know what the final running sum will be. In the sample data, there is one combination of product and date that has 64 rows. A sliding 45 day range of cost and transaction dates only, for the current product. This is necessary to adjust the simple running sum for rows that leave the 45-day sliding window. This minimal caching should ensure this method scales well certainly better than trying to hold the entire input set in CLR memory. If you are on 64-bit Enterprise, Developer, or Evaluation edition of SQL Server 2014 you can use In-Memory OLTP. The solution will not be a single scan and and will hardly use any window functions at all but it might add some value to this question and the algorithm used could possibly be used as inspiration to other solutions. First you need to enable In-Memory OLTP on AdventureWorks database. The parameter to the procedure is an In-Memory table variable and that has to be defined as a type. ID is not unique in this table, it is unique for each combination of ProductID and TransactionDate . There are some comments in the procedure that tell you what it does but overall it is calculating the running total in a loop and for each iteration it does a lookup for the running total as it was 45 days ago (or more). The current running total minus the running total as it was 45 days ago is the rolling 45 days sum we are looking for. Invoke the procedure like this. Testing this on my computer Client Statistics reports a Total execution time of about 750 millisecond. For comparisons the sub-query version takes 3.5 seconds. This algorithm could also be used by regular T-SQL. Calculate the running total, using range not rows, and store the result in a temp table. Then you can query that table with a self join to to the running total as it was 45 days ago and calculate the rolling sum. However, the implementation of range compared to rows is quite slow due to the fact that is needs to treat duplicates of the order by clause differently so I did not get all that good performance with this approach. A workaround to that could be to use another window function like lastvalue() over a calculated running total using rows to simulate a range running total. Another way is to use max() over(). Both had some issues. Finding the appropriate index to use to avoid sorts and avoiding spools with the max() over() version. I gave up optimising those things but if you are interested in the code I have so far please let me know. answered Sep 15 15 at 12:38 Well that was fun :) My solution is a bit slower than GeoffPattersons but part of that is the fact that Im tying back to the original table in order to eliminate one of Geoffs assumptions (i. e. one row per productdate pair). I went with the assumption this was a simplified version of a final query and may require additional information out of the original table. Note: Im borrowing Geoffs calendar table and in fact ended up with a very similar solution: Here is the query itself: Basically I decided that the easiest way to deal with it was to use the option for the ROWS clause. But that required that I only have one row per ProductID. TransactionDate combination and not just that, but I had to have one row per ProductID and possible date. I did that combining the Product, calendar and TransactionHistory tables in a CTE. Then I had to create another CTE to generate the rolling information. I had to do this because if I joined it back the the original table directly I got row elimination that threw off my results. After that it was a simple matter of joining my second CTE back to the original table. I did add the TBE column (to be eliminated) to get rid of the blank rows created in the CTEs. Also I used a CROSS APPLY in the initial CTE to generate boundaries for my calendar table. I then added the recommended index: And got the final execution plan: EDIT: In the end I added an index on the calendar table that sped up performance by a reasonable margin. answered Sep 10 15 at 16:34 I have a few alternate solutions that dont use indexes or reference tables. Perhaps they could be useful in situations in which you dont have access to any additional tables and cannot create indexes. It does appear to be possible to get correct results when grouping by TransactionDate with just a single pass of the data and just a single window function. However, I could not figure out a way to do it with just one window function when you cannot group by TransactionDate . To provide a frame of reference, on my machine the original solution posted in the question has a CPU time of 2808 ms without the covering index and 1950 ms with the covering index. I am testing with the AdventureWorks2014 database and SQL Server Express 2014. Lets start with a solution for when we can group by TransactionDate. A running sum over the last X days can also be expressed in the following way: Running sum for a row running sum of all previous rows - running sum of all previous rows for which the date is outside the date window. In SQL, one way to express this is by making two copies of your data and for the second copy, multiplying the cost by -1 and adding X1 days to the date column. Computing a running sum over all of the data will implement the above formula. Ill show this for some example data. Below is some sample date for a single ProductID. I represent dates as numbers to make the calculations easier. Starting data: Add in a second copy of the data. The second copy has 46 days added to the date and the cost multiplied by -1: Take the running sum ordered by Date ascending and CopiedRow descending: Filter out the copied rows to get the desired result: The following SQL is one way to implement the above algorithm: On my machine this took 702 ms of CPU time with the covering index and 734 ms of CPU time without the index. The query plan can be found here: brentozarpastetheplanidSJdCsGVSl One downside of this solution is that there appears to be an unavoidable sort when ordering by the new TransactionDate column. I dont think that this sort can be resolved by adding indexes because we need to combine two copies of the data before doing the ordering. I was able to get rid of a sort at the end of the query by adding in a different column to ORDER BY. If I ordered by FilterFlag I found that SQL Server would optimize out that column from the sort and would perform an explicit sort. Solutions for when we need to return a result set with duplicate TransactionDate values for the same ProductId were much more complicated. I would summarize the problem as simultaneously needing to partition by and order by the same column. The syntax that Paul provided resolves that issue so its not surprisingly that its so difficult to express with the current window functions available in SQL Server (if it wasnt difficult to express there would be no need to expand the syntax). If I use the above query without grouping then I get different values for the rolling sum when there are multiple rows with the same ProductId and TransactionDate. One way to resolve this is to do the same running sum calculation as above but also to flag the last row in the partition. This can be done with LEAD (assuming ProductID is never NULL) without an additional sort. For the final running sum value, I use MAX as a window function to apply the value in the last row of the partition to all rows in the partition. On my machine this took 2464ms of CPU time without the covering index. As before there appears to be an unavoidable sort. The query plan can be found here: brentozarpastetheplanidHyWxhGVBl I think that there is room for improvement in the above query. There are certainly other ways to use windows functions to get the desired result. Window Functions (OVER Clause)Help Make a Difference If I had to name one concept in standard SQL that I thought was the most important one, and that is worth Microsoftrsquos investment for future versions of SQL Server, Irsquod say window functions, hands down, without a doubt. Window functions are a subset of what the standard calls set functions, meaning, functions that are applied to a set of rows. The term window is used to describe the set of rows that the function operates on, and the language provides a clause called OVER where you provide the window specification. So whatrsquos the big deal, and what makes window functions more important than other features that are missing in SQL Server There are so many reasonshellip But first Irsquoll give a bit more background about window functions, and then Irsquoll get to the reasons and demonstrate use caseshellip First, to clarify, SQL Server 2005 already introduced some support for window functionsmdashthe ranking calculations: ROWNUMBER, RANK, DENSERANK and NTILE, and partial support for window aggregate functions with only the partitioning part implemented. SQL Server 2005 was a great release for developers with so many cool and practical T-SQL features. The number of solutions that I simplified and optimized just with the ROWNUMBER function and CTEs is amazing. Still, there are many standard features related to window functions that SQL Server didnrsquot yet implement (as of SQL Server 2008 R2) and that can help address quite a wide variety of business problems with simpler and more efficient solutions. These days the next major release of Microsoft SQL Servermdashversion 11mdashis being developed. These are pivotal days for candidate features where decisions are made whether they will or will not make it to the final release. And even though I think that more complete support for window functions is so important to developers and to the success of SQL Server, Irsquom not sure at all that we will see those in the product. This is time for us as part of the SQL Server community to express our strong opinion. Hopefully Microsoft will realize how important it is for us to have those features in the product, as well as to show that the SQL Server communityrsquos opinion matters. In this article I will explain some of the key features that are missing in SQL Server and why itrsquos important to add support for such features. If you share my opinion, and havenrsquot done so already, you can cast your vote in the following feature request items: Like with any thing in life that yoursquore not aware of, you donrsquot know how it can help you if you donrsquot know that it exists. My feeling is that many developers are not really aware of the capabilities of the standard window functions and therefore Microsoft doesnrsquot see a lot of demand for it. Education and raising the topic to peoplersquos awareness is therefore key to the realization of the benefits, and as a consequence, encourage people to ask Microsoft for more support. The unfortunate part is that all of SQL Serverrsquos leading competitors including Oracle, DB2 and Teradata for some time now already have a far more complete support for window functions. So even though my focus and passion is for SQL Server, I sometimes find myself in the awkward situation of demoing standard SQL window functions on Oracle when teaching or presenting. So whatrsquos missinghellip The most important missing features are probably ordering and framing options for window aggregate functions. Other key features that are still missing are distribution and offset functions, and reusability of window definitions. More details shortly. Why are window functions so powerful SQL is often referred to as a set-based language. The reason is that the language is based on the relational model, which in turn is based, in part, on mathematical set theory. When writing SQL queries yoursquore supposed to deal with a table (or relation, which is a set) as a whole, as opposed to the tablersquos individual rows. Also, since sets have no order, yoursquore not supposed to make any assumptions in regards to the physical ordering of the data. The reality is that for many developers set-based thinking is far from being intuitive, and it can take a few good years to truly think in SQL terms. This is why often developers tend to use cursorsmdashbecause using those feel like an extension to what they already know. Cursors allow you to deal with one row at a time, and also rely on specified order of the data. Window functions have an ingenious design. They do operate on sets, or windows, while allowing you to indicate ordering as part of the calculation where relevant. Not to confuse with cursors, window functions allow defining ordering for the calculation without making any expectations in regards to ordering of the input data given to the query or the output coming out of the query. In other words, no relational concepts are violated. Ordering is only part of the specification of the calculation. Similarly, other common elements in querying problems, like partitioning, framing of applicable rows, are all intuitive parts of the window specification. So in a sense, I see window functions as bridging the big gap that exists between cursoriterative and set-based thinking. Now, thatrsquos a lot of words before showing even one example. So letrsquos look at a few more concrete examples of some of the missing featureshellip Most of the examples Irsquoll show are against a database called InsideTSQL2008. You can find the script creating it here: InsideTSQLbookssourcecodeInsideTSQL2008.zip. In addition, the following view will be used in some of the examples: SET NOCOUNT ON USE InsideTSQL2008 GO IF OBJECTID ( 39Sales. EmpOrders39. 39V39 ) IS NOT NULL DROP VIEW Sales. EmpOrders GO CREATE VIEW Sales. EmpOrders WITH SCHEMABINDING AS SELECT O. empid , DATEADD ( month. DATEDIFF ( month. 0. O. orderdate ), 0 ) AS ordermonth. SUM (OD. qty ) AS qty , CAST ( SUM (OD. qty OD. unitprice (1 - discount )) AS NUMERIC (12. 2 )) AS val , COUNT () AS numorders FROM Sales. Orders AS O JOIN Sales. OrderDetails AS OD ON OD. orderid O. orderid GROUP BY empid. DATEADD ( month. DATEDIFF ( month. 0. O. orderdate ), 0 ) GO Ordering and Framing for Window Aggregate Functions As mentioned, currently window aggregate functions support only a partitioning element. Whatrsquos missing are ordering and framing options. The standard supports an ORDER BY clause to define ordering in the window and ROWS and RANGE clauses that frame the window based on the defined ordering. A classic example that would benefit from ordering and framing is running totals. Consider the following Accounts table definition: CREATE TABLE dbo. Accounts ( actid INT NOT NULL, -- partitioning column tranid INT NOT NULL, -- ordering column val MONEY NOT NULL -- measure CONSTRAINT PKAccounts PRIMARY KEY (actid. tranid ) ) The table represents deposit (positive value) and withdrawal (negative value) transactions in bank accounts. You need to calculate at each point what the account balance was. Like with many querying problems therersquos a partitioning element (actid), ordering element (tranid), and a measure that the calculation applies to (val). Window aggregate functions in standard SQL support all three elements. Herersquos how you would express the query calculating the balance at each point for each account: SELECT actid. tranid. val , SUM (val ) OVER ( PARTITION BY actid ORDER BY tranid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS balance FROM dbo. Accounts You can achieve such calculations today in SQL Server using a subquery or a join: -- Set-Based Solution Using Subqueries SELECT actid. tranid. val , ( SELECT SUM (S2.val ) FROM dbo. Accounts AS S2 WHERE S2.actid S1.actid AND S2.tranid lt S1.tranid ) AS balance FROM dbo. Accounts AS S1 -- Set-Based Solution Using Joins SELECT S1.actid. S1.tranid. S1.val , SUM (S2.val ) AS balance FROM dbo. Accounts AS S1 JOIN dbo. Accounts AS S2 ON S2.actid S1.actid AND S2.tranid lt S1.tranid GROUP BY S1.actid. S1.tranid. S1.val But besides the fact that these solutions are not as straightforward and intuitive as the one using a window function, therersquos a big problem with the way SQL Server currently optimizes the subquery and join solutions. Assuming you defined a covering index on the partitioning column, followed by the ordering column, and including the aggregated measure, for each row SQL Server will scan all rows with the same partitioning value and an ordering value that is less than or equal to the current. Given p partitions with r rows in average, and fairly even distribution of rows in partitions, the total number of rows processed in such a plan is pr p(r r2)2. This means that in respect to the partition size, the algorithmic complexity, or scaling, of the solution s quadratic (N2). Thatrsquos bad. The window function form lends itself to good optimization, especially with the fast track case like the above (rows between unbounded preceding and current row). It should be straightforward to the optimizer to optimize this query with one ordered scan of the index, translating to simply pr rows being scanned. Another example for running totals is querying a table called EmpOrders with a row for each employee and month, and calculating the cumulative performance for each employee and month in other words, the total value for the employee from the beginning of hisher activity until the current month. Herersquos how you would express it with a window aggregate: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runqty FROM Sales. EmpOrders empid ordermonth qty runqty ----------- ----------------------- ----------- ----------- 1 2006-07-01 00:00:00.000 121 121 1 2006-08-01 00:00:00.000 247 368 1 2006-09-01 00:00:00.000 255 623 1 2006-10-01 00:00:00.000 143 766 1 2006-11-01 00:00:00.000 318 1084 . 2 2006-07-01 00:00:00.000 50 50 2 2006-08-01 00:00:00.000 94 144 2 2006-09-01 00:00:00.000 137 281 2 2006-10-01 00:00:00.000 248 529 2 2006-11-01 00:00:00.000 237 766 . There are many business examples where ordering and framing options can be useful besides calculating account balances. Those include inventory, running totals for reporting, moving averages, and so on. Herersquos an example for a query calculating the average of the last three recorded periods: SELECT empid. ordermonth , AVG (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avglastthree FROM Sales. EmpOrders There are also various temporal querying problems where running totals serve part of the solution. For simplicity I showed examples where framing is based on the ROWS clause where you indicate an offset in terms of number of rows. The standard also supports a RANGE clause that allows indicating an offset in terms of values, such as time intervals, as in the following example returning the average of the last three months: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth RANGE INTERVAL 39239 MONTH PRECEDING ) AS sum3mqty FROM Sales. EmpOrders ORDER BY empid. ordermonth The SQL standard defines several offset functions that would make developersrsquo life so much easier compared to the tools available today for similar needs. Among the missing offset functions are LAG and LEAD, returning a value from a row in a given offset from the current row based on specified ordering. For example, the following query will return, for each current order, also the order date of the previous and next orders: SELECT custid. orderdate. orderid , LAG (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS prvod , LEAD (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS nxtod FROM Sales. Orders custid orderdate orderid prvod nxtod ------- ----------- -------- ----------- ----------- 1 2007-08-25 10643 NULL 2007-10-03 1 2007-10-03 10692 2007-08-25 2007-10-13 1 2007-10-13 10702 2007-10-03 2008-01-15 1 2008-01-15 10835 2007-10-13 2008-03-16 1 2008-03-16 10952 2008-01-15 2008-04-09 1 2008-04-09 11011 2008-03-16 NULL 2 2006-09-18 10308 NULL 2007-08-08 2 2007-08-08 10625 2006-09-18 2007-11-28 2 2007-11-28 10759 2007-08-08 2008-03-04 2 2008-03-04 10926 2007-11-28 NULL . Notice how elegant and intuitive this form is. The default offset is one row, but you can also be explicit if you need an offset that is other than one row, e. g. three rows: SELECT custid. orderdate. orderid , LAG (orderdate. 3 ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS prv3od FROM Sales. Orders There are lots of business examples for the usefulness of these functions, like recency calculations, trend analysis, and others. Herersquos an example for a query addressing recency calculations, returning the difference in terms of days between the current and previous orders: SELECT custid. orderdate. orderid , DATEDIFF ( day , LAG (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ), orderdate ) AS diff FROM Sales. Orders Other missing offset functions are FIRSTVALUE, LASTVALUE, returning the value from the first or last rows in the partition based on specified ordering. Herersquos an example returning the value of the first and last orders per customer with each order: -- FIRSTVALUE, LASTVALUE SELECT custid. orderdate. orderid. val , FIRSTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS valfirstorder , LASTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. ordered ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS vallastorder FROM Sales. OrderValues custid orderdate orderid val valfirstorder vallastorder ------- ----------- -------- ------- --------------- -------------- 1 2007-08-25 10643 814.50 814.50 933.50 1 2007-10-03 10692 878.00 814.50 933.50 1 2007-10-13 10702 330.00 814.50 933.50 1 2008-01-15 10835 845.80 814.50 933.50 1 2008-03-16 10952 471.20 814.50 933.50 1 2008-04-09 11011 933.50 814.50 933.50 2 2006-09-18 10308 88.80 88.80 514.40 . And herersquos an example calculating the difference between the current order value and the first and last: SELECT custid. orderdate. orderid. val , val - FIRSTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS difffirst , val - LASTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. ordered ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS difflast FROM Sales. OrderValues Standard SQL supports window distribution functions that performing statistical calculations. Specifically it supports the PERCENTRANK and CUMDIST functions, calculating a percentile rank and cumulative distribution. These functions give you a relative rank of a row in respect to other rows in the window partition, expressed as ratiopercent. The specific formulas used by the two variants are: PERCENTRANK: (RK-1)(NR-1), where RK rank, NR number of rows in partition CUMEDIST: NPNR, where NP number of rows preceding or peer with current row (same as next rank - 1) Herersquos an example using these functions: SELECT custid. COUNT () AS numorders , PERCENTRANK () OVER ( ORDER BY COUNT ()) AS percentrank , CUMEDIST () OVER ( ORDER BY COUNT ()) AS cumedist FROM Sales. Orders GROUP BY custid custid numorders percentrank cumedist ------- ---------- ------------ --------- 13 1 0.0000 0.0112 33 2 0.0114 0.0337 43 2 0.0114 0.0337 42 3 0.0341 0.1124 53 3 0.0341 0.1124 . 37 19 0.9545 0.9663 24 19 0.9545 0.9663 63 28 0.9773 0.9775 20 30 0.9886 0.9888 71 31 1.0000 1.0000 Reuse of Window Definition using WINDOW Clause Suppose you need to write several window functions that rely on the same window definition (or part of it). You will end up with a lot of repetition of code. Standard SQL has a clause called WINDOW that allows naming a window definition or part of it, making it reusable. For example, instead of: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runsumqty , AVG (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runavgqty , FROM Sales. EmpOrders SELECT empid. ordermonth. qty , SUM (qty ) OVER W1 AS runsumqty , AVG (qty ) OVER W1 AS runavgqty , FROM Sales. EmpOrders WINDOW W1 AS ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) As you can see, with the WINDOW clause the code is shorter, more readable, and easier to maintain. I showed just part of the standard support for window functions that SQL Server is still missing. Therersquos more, like window frame exclusion. There are also other set functions not implemented, like ordered set functions, and so on. But here I wanted to make a point in hope that Microsoft will realize how important it is to add such support in SQL Server 11. If you feel so as well, help make a difference by voting for the items, write about the topic, talk about it, increasing peoplersquos awareness. Hopefully this request will find open ears. As a reminder, here are the open items for some of the requests for enhancements:
No comments:
Post a Comment