Dynamics CRM-feilsøking 02032015 Hvis du har en Dynamics CRM 2011 2013 2015 på forhåndsinstallasjon som ikke utfører måten du (eller brukerne dine) tror det skulle da, gir denne artikkelen deg informasjon om hvordan du skal diagnostisere hvor problemet (e) ligger og noen foreslåtte løsninger. Noen av disse tipsene og triksene gjelder også CRM Online, så les videre på sky-kunder. Angi det åpenbare Det første du må forstå er hva som er sakte om CRM og hvordan har du målt at du skal trenge et håndtak på hvilke aspekter av ytelse som rapporteres som problematisk hvis du skal løse dem . Ofte er det en rekke faktorer som fører til dårlig ytelse, så det er viktig at du etablerer en slags baseline-måling av den nåværende dårlige ytelsen, forstår den akseptable ytelsen og deretter utarbeider det som forårsaker forskjellen. Er det vibrasjonen som rapporteres av brukerne ellers kjent som brukerens oppfatning av ytelse, når du får tilgang til webgrensesnittet. Er det å finne data. Er det redigering og oppretting av data. Er det webservicesamtaler fra noen tilpasset integrasjon. Er den dårlige ytelsen konsekvent (lett gjengitt) eller intermitterende Har ytelsen blitt verre over tid - Kan det være relatert til økt antall brukere eller økte data Diagnostiske verktøy Følgende er noen av verktøyene som jeg synes er nyttige når du feilsøker ytelsesproblemer med en CRM-installasjon, og de bør være kjent for de fleste utviklere og infralopere verdt deres salt. Fiddler for å fange trafikk mellom nettleseren og serveren SOAP-UI for gjengivelse av webservicesamtaler, fungerer både på SOAP og REST-webtjenester. CRMer Innebygde diagnostiske verktøy for måling av webleserens nettbrukerytelse og nettverksmålinger mellom nettleser og server SQL Server Management Studio for undersøke underliggende SQL Server-ytelse, utføre spørringer, sjekke utførelsesplaner Jeg liker å være vitenskapelig i min tilnærming og fange timing for operasjoner som bruker verktøy der det er mulig. Flere testkjøringer, bruker gjennomsnittet som mål (men pass opp magien til caching på sekundære løp). Det vanskelige problemet med sluttbrukernes oppfatning av ytelse, måles ofte i feelpinions. Du må få brukerne til å prøve og teste driften der det er mulig. Den gode nyheten er at det er innebygde verktøy som kan hjelpe deg med dette. Helsekontroll Først har du kjørt Microsoft Dynamics CRM 2013 Best Practices Analyzer på installasjonen din Hvis det fremheves noen feil, bør du adressere de som ikke har noen direkte innvirkning på ytelsen til systemet ditt, men du er best for å eliminere disse før du bli for langt. Også mens du er i det, hvor oppdatert er patchene dine, jeg vet at det er tider når du har hamstrung og ikke kan bruke den nyeste oppdateringen på grunn av noen grunner, men hvis det ikke er noe som stopper deg, sørg for at du er oppdatert. Hvis du ikke er sikker på hvilket oppdateringsnivå du er på, kan du sjekke CRM Build Numbers her. Webgrensesnitt Før du selv prøver, bruker du en støttet nettleser Fra webgrensesnittet er det noen flotte innebygde verktøy som kan hjelpe deg med å diagnostisere problemer og måle ytelsen. CRM Browser Diagnostics Hvis du går til toolsdiagnosticsdiag. aspx titlecrm2015webtoolsdiagnosticsdiag. aspx verktøydiagnosticsdiag. aspx toolsdiagnosticsdiag. aspx vil du se siden nedenfor. Klikk på Run-knappen, og du får resultatene av noen tester som er utført fra nettleseren, som viser eventuelle problemer mellom nettleseren og serveren, eller med selve nettleseren. Legg merke til at nettadressen er for roten til CRM-nettstedet, ikke for en bestemt organisasjon. Dette er en veldig praktisk måte å få sluttbrukere til å fange oppførelsen til CRM fra slutten og sende den inn til deg. Hjelper også med å kjøre dette over forskjellige tider på dagen hvis scenariet innebærer forskjellig ytelse på forskjellige tider av dagen. Fra og med CRM 2013 SP1 og fremover er det et nytt nettleser-diagnostisk verktøy i byen. Hit Ctrl Shift Q i IE, og du vil se følgende. Klikk nå på Aktiver-knappen og legg inn et skjema du prøver å analysere. Hit Ctrl Shift Q igjen og nå har du en utmerket sammenbrudd av formytelsen. Dette kan være nyttig for å sammenligne ytelsen fra ulike nettlesere, og for å se hvilken innvirkning skjemaet ditt har. Lastet opp med en milliard delgitter Det er en padling. Form Design Ill Hold denne korte anmeldelsen din form design nøye. Overvei sluttbrukerne og skreddersy skjemaoppsettet på riktig måte, du trenger kanskje ikke å ha alle feltene hele tiden. Kanskje å designe en lett form som brukes 90 av tiden og la brukeren bytte til detaljert skjema, de resterende 10 gir brukerne mer produktive enn 1 gigantisk form som prøver å være alt for alle. Husk også å vurdere sluttbrukerroller og segregere ulike skjemaoppsett på den måten. Ja det fører til litt ekstra utvikling og vedlikehold, men hvis det fører til en mer nyttig form for brukere som utfører bedre, er systemet mer sannsynlig å bli brukt enn en langsom-som-melassform fylt med et milliard delnett som kan komme til nytte. Web Services CRM leveres med en flott webtjenester API som tillater integrering av andre systemer. Et mønster Ive sett involverer ofte å få utviklere til å skrive et forenklet sett av webtjenester som samsvarer med organisasjonsspesifikke datamodeller, som fungerer som en wrapper til CRM. Dette forenkler integrasjon og forvandler CRM-objekter til de nødvendige modellene, det gir også litt abstraksjon, slik at du kan minimere forstyrrelser hvis du oppgraderer CRM-installasjonen senere. Høres kjempebra, og du kan bash out noen kode ganske jævla raskt som gir deg de ønskede resultatene ved hjelp av LINQ. Som Peter Parkers onkel Ben sa med stor makt kommer stort ansvar. Å få kode-ferdig raskt betyr ikke at du har skrevet et effektivt system. Forutsatt at du skriver spørringer mot OData-tjenesten: (Oppdater September 2016) Bruk PFE-kjernebiblioteket for å unngå å gjenoppfinne hjulet. Test dine spørsmål ved hjelp av SOAP-UI direkte mot CRM OData-tjenesten. Bruk ODAT Query Designer i Dynamics XRM Tools. eller vær modig og bare trene URL-formatet ditt nå. Test nå den egendefinerte webtjenesten som utfører samme tjeneste. Forskjellen er overhead av din egendefinerte webtjeneste (dvs. 200ms). Forstå at LINQ-spørringen din kan føre til flere OData-webtjenesteanrop. Som skjer i rekkefølge. Som legger opp til tapt tid. Kontroller IIS-loggene på CRM-serveren for å se hvor mange forespørsler som kommer inn i OData-webtjenesten. Kan du reflektere spørringen for å redusere antall samtaler. Returner bare de attributter og linker du trenger. Venner, ikke la venner skrive SELECT-spørringer og lignende du bør ikke laste flere attributter i CRM-enhetene enn du trenger Angi bare de attributter du trenger og utfør søket. Ytterligere unødvendige attributter resulterer bare i ekstra overhead for serialising de-serialising. Sammenlign resultatene med SQL Server-filtrerte visninger prøv T-SQL i SQL Server Management Studio som får lignende resultatsett. Hvordan gjør det ved sammenligning Ett alternativ for å lese data er å koble til SQL Server-filtrerte visninger gå rett til hjertet av beistet. Ikke hopp inn i dette uten å vurdere fremtidige implikasjoner som det ikke vil fungere i en CRM Online-verden for eksempel, men hvis hoveddelen av operasjonene for webtjenestene dine er leseorienterte, kan det være verdt å sjekke ut. En praktisk måte å logge på timingen til dine egendefinerte webtjenester er å sikre at tidsprosessen er logget inn i IIS (forutsatt ASP-webtjenester). Du kan deretter analysere dette for spørsmål som overskrider måltider. CRM Maintenance Jobs CRM har en rekke innebygde vedlikeholdsrelaterte jobber som utfører ting som omregning av indekser. Som standard slår disse av en gang om dagen omtrent rundt den tiden CRM ble installert. Som er vanligvis åpningstider. Et utmerket verktøy for å gjennomgå disse tidsplanene og endre dem til en tid med mindre avbrudd for brukeren, er CRM Job Editor med utgaver for CRM 2011 2013 2015. Nettverk og infrastruktur Har de underliggende Windows-serverne blitt gitt riktig mengde RAM - og CPU-kontroll Implementeringsveiledningene for 2011 2013 for de anbefalte verdiene. Hva synes å være gjennomsnittlig minnebruk og CPU-bruk i perfmon (eller for mer ledervennlig grafikk Resource Monitor) Hvis du har flere CRM-servere distribuert bak en belastningsbalanserer, kan du sette trinnets balanser og sidde direkte på CRM-serveren (fra sluttbrukerens skrivebordsmiljø), og gjør det noe for ytelsen Hvis det gjør det, sjekk ut NLB-konfigurasjonen for eventuelle problemer. Hva ser ytelsen til CRM-webgrensesnittet ut av en av serverne selv (via eksternt skrivebord) Hvordan sammenligner det Er Windows-hendelsesloggene fulle av feil eller annen informasjon som indikerer problemer med disk, nettverk (tilkobling, DNS), autentisering eller andre kritiske ting Hva er nettverkstopologien Når du surfer på CRM Web-brukergrensesnittet og får din egen følelse av systemytelse, gjør du det bare meter unna datasenteret, mens brukerne klager på ytelse er i enkelte regionale kontorer koblet over en våt stykke streng Hvis ytelsessymptomene blir klaget over, synes å være geospesifikke, må du prøve å teste fra slutten så mye som mulig (se de innebygde diagnostiske verktøyene i Webgrensesnittet). Har du latensproblemer mellom sluttbrukere og CRM-serverne dine CRM kan være litt chatty, og dette kan føre til smerte over en forbindelse med høy ventetid (for eksempel tenk London til Canberra). I noen organisasjoner har jeg sett bedre ytelse gjennom Citrix fordi nettleseren til CRM-serverens chattiness skjedde lokalt innenfor samme datasenter. Kilometerstanden din vil variere, og det vil også være verktøyene du har til rådighet for å takle dette. IIS-innstillinger Dobbeltklikk at dynamisk komprimering er aktivert for CRM-nettstedet ditt i IIS 8 8.5. Etter at du har gjort det, må du sjekke outputCache-innstillingen for omitVaryStar som i denne CRM-tipset på dagen. Ja det gjelder også IIS 8.5, og det er ikke bare et CRM problem, det påvirker alle nettsteder som er vert i IIS. Uten denne innstillingen kan du oppdage at utgangen ikke blir cached av nettleseren riktig, noe som forårsaker en ytelsesdrap ved å gjøre ytterligere forespørsler om innhold ved sidebelastning. Vær sikker på at denne skal testes før etter med nettlesere som har fått cachen deres tømt (så legg inn noen forskjellige former) for å måle ytelsesforskjellen. SQL Server Selvfølgelig er Dynamics CRM-ytelse sterkt avhengig av ytelsen til den underliggende SQL Server-installasjonen. Så har du kjørt SQL Server Best Practices Analzyer (2012-utgaven) Minne og CPU er SQL gråt ut for noen av disse Fysisk plassering av data og loggfiler er data og loggfiler på separate fysiske disker Max grad av parallellisme (MAXDOP) anbefales det at dette er satt til 1. Det påvirker hele forekomsten, og endringer skjer umiddelbart. Tread forsiktig før du gjør denne endringen. tempdb det anbefales vanligvis å ha samme antall fysiske filer for tempdb-data som antall CPUer på serveren. Som standard vil det være 1 fil. Vekst av databasefiler kontrollerer innstillingene for automatisk vekst av databasefilene og pre-vokser dem til en større størrelse hvis databasen din vokser regelmessig. Dette kan redusere antall harddisker som SQL gjør når det utvider databasene. Data Management Views dmvs SQL har noen gode statistikker som det holder med om dyre spørringer, indeksbruk og andre tuningrelaterte ting. Fra et CRM-perspektiv kan disse bidra til å avdekke hva dine mest kostbare spørsmål er. Denne artikkelen fra Nuno Costa gjør en mye bedre jobb med å forklare disse enn jeg kan, så sjekk det ut. Oppdater 18 okt 2015. Performance Analyzer for Microsoft Dynamics (aka DynamicsPerf) er et verktøysett utviklet av Microsoft Premier Field Engineering. Det er et sett med SQL-skript for å samle SQL Server DMV-data og Microsoft Dynamics (CRM, AX, GP, NAV, SL) spesifikke produktdata for rask oppløsning av ytelsesproblemer på Microsoft Dynamics-produkter. Hvis du gjør mange spørsmål som involverer WHERE klausuler med egendefinerte attributter eller ORDER BYs med egendefinerte attributter, er det mulig at du kan dra nytte av en indeks på disse attributter, spesielt hvis antall poster er store. Å legge til en INDEX i SQL-tabellen er den eneste støttede tingen du kan endre i SQL-databasen. Ting du trenger å vurdere hvor unike er dataene Hvor ofte leser du fra det mot å skrive til det (innlegg, oppdateringer) Fordi kostnadene vil komme i form av indeksberegning når du gjør endringer. I disse dager skjer denne beregningen online og ikke blokkere, men det er fortsatt skatt CPU og Memory selvfølgelig. Men hvordan vil du vite hvilke attributter som trenger indekser Kjør spørringer som ligner de som utfører sakte, direkte i SQL Server Management Studio og sørg for å inkludere utførelsesplanen. SQL vil fortelle deg kostnaden for spørrekomponenter og avsløre om en indeks vil være til nytte for spørringen. Hva om jeg er en CRM Online-kunde Hvis du legger et supportanrop til Microsoft, kan du få dem til å legge til indeksen for deg. Mer informasjon Im ennå å komme over en oppdatering utover CRM 2011 versjonen, men det er en omfattende ytelses - og optimaliserings whitepaper fra Microsoft, og mange av de samme prinsippene gjelder fortsatt. Oppdatering 2. september 2015 Microsoft utgitt noen spesifikke CRM 2015-informasjon om skalerbar Dynamics CRM, sjekk den ut Konklusjon Det er mange bevegelige deler til Dynamics CRM. Hver ytelsesanalyse vil være annerledes som kontekst er alt for en på-premise-installasjon. Men jeg håper du har funnet dette et nyttig oversikt over noen viktige ting å være klar over med hensyn til optimalisering og feilsøking av Dynamics CRM. Aktiver JavaScript for å se kommentarene drevet av Disqus. Jeg må beregne en rullende sum over en datoperiode. For å illustrere, bruk AdventureWorks-prøvedatabasen. Den følgende hypotetiske syntaksen ville gjøre akkurat det jeg trengte: Dessverre tillater ikke RANGE-vinduets rammeutvidelse et intervall i SQL Server. Jeg vet at jeg kan skrive en løsning ved hjelp av en subquery og et vanlig (ikke-vindu) aggregat: Gitt følgende indeks: Utførelsesplanen er: Selv om det ikke er forferdelig ineffektiv, ser det ut til at det burde være mulig å uttrykke dette spørsmålet ved hjelp av bare vindusaggregat og analytiske funksjoner støttes i SQL Server 2012, 2014 eller 2016 (hittil). For klarhet er jeg på utkikk etter en løsning som utfører et enkelt pass over dataene. I T-SQL vil dette sannsynligvis bety at OVER-klausulen vil utføre arbeidet, og gjennomføringsplanen vil inneholde vindusspoler og vindusaggregater. Alle språkelementer som bruker OVER-klausulen, er rettferdig spill. En SQLCLR-løsning er akseptabel, forutsatt at det er garantert å produsere riktige resultater. For T-SQL-løsninger, jo færre Hashes, Sorts og Window SpoolsAggregates i utførelsesplanen, jo bedre. Du er velkommen til å legge til indekser, men separate strukturer er ikke tillatt (slik at ingen forhåndsberegnede tabeller holdes synkronisert med utløsere, for eksempel). Referansetabeller er tillatt (tabeller med tall, datoer osv.) Ideelt sett vil løsninger produsere nøyaktig de samme resultatene i samme rekkefølge som underprosessversjonen ovenfor, men alt muligens riktig er også akseptabelt. Ytelse er alltid en overveielse, så løsninger bør være minst rimelig effektive. Dedikert chatrom: Jeg har opprettet et offentlig chatrom for diskusjoner relatert til dette spørsmålet og dets svar. Enhver bruker med minst 20 omdømme poeng kan delta direkte. Vennligst ping meg i en kommentar nedenfor hvis du har mindre enn 20 rep og vil gjerne delta. spurte Sep 7 15 kl 20:13 Flott spørsmål, Paul Jeg brukte et par forskjellige tilnærminger, en i T-SQL og en i CLR. T-SQL-tilnærmingen kan oppsummeres som følgende trinn: Ta kryssproduktet av productdates Slå sammen i de observerte salgsdataene Samle disse dataene til productdate-nivået Beregn rullende summer i løpet av de siste 45 dagene basert på disse aggregerte dataene (som inneholder noen manglende dager fylt inn) Filtrer disse resultatene til bare productdate pairings som hadde ett eller flere salg. Bruk SET STATISTICS IO ON. Denne tilnærmingen rapporterer Table TransactionHistory. Scan teller 1, logisk leser 484. Som bekrefter enkeltpasset over bordet. Som referanse rapporterer den opprinnelige sløyfesøk-spørringen Table TransactionHistory. Scan teller 113444, logisk leser 438366. Som rapportert av SET STATISTIK TID PÅ. CPU-tiden er 514ms. Dette sammenlikner gunstig til 2231ms for det opprinnelige spørsmålet. CLR-sammendraget kan oppsummeres som følgende trinn: Les dataene i minnet, bestilt etter produkt og dato. Når du behandler hver transaksjon, legger du til en løpende sum av kostnadene. Når en transaksjon er et annet produkt enn den forrige transaksjonen, tilbakestill kjørestrengen til 0. Vedlikehold en peker til den første transaksjonen som har samme (produkt, dato) som gjeldende transaksjon. Når den siste transaksjonen med det (produktet, datoen) oppstår, beregner du rullingsummen for den transaksjonen og bruker den til alle transaksjoner med samme (produkt, dato) Returner alle resultatene til brukeren. Bruk SET STATISTICS IO ON. denne tilnærmingen rapporterer at ingen logisk IO har oppstått Wow, en perfekt løsning (Faktisk ser det ut til at SET STATISTICS IO ikke rapporterer IO påløpt i CLR. Men fra koden er det enkelt å se at det er akkurat en skanning av bordet laget og henter dataene i rekkefølge av indeksen Paul foreslo. Som rapportert av SET STATISTICS TIME ON, er CPU-tiden nå 187ms. Så dette er en ganske forbedring over T-SQL-tilnærmingen. Dessverre er den totale forløpte tiden for begge tilnærmingene veldig lik på omtrent et halvt sekund hver. Den CLR-baserte tilnærmingen må imidlertid sende 113K-rader til konsollen (vs. bare 52K for T-SQL-tilnærmingen som grupperer etter produktdatoer), derfor er det derfor Ive fokusert på CPU-tiden i stedet En annen stor fordel ved denne tilnærmingen er at den gir nøyaktig de samme resultatene som den opprinnelige loopseek-tilnærmingen, inkludert en rad for hver transaksjon, selv i tilfeller der et produkt selges flere ganger på samme dag. (På AdventureWorks sammenlignet jeg spesifikt rad - by-rad re satser og bekreftet at de knytter seg til Pauls originale spørring.) En ulempe med denne tilnærmingen, i hvert fall i sin nåværende form, er at den leser all data i minnet. Algoritmen som er designet, trenger imidlertid strengt den gjeldende vindusrammen i minnet til enhver tid og kan oppdateres for å arbeide for datasett som overskrider minnet. Paul har illustrert dette punktet i sitt svar ved å produsere en implementering av denne algoritmen som bare lagrer glidevinduet i minnet. Dette kommer på bekostning av å gi høyere tillatelser til CLR-montering, men ville definitivt være verdt å skalere denne løsningen opp til vilkårlige store datasett. T-SQL - en skanning, gruppert etter dato Utførelsesplanen Fra utførelsesplanen ser vi at den opprinnelige indeksen foreslått av Paul er tilstrekkelig til å tillate oss å utføre en enkelt bestilt skanning av Production. TransactionHistory. bruker en sammenføyning til å kombinere transaksjonsloggen med hver mulig produktdatakombinasjon. Det er noen viktige forutsetninger bakket inn i denne tilnærmingen. Jeg antar at det vil være opp til Paul å avgjøre om de er akseptable :) Jeg bruker Production. Product-tabellen. Denne tabellen er fritt tilgjengelig på AdventureWorks2012 og forholdet håndheves av en fremmed nøkkel fra Production. TransactionHistory. så jeg tolket dette som et rettferdig spill. Denne tilnærmingen er avhengig av at transaksjoner ikke har en tidskomponent på AdventureWorks2012 hvis de gjorde det, og det ville ikke lenger være mulig å generere det komplette settet av produktdatakombinasjoner uten å først ta et pass over transaksjonsloggen. Jeg produserer en rad som inneholder bare én rad per produktdatapar. Jeg tror at dette er antagelig riktig og i mange tilfeller et mer ønskelig resultat å returnere. For hver produktdato har jeg lagt til en NumOrders-kolonne for å angi hvor mange salg som skjedde. Se følgende skjermbilde for å sammenligne resultatene av det opprinnelige spørsmålet versus det foreslåtte spørsmålet i tilfeller der et produkt ble solgt flere ganger på samme dato (f. eks. 319 2007-09-05 00: 00: 00.000) CLR - en skanning , full ugruppert resultat sett Hovedfunksjonen kroppen Det er ikke noe å se her hoveddelen av funksjonen erklærer inngangene (som må matche den tilsvarende SQL-funksjonen), setter opp en SQL-tilkobling, og åpner SQLReader. Ive skilt ut hovedlogikken slik at det er lettere å fokusere på: Følgende logikk kan skrives inline, men det er litt lettere å lese når de deles opp i egne metoder. Tynger alt sammen i SQL Alt opp til dette punktet har vært i C, så vi kan se den faktiske SQL involvert. (Alternativt kan du bruke dette distribusjonsskriptet til å lage sammenstillingen direkte fra bitene i min samling i stedet for å kompilere deg selv.) CLR-tilnærmingen gir mye mer fleksibilitet for å optimalisere algoritmen, og det kan sannsynligvis bli avstemt enda av en ekspert i C. Det er imidlertid også ulemper med CLR-strategien. Noen ting å huske på: Denne CLR-tilnærmingen holder en kopi av datasettet i minnet. Det er mulig å bruke en streaming-tilnærming, men jeg opplevde innledende problemer og fant ut at det er et utmerket Connect-problem som klager på at endringer i SQL 2008 gjør det vanskeligere å bruke denne typen tilnærming. Det er fortsatt mulig (som Paul demonstrerer), men krever et høyere nivå av tillatelser ved å sette databasen som TRUSTWORTHY og gi EXTERNALACCESS til CLR-enheten. Så det er litt stress og potensielle sikkerhetsimplikasjoner, men utbetalingen er en streaming-tilnærming som kan bedre skalere til mye større datasett enn de som er på AdventureWorks. CLR kan være mindre tilgjengelig for noen DBAer, noe som gjør en slik funksjon mer av en svart boks som ikke er så gjennomsiktig, ikke så lett modifisert, ikke så lett distribuert, og kanskje ikke så lett feilsøkt. Dette er en ganske stor ulempe når det sammenlignes med en T-SQL-tilnærming. Bonus: T-SQL 2 - den praktiske tilnærmingen Id faktisk bruker Etter å ha forsøkt å tenke på problemet kreativt for en stund, trodde jeg at Id også postet den ganske enkle, praktiske måten som jeg sannsynligvis ville velge å takle dette problemet hvis det kom opp i mitt daglige arbeid. Det gjør bruk av SQL 2012-vinduets funksjonalitet, men ikke i en form for banebrytende måte som spørsmålet hadde håpet på: Dette gir faktisk en ganske enkel total spørringsplan, selv når man ser på begge de to aktuelle spørringsplanene sammen: Noen få grunner jeg liker denne tilnærmingen: Den gir det ønskede resultatoppsettet i problemstillingen (i motsetning til de fleste andre T-SQL-løsninger, som returnerer en gruppert versjon av resultatene). Det er lett å forklare, forstå og feilsøke jeg kommer ikke tilbake et år senere og lurer på hvordan hecken jeg kan gjøre en liten forandring uten å ødelegge korrektheten eller ytelsen. Det går i omtrent 900ms på det angitte datasettet, i stedet for de 2700ms Den opprinnelige loop-søk Hvis dataene var mye tettere (flere transaksjoner per dag), vokser beregningskompleksiteten ikke kvadratisk med antall transaksjoner i skyvevinduet (som det gjør for det opprinnelige spørsmålet) Jeg tror dette er en del av Pauls bekymring om ønsket å unngå flere skanninger Det resulterer i i det vesentlige ingen tempdb IO i nyere oppdateringer av SQL 2012 på grunn av ny tempdb lat skrivefunksjon For svært store datasett er det trivielt å dele arbeidet i separate batcher for hvert produkt hvis minnetrykk var å bli en bekymring Et par potensielle advarsler: Mens det teknisk skanner Production. Transaction History bare en gang, er det ikke virkelig en skanningstilnærming fordi tempetabellen av tilsvarende størrelse og vil trenge å utfør ekstra logicion IO på den tabellen også. Men jeg ser ikke dette som for forskjellig fra et arbeidsbord som vi har mer manuell kontroll over siden vi har definert sin presise struktur. Avhengig av ditt miljø, kan bruken av tempdb bli sett på som en positiv (f. eks. På et separat sett med SSD-stasjoner) eller en negativ (høy samtidighet på serveren, mye tempdb-påstand allerede) besvart 8. september kl 15:41 Dette er et langt svar, så jeg bestemte meg for å legge til et sammendrag her. Først presenterer jeg en løsning som gir nøyaktig samme resultat i samme rekkefølge som i spørsmålet. Den skanner hovedtabellen 3 ganger: for å få en liste over ProductIDs med datoperioden for hvert produkt, oppsummerer du kostnadene for hver dag (fordi det er flere transaksjoner med samme datoer), for å bli resultat med originale rader. Deretter sammenligner jeg to tilnærminger som forenkler oppgaven og unngår en siste skanning av hovedbordet. Resultatet er en daglig oppsummering, dvs. hvis flere transaksjoner på et produkt har samme dato, rulles de til en enkelt rad. Min tilnærming fra forrige trinn skanner tabellen to ganger. Tilnærming av Geoff Patterson skanner bordet en gang, fordi han bruker ekstern kunnskap om datoperioden og listen over produkter. Til slutt presenterer jeg en enkeltpassløsning som igjen gir en daglig oppsummering, men det krever ikke ekstern kunnskap om rekkevidde av datoer eller liste over ProductIDs. Jeg vil bruke AdventureWorks2014 database og SQL Server Express 2014. Endringer i den opprinnelige databasen: Endret type Production. TransactionHistory. TransactionDate fra datetime til dato. Tidskomponenten var likevel null. Lagt til kalenderbordet dbo. Calendar Lagt indeks til Production. TransactionHistory MSDN artikkel om OVER-klausul har en lenke til et utmerket blogginnlegg om vindufunksjoner av Itzik Ben-Gan. I det innlegget forklarer han hvordan OVER fungerer, forskjellen mellom ROWS og RANGE-alternativene, og nevner dette svært problemet med å beregne en rullende sum over et datoperiode. Han nevner at dagens versjon av SQL Server ikke implementerer RANGE i sin helhet, og implementerer ikke tidsintervalldatatyper. Hans forklaring på forskjellen mellom ROWS og RANGE ga meg en ide. Datoer uten hull og duplikater Hvis TransactionHistory-tabellen inneholdt datoer uten hull og uten duplikater, ville følgende spørring gi riktige resultater: Faktisk ville et vindu på 45 rader dekke nøyaktig 45 dager. Datoer med hull uten duplikater Dessverre har våre data hull i datoer. For å løse dette problemet kan vi bruke et Kalender-tabell for å generere et sett med datoer uten hull, deretter VENSTRE KJØP originale data til dette settet og bruk det samme spørsmålet med ROWS MELLOM 45 PRECEDING AND CURRENT ROW. Dette ville bare gi korrekte resultater hvis datoene ikke gjentar (innenfor samme ProductID). Datoer med hull med duplikater Dessverre har våre data begge hull i datoer, og datoer kan gjenta innen samme ProductID. For å løse dette problemet kan vi GROUP opprinnelige data ved ProductID, TransactionDate for å generere et sett med datoer uten duplikater. Bruk deretter Kalender-tabellen til å generere et sett med datoer uten hull. Da kan vi bruke spørringen med ROWS BETWEEN 45 PRECEDING OG CURRENT ROW for å beregne rullende SUM. Dette ville gi riktige resultater. Se kommentarer i spørringen nedenfor. Jeg bekreftet at denne spørringen gir samme resultater som tilnærmingen fra spørsmålet som bruker subquery. Første spørringen bruker subquery, andre - denne tilnærmingen. Du kan se at varighet og antall leser er mye mindre i denne tilnærmingen. Flertallet av estimerte kostnader i denne tilnærmingen er den endelige ORDER BY. se nedenfor. Subquery tilnærming har en enkel plan med nestede løkker og O (nn) kompleksitet. Planlegg for denne tilnærmingen skanner TransactionHistory flere ganger, men det er ingen løkker. Som du kan se mer enn 70 av estimert kostnad, er sorteringen for den endelige ORDER BY. Toppresultat - subquery. bunn - OVER. Unngå ekstra skanning Den siste indekssøkingen, Merge Join og Sorter i planen ovenfor skyldes den endelige INNER JOIN med originaltabellen for å gjøre det endelige resultatet nøyaktig det samme som en langsom tilnærming med subquery. Antall returnerte rader er det samme som i TransactionHistory-tabellen. Det er rader i TransactionHistory når flere transaksjoner skjedde samme dag for samme produkt. Hvis det er OK å bare vise daglig sammendrag i resultatet, kan denne siste JOIN bli fjernet, og spørringen blir litt enklere og litt raskere. Den siste indekssøk, sammenslåing og sortering fra forrige plan erstattes med filter, som fjerner rader lagt til av kalender. Likevel skannes TransactionHistory to ganger. En ekstra skanning er nødvendig for å få rekkevidde av datoer for hvert produkt. Jeg var interessert i å se hvordan den sammenlikner med en annen tilnærming, der vi bruker ekstern kunnskap om det globale spekteret av datoer i TransactionHistory. pluss ekstra bord Produkt som har alle ProductIDs for å unngå den ekstra skanningen. Jeg fjernet beregning av antall transaksjoner per dag fra denne spørringen for å gjøre sammenligningen gyldig. Det kan legges til i begge spørringene, men jeg liker å holde det enkelt å sammenligne. Jeg måtte også bruke andre datoer, fordi jeg bruker 2014 versjon av databasen. Begge spørringene returnerer det samme resultatet i samme rekkefølge. Her er tid og IO statistikk. To-skanning varianten er litt raskere og har færre leser, fordi en-skanning variant må bruke Worktable mye. Dessuten genererer en skanningsvariant flere rader enn nødvendig som du kan se i planene. Det genererer datoer for hvert ProductID som er i produkttabellen, selv om et ProductID ikke har noen transaksjoner. Det er 504 rader i produkttabell, men bare 441 produkter har transaksjoner i TransactionHistory. Dessuten genererer det samme tidsintervall for hvert produkt, noe som er mer enn nødvendig. Hvis TransactionHistory hadde en lengre samlet historie, med hvert enkelt produkt med relativt kort historie, ville antall ekstra unødvendige rader være enda høyere. På den annen side er det mulig å optimalisere to-scan-varianten litt lenger ved å opprette en annen, smalere indeks på bare (ProductID, TransactionDate). Denne indeksen vil bli brukt til å beregne StartEnd-datoer for hvert produkt (CTEProducts), og det ville ha mindre sider enn å dekke indeksen og som følge av at mindre leser. Så, vi kan velge, enten ha en ekstra eksplisitt enkel skanning, eller ha en implisitt Worktable. BTW, hvis det er OK å få et resultat med bare daglige oppsummeringer, så er det bedre å lage en indeks som ikke inkluderer ReferenceOrderID. Det ville bruke mindre sider mindre IO. Enkeltpasningsløsning ved hjelp av CROSS APPLY Det blir et veldig langt svar, men her er en annen variant som bare returnerer daglig sammendrag igjen, men det gjør bare en skanning av dataene, og det krever ikke ekstern kunnskap om datointervall eller liste over ProductIDs. Det gjør ikke mellomliggende sorter også. Samlet ytelse ligner på tidligere varianter, men synes å være litt verre. Hovedideen er å bruke et bord med tall for å generere rader som fyller hullene i datoer. For hver eksisterende dato, bruk LEAD til å beregne størrelsen på gapet i dager, og bruk deretter CROSS APPLY for å legge til ønsket antall rader i resultatsettet. Først prøvde jeg det med et permanent bord med tall. Planen viste et stort antall lesninger i denne tabellen, selv om den faktiske varigheten var stort sett den samme, som da jeg genererte tall i fly ved hjelp av CTE. Denne planen er lengre, fordi spørringen bruker to vindusfunksjoner (LEAD og SUM). En alternativ SQLCLR-løsning som kjører raskere og krever mindre minne: Det krever EXTERNALACCESS-tillatelsett fordi det bruker en loopback-tilkobling til målserveren og databasen i stedet for (sakte) kontekstforbindelse. Slik kalles funksjonen: Produserer nøyaktig de samme resultatene, i samme rekkefølge som spørsmålet. Profiler logisk leser: 481 Hovedfordelen ved denne implementeringen er at den er raskere enn å bruke kontekstforbindelsen, og den bruker mindre minne. Den holder bare to ting i minnet til enhver tid: Enhver duplikat rader (samme produkt og transaksjonsdato). Dette kreves fordi inntil produkt eller dato endres, vet vi ikke hva det endelige løpende beløpet vil være. I utvalgsdata er det en kombinasjon av produkt og dato som har 64 rader. En glidende 45 dagers rekkevidde av kostnads - og transaksjonsdata bare for det nåværende produktet. Dette er nødvendig for å justere den enkle løpesummen for rader som forlater 45-dagers skyvevinduet. Denne minimale caching bør sikre at denne metoden skaleres ganske sikkert bedre enn å prøve å holde hele inngangssettet i CLR-minne. Hvis du er på 64-biters Enterprise, Developer eller Evaluation Edition av SQL Server 2014, kan du bruke In-Memory OLTP. Løsningen vil ikke være en enkelt skanning og og vil neppe bruke noen vindusfunksjoner i det hele tatt, men det kan legge til litt verdi for dette spørsmålet, og algoritmen som brukes kan muligens brukes som inspirasjon til andre løsninger. Først må du aktivere In-Memory OLTP på AdventureWorks-databasen. Parameteren til prosedyren er en In-Memory-tabellvariabel, og den må defineres som en type. ID er ikke unikt i denne tabellen, det er unikt for hver kombinasjon av ProductID og TransactionDate. Det er noen kommentarer i prosedyren som forteller deg hva det gjør, men totalt sett beregner den kjørende totalen i en løkke, og for hver iterasjon blir det en oppslag for løpende summen som det var 45 dager siden (eller mer). Den nåværende løpende summen minus løpende summen som det var 45 dager siden, er den rullende 45-dagers summen vi leter etter. Invoke prosedyren som dette. Testing dette på datamaskinen min Klientstatistikk rapporterer en Total kjøretid på rundt 750 millisekunder. For sammenligninger tar underspørselsversjonen 3,5 sekunder. Denne algoritmen kan også brukes av vanlig T-SQL. Beregn kjørestrengen, bruk rekkevidde ikke rader, og lagre resultatet i et tempeltabell. Deretter kan du spørre det bordet med en selvtillit til til løpende summen som det var 45 dager siden og beregne rullende summen. Imidlertid er implementeringen av rekkevidde i forhold til rader ganske treg på grunn av det faktum at det er behov for å behandle duplikater av ordren etter en annen setning, slik at jeg ikke fikk all den gode ytelsen med denne tilnærmingen. En løsning på det kan være å bruke en annen vindusfunksjon som lastverdien () over en beregnet løpende total ved hjelp av rader for å simulere en rekkevidde. En annen måte er å bruke max () over (). Begge hadde noen problemer. Finne riktig indeks som skal brukes for å unngå sorter og unngå spoler med maks () over () versjonen. Jeg ga opp optimalisere disse tingene, men hvis du er interessert i koden jeg har så langt, vennligst gi meg beskjed. svaret 15. september kl 15:38 Vel, det var morsomt :) Min løsning er litt tregere enn GeoffPattersons, men en del av det er at jeg knytter meg tilbake til det opprinnelige bordet for å eliminere en av Geoffs antagelser (dvs. en rad per produktdatapar). Jeg gikk med antagelsen om at dette var en forenklet versjon av en endelig spørring, og kan kreve tilleggsinformasjon ut av det opprinnelige tabellen. Merk: Jeg låner Geoffs kalenderbord og faktisk endte med en veldig lignende løsning: Her er spørringen selv: I utgangspunktet bestemte jeg meg for at den enkleste måten å håndtere det var å bruke alternativet til ROWS-klausulen. Men det krevde at jeg bare har en rad per ProductID. TransactionDate kombinasjon og ikke bare det, men jeg måtte ha en rad per ProductID og mulig dato. Jeg gjorde det som kombinerer tabellene Produkt, kalender og TransactionHistory i en CTE. Da måtte jeg opprette en annen CTE for å generere rullende informasjon. Jeg måtte gjøre dette fordi hvis jeg kom tilbake til den opprinnelige tabellen, fikk jeg raden eliminering som kastet bort resultatene mine. Etter det var det enkelt å bli med min andre CTE tilbake til det opprinnelige bordet. Jeg har lagt til TBE-kolonnen (som skal elimineres) for å kvitte seg med de tomme rader som er opprettet i CTE-ene. Også jeg brukte en CROSS APPLY i den første CTE for å generere grenser for kalenderen mitt. Jeg la til den anbefalte indeksen: Og fikk den endelige utførelsesplanen: EDIT: Til slutt la jeg til en indeks på kalenderen som økte ytelsen med en rimelig margin. besvart 10. september kl 15:34 Jeg har noen alternative løsninger som ikke bruker indekser eller referansetabeller. Kanskje de kunne være nyttige i situasjoner der du ikke har tilgang til noen ekstra tabeller og ikke kan opprette indekser. Det ser ut til å være mulig å få riktige resultater når de grupperes av TransactionDate med bare ett enkelt pass på dataene og bare en enkelt vindufunksjon. Imidlertid kunne jeg ikke finne ut en måte å gjøre det med bare ett vindusfunksjon når du ikke kan gruppere av TransactionDate. For å gi en referanseramme, har den opprinnelige løsningen i spørsmålet på min maskin en CPU-tid på 2808 ms uten dekselindeksen og 1950 ms med dekselindeksen. Jeg tester med AdventureWorks2014-databasen og SQL Server Express 2014. Lar oss starte med en løsning for når vi kan gruppere med TransactionDate. En løpende sum i løpet av de siste X-dagene kan også uttrykkes på følgende måte: Kjøresum for en rad som er summen av alle tidligere rader - Kjør summen av alle tidligere rader som dato er utenfor datovinduet. I SQL er en måte å uttrykke dette ved å lage to kopier av dataene dine og for den andre kopien, multiplisere prisen med -1 og legge til X1 dager i datakolonnen. Å beregne en løpende sum over alle dataene vil implementere formelen ovenfor. Jeg viser dette for noen eksempeldata. Nedenfor er noen prøve dato for et enkelt ProductID. Jeg representerer datoer som tall for å gjøre beregningene enklere. Startdata: Legg til i en andre kopi av dataene. Den andre kopien har 46 dager lagt til datoen, og kostnaden multiplisert med -1: Ta løpesummen bestilt av Dato stigende og Kopiert Row synkende: Filtrer de kopierte radene for å få ønsket resultat: Følgende SQL er en måte å implementere over algoritmen: På min maskin tok dette 702 ms CPU-tid med dekselindeksen og 734 ms CPU-tid uten indeksen. Spørringsplanen finner du her: brentozarpastetheplanidSJdCsGVSl En ulempe av denne løsningen er at det synes å være en uunngåelig sortering når du bestiller av den nye TransactionDate-kolonnen. Jeg tror ikke at denne typen kan løses ved å legge til indekser fordi vi må kombinere to kopier av dataene før du bestiller. Jeg var i stand til å kvitte seg med en sortering på slutten av spørringen ved å legge til i en annen kolonne til BESTILL BY. Hvis jeg bestilte av FilterFlag, fant jeg ut at SQL Server ville optimalisere ut den kolonnen fra sorteringen og ville utføre en eksplisitt sortering. Løsninger for når vi må returnere et resultatsett med dupliserte TransactionDate-verdier for samme ProductId, var mye mer komplisert. Jeg vil oppsummere problemet mens du samtidig trenger å partisjonere av og bestille med samme kolonne. Syntaxen som Paul ga til, løser dette problemet, så det er ikke overraskende at det er så vanskelig å uttrykke med de nåværende vindufunksjonene som er tilgjengelige i SQL Server (hvis det ikke var vanskelig å uttrykke det, ville det ikke være nødvendig å utvide syntaksen). Hvis jeg bruker ovennevnte spørring uten gruppering, får jeg forskjellige verdier for rullende sum når det er flere rader med samme ProductId og TransactionDate. En måte å løse dette på er å gjøre samme løpende sumberberegning som ovenfor, men også for å markere den siste raden i partisjonen. Dette kan gjøres med LEAD (forutsatt at ProductID aldri er NULL) uten en ekstra sortering. For den endelige løpende sumverdien bruker jeg MAX som en vindusfunksjon til å bruke verdien i den siste raden i partisjonen til alle rader i partisjonen. På min maskin tok dette 2464ms CPU-tid uten dekselindeksen. Som før ser det ut til å være en uunngåelig sortering. Spørringsplanen finner du her: brentozarpastetheplanidHyWxhGVBl Jeg tror at det er rom for forbedring i ovennevnte spørring. Det er sikkert andre måter å bruke Windows-funksjoner på for å få det ønskede resultatet. Vindufunksjoner (OVER Klausul) Hjelp Gjør en forskjell Hvis jeg måtte nevne et konsept i standard SQL som jeg trodde var den viktigste, og det er verdt Microsoftrsquos investering For fremtidige versjoner av SQL Server, sier Irsquod vindufunksjoner, hendene ned, uten tvil. Vinduefunksjoner er en delmengde av hva standardanropene angir, dvs. funksjoner som brukes på et sett med rader. Termen vinduet brukes til å beskrive settet av rader som funksjonen fungerer på, og språket gir en klausul som heter OVER hvor du oppgir vinduspesifikasjonen. Så whatrsquos big deal, og hva som gjør vindufunksjoner viktigere enn andre funksjoner som mangler i SQL Server. Det er så mange reasonshellip Men første Irsquoll gir litt mer bakgrunn om vindufunksjoner, og deretter får Irsquoll grunnene og demonstrerer bruk casehellip 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