1 / 38

Bruk og misbruk av SQL

DAG HOFTUN KNUTSEN daghk@online.no. Bruk og misbruk av SQL. Agenda. Generelt om SQL 5 vanlige feil 5 gode tips. + Noen nyheter i 2005. Bakgrunn og historikk. SQL = Structured Query Language Misvisende navn: Det er ikke bare query Stammer opprinnelig fra IBM

siusan
Download Presentation

Bruk og misbruk av SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. DAG HOFTUN KNUTSEN daghk@online.no Bruk og misbruk av SQL

  2. Agenda • Generelt om SQL • 5 vanlige feil • 5 gode tips + Noen nyheter i 2005

  3. Bakgrunn og historikk • SQL = Structured Query Language • Misvisende navn: Det er ikke bare query • Stammer opprinnelig fra IBM • SystemR i 1976, protoyp som siden ble DB2 • Kritisert for ikke å følge den matematiske teorien godt nok • Resultatorientert • Spesifiser ønsket resultat, ikke hvordan resultatet skal produseres • Standardisert, benyttes av alle relasjonsdatabaseprodukter • Men alle avviker mer eller mindre fra standarden • Transact-SQL • Sybase versjon av SQL fra 1987 • Utvider SQL med prosedyrespråk

  4. Ikke bare “Query” • Data Definisjon • CREATE, ALTER, DROP • Adgangskontroll • GRANT, REVOKE, DENY • Oppdatering • INSERT, UPDATE, DELETE • Enkel syntax: Noen få regler • Vanskeligheten er å utnytte kraften i språket på best mulig måte • Vanskelig å verifisere at en setning er logisk korrekt

  5. Aksessmetode SQL: Eneste måte å aksessere tabellene i databasen Server Klient Proc SQL SQL

  6. Enkelt eller komplisert Enkle SQL setninger Leser en rad av gangen, tilsv. fil lesing Klient program gjør mesteparten av jobben Kompliserte SQL setninger Joins, subqueries, funksjoner, aggregering Server gjør mesteparten av jobben kompleksitetsgrad Mindre kode å skrive og vedlikeholde Bedre ytelse Lett å forstå Lett å konvertere gamle filbaserte system Kan bli for komplisert for både programmerer og optimizer Vanligvis dårlig ytelse

  7. Agenda • Generelt om SQL • 5 vanlige feil • 5 gode tips

  8. Feil nr. 1: NULL-verdier • Null-verdi i uttrykk • Resultatet blir NULL • Bruk ISNULL eller COALESCE for å gjøre om NULL til ordentlige verdier

  9. Feil nr. 1: NULL-verdier • NULL-verdi i betingelser • NULL er aldri lik, ulik, større eller mindre enn noenting • NULL-verdier er aldri like, ulike, større eller mindre enn hverandre

  10. Feil nr. 1: NULL-verdier • NULL-verdi ved IN og NOT IN • IN “overser” NULL-verdier • En eneste NULL-verdi gjør at NOT IN ikke er oppfylt

  11. Feil nr. 2: Datamodellen • Ikke kjenne betydningen av datamodellen • Tabeller • Kolonner • Primary keys • Foreign keys • Statuskoder

  12. Hvorfor blir resultatet feil? Feil nr. 3: Datatyper • Implisitt datatype konvertering • OrderID har datatype varchar • Implisitt konvertering forhindrer bruk av indeks

  13. Feil nr. 4: Subqueries • Tilfeldigvis single-row subquery • Hvem har lik eller lavere lønn enn King? • Setningen er logisk feil, men virker fordi det tilfeldigvis er bare én King

  14. Feil nr. 4: Subqueries • Korrelert ved feiltagelse • Kolonnen DivisionName finnes ikke I Employees tabellen og hentes derfor fra Divisions tabellen • Problemet unngås hvis kolonnene i subqueries alltid kvalifiseres med tabell prefix

  15. Feil nr. 5: OUTER JOIN • OUTER JOIN med filterbetingelse “virker ikke” • WHERE filter appliseres etter join • INNER og OUTER gir samme resultat

  16. Feil nr. 5: OUTER JOIN • Legg filteret inn i JOIN betingelsen • Da virker OUTER JOIN

  17. Agenda • Generelt om SQL • 5 vanlige feil • 5 gode tips

  18. Tip nr. 1: Unngå “tidsinnstilte bomber” • Setninger som slutter å virke pga. endringer andre steder i systemet • Hva hvis noen gjør ALTER TABLE og legger til en ny kolonne Description i Products tabellen? • Kvalifiser alle kolonner med tabell prefix, også de der det i øyeblikket ikke er strengt nødvendig

  19. Tip nr. 2: Utnytt innebygde funksjoner • Skalarfunksjoner, også kalt radfunksjoner • Eksempel: Beregning av nettopris

  20. Tip nr. 2: Utnytt innebygde funksjoner • Beregnet kolonne • Denne beregningen skal sannsynligvis gjentas i mange sammenhenger • Kan defineres en gang for alle som en beregnet kolonne

  21. Tip nr. 2: Utnytt innebygde funksjoner • Aggregatfunksjoner, også kalt gruppefunksjoner • Aggregerer flere rader i kildetabellen sammen til en rad i resultatet • Mindre netttrafikk enn om aggregeringen gjøres på klientnivå

  22. Tip nr. 2: Utnytt innebygde funksjoner • Matrise rapport • Kombinasjon av CASE og SUM • CASE fordeler på kolonner • SUM aggregerer rader

  23. 2005 Tip nr. 2: Utnytt innebygde funksjoner • Ny operator for matrise rapporter: PIVOT • Gjør implisitt GROUP BY på kolonner som ikke er med i PIVOT klausulen • Inline view (derived table) benyttes for å bli kvitt uønskede kolonner

  24. 2005 Tip nr. 2: Utnytt innebygde funksjoner • Window funksjoner, også kalt analytiske funksjoner • Helt ny kategori av funksjoner • Konseptuelt utføres disse funksjonene etter at resultatsettet er produsert, men før den endelige ORDER BY • Kjennetegnes ved OVER () klausul • Kan spesifisere sortering og/eller partisjonering • To hovedkategorier • RANK funksjoner • Aggregatfunksjoner

  25. 2005 Tip nr. 2: Utnytt innebygde funksjoner • RANK funksjon • Finnes også DENSE_RANK som ikke hopper over etterfølgende etter like verdier, og ROW_NUMBER som teller uten hensyn til verdi

  26. 2005 Tip nr. 2: Utnytt innebygde funksjoner • PARTITION BY deler inn resultatsettet i partisjoner (grupper) • Funksjonen beregnes innenfor hver enkelt gruppe

  27. 2005 Tip nr. 2: Utnytt innebygde funksjoner • Blande rad- og gruppeverdier • Tillatt med window funksjon, ikke med “vanlig” aggregatfunksjon

  28. Tip nr. 3: Utnytt joins og subqueries • Eksempel: Tabell med romreservasjoner • Problemstilling: Finn konflikterende (overlappende) reservasjoner

  29. Tip nr. 3: Utnytt joins og subqueries • Eksempel: Beregning av rangnummer hvis RANK ikke er tilgjengelig • Advarsel: • Blir tung hvis tabellen er stor • Alternativ: Tildel rangnummer prosedyrelt

  30. Tip nr. 4: Splitt og hersk • Eksempel: Aggregering i flere nivåer • Aggregatfunksjoner kan ikke nestes i hverandre • Workaround: Subquery i FROM-klausulen • Også kalt derived table eller inline-view

  31. Tip nr. 4: Splitt og hersk • Et problem kan deles opp i mindre deler • Kan løse ellers uløselige problem • Forenkler programmering • Kan forbedre ytelse • Følgende objekter har tabellstruktur og kan brukes til å mellomlagre delresultater • Permanent tabell • Temporær tabell • Derived table (inline view) • Common table expression (CTE) (Ny i 2005) • Lagret view • Indeksert view • Tabell variabel • Tabell funksjon • Alle disse kan leses som tabeller og kan benyttes og kombineres i queries • Enkle SELECT-setninger, JOIN, UNION, Subqueries etc. • De har ulike egenskaper og er derfor egnet i ulike situasjoner

  32. 2005 Tip nr. 4: Splitt og hersk • Common Table Expression, også kalt “factored subquery” • Mellomting mellom view og derived table • Defineres før SELECT i egen WITH klausul • Nyttig hvis view-et skal gjenbrukes flere ganger i samme setning

  33. 2005 Rekursiv Common Table Expression • En CTE kan referere til seg selv rekursivt • Må bestå av to deler • En ikke-rekursiv SELECT kalt anchor member • En rekursiv SELECT • De to er koblet sammen med UNION ALL • Andre mengdeoperatorer er ikke tillatt

  34. 2005 Hierarkier • Rekursiv CTE er spesielt nyttig for å håndtere hierarkier

  35. ID: 2 Fuller Level 1 Level 2 Level 3 ID: 5 Buchanan ReportsTo:2 ID: 8 Callahan ReportsTo:2 ID: 1 Davolio ReportsTo:2 ID: 3 Leverling ReportsTo:2 ID: 4 Peacock ReportsTo:2 ID: 7 King ReportsTo:5 ID: 9 Dodsworth ReportsTo:5 ID: 6 Suyama ReportsTo:5 2005 Hierarkier • Vi vil ha hierarkiet presentert slik:

  36. 2005 Rekursiv CTE eksempel Anchor member Starting point Recursive member

  37. Tip nr. 5: Eksekveringsplaner • Sjekk eksekveringsplanene for ulike alternative løsninger • SQL er et resultatorientert språk som beskriver ønsket resultat, ikke hvordan resultatet skal produseres • Optimereren er den del av SQL-oversetteren som finner ut hvordan, og forsøker å gjøre det mest mulig effektivt • Vanskelig å forutsi hva optimereren vil gjøre • Eksekveringsplanen viser hvordan optimereren vil utføre setningen • Indeksbruk, join strategi etc

  38. Oppsummering • Det er god grunn til å sette seg grundig inn i mulighetene i SQL • Utnytte kraften i språket, men uten å overdrive • Unngå feller og problemer • Oppnå god ytelse DAG HOFTUN KNUTSEN daghk@online.no

More Related