Förord

Databaser är själva mittpunkten i många Enterprise applikationer, så det är viktigt att man håller ett vakande öga på databasprogrammeringen. Jag har råkat ut för många fall där man ser över databasprogrammeringen, och där man tror att det är så lätt att vem som helst kan göra det. Det är fel. För att få en databas som presterar bättre så behöver du en riktig DBA och en specialiserad databasprogrammerare, vare sig det gäller Microsoft SQL Server, Oracle, Sybase, DB2 eller vilken som helst. Om du inte använder dig av databasspecialister under utvecklingsprocessen, så kommer databaserna ofta att bli orsaken till flaskhalsar i prestanda. Jag har valt att skriva den här artikeln för att samla ihop mina bästa tips vid databasprogrammering, som mina DBA-kompanjoner och databasutvecklare kan dra fördel av.

Ett kompendium för SQL Server databaskodning, Tips och Riktlinjer för bra programmering

Några riktlinjer

• Bestäm dig för en enhetlig namngivning av databaserna, standardisera den över hela organisationen och gör ert bästa för att följa den överenskommelsen. Det kommer att hjälpa dig göra dina koder mer läsbara och begripliga. Klicka här för att se hur jag namnger mina databaser.
  
• Försäkra dig om att du normaliserar dina data till åtminstone den 3:e normala formen. Men se samtidigt till att inte det sker på bekostnaden av satsernas prestanda. En denormalisering kan hjälpa satserna att prestera snabbare.
  
• Var generös med kommentarer i lagrade procedurer, triggers och SQL Batchfiler, så fort någonting verkar oklart. Det hjälper andra programmerare att förstå koden bättre. Oroa dig inte över längden på kommentarerna, eftersom det inte påverkar prestandan som det gjorde i interpreterade språk som t ex ASP 2.0.
  
• Använd inte SELECT * i dina satser. Specificera alltid de kolumnnamnen som du vill ha ut efter SELECT, som här:
  
  SELECT CustomerID, CustomerFirstName, City
  
  Den här tekniken reducerar disk I/O och förbättrar prestandan.
  
• Försök att undvika pekare på Servern så mycket som möjligt. Försök att alltid hålla dig till Setbaserade metoder istället för procedurmetoder när du vill komma åt och manipulera data. Du kan ofta komma undan pekare genom att använda SELECT uttrycket istället.
  
  Men om pekare är oundvikliga, så bör du istället använda en WHILE-loop. Jag har ofta testat och kommit fram till att en WHILE-loop presterar snabbare än en pekare. Men för att du ska kunna ha en WHILE-loop som ersättning för pekare, så måste du ha en kolumn (med en primär- eller unik nyckel) som identifierar varje post unikt. Jag tror personligen att varje tabell måste ha med en primär- eller unik nyckel. Klicka här för att se några exempel på hur du använder en WHILE-loop.
  
• Undvik att skapa temporära tabeller så mycket som möjligt när du hanterar data, eftersom temporära tabeller innebär mer disk I/O. Överväg att använda avancerad SQL, vyer, SQL Server 2000 tabellvariabler eller härledda tabeller, istället för temporära tabeller.
  
• Undvik att använda ”vilda” karaktärer i början av ett ord då du söker med LIKE uttrycket. Om du gör det så initieras en Indexscan, vilket strider mot syftet med ett Index. Den första av de två följande SELECT uttrycken kommer att resultera i en Indexscan, och den andra resulterar i en Indexsökning:
  
  SELECT LocationID FROM Locations WHERE Specialities LIKE ‘%pples’
  
  SELECT LocationID FROM Locations WHERE Specialities LIKE ‘A%s’
  
  Undvik också att söka med hjälp av relationsoperatorer (<>) eller logiska operatorer (NOT), eftersom de också resulterar i tabell- eller Indexscans.
  
• Använd ”härledda tabeller” (derived tables) så ofta du kan, eftersom de presterar bättre. Följande sats hämtar ut den näst högsta försäljningen ur Employees-tabellen:
  
  
SELECT MIN(Salary) FROM Employees WHERE EmpID IN
(SELECT TOP 2 EmpID FROM Employees ORDER BY Salary Desc)

  
  Samma sats kan skrivas om genom att använda en härledd tabell, och den kommer att prestera dubbelt så snabbt som ovanstående sats:
  
  
SELECT MIN(Salary) FROM
(SELECT TOP 2 Salary FROM Employees
ORDER BY Salary DESC) AS A

  
  Det här är bara ett exempel på hur dina resultat kan variera under olika scenarier beroende på databasens design, Index, volymen av data, osv. Så försök att testa olika varianter av SQL-satser som du kan skriva, och använd den som är mest effektiv.
  
• När du designar din databas så måste du hela tiden tänka på prestandan. Du kan inte förändra prestandan senare när databasen är i produktion, eftersom det involverar att bygga om tabeller och Index, skriva om satser, osv. Du kan analysera dina satser genom att använda en grafisk exekveringsplan i Query Analyzer, eller genom att skriva något av kommandona SHOWPLAN_TEXT eller SHOWPLAN_ALL. Försäkra dig om att dina SQL-satser genomför en Indexsökning hellre än en Index- eller tabellscan. En Index- eller tabellscan är inte bra alls, och bör undvikas så mycket som möjligt. Sätt rätt Index på rätt kolumner.
  
• Sätt ägarens namn som prefix på tabellnamnet, för att göra det mer läsbart samt undvika onödig förvirring. Microsoft SQL Server Books Online påpekar till och med att du genom att sätta ägarens namn i tabellnamnet bidrar till att återanvända exekveringsplanen, samt höjer prestandan ytterligare.
  
• Genom att skriva SET NOCOUNT ON i början på dina SQL Batchfiler, lagrade procedurer samt triggers i din produktionsmiljö, så avvärjer du meddelanden som ’(1 row(s) affected)’ efter att ha exekverat uttryck som INSERT, UPDATE och DELETE. Det höjer prestandan vi t ex lagrade procedurer genom att reducera nätverkstrafiken.
  
• Försök att använda de mer läsbara ANSI-standard JOIN klausulerna istället för de gamla varianterna av JOIN klausulerna. I en ANSI JOIN så används WHERE klausulen endast för att filtrera data, medan WHERE klausulen i de gamla varianterna av JOINs används både som JOIN villkor och filtrering. Den första av de följande två satserna använder en gammal JOIN vaiant, medan den andra av dem använder en ANSI JOIN:
  
  
SELECT a.au_id, t.title
FROM titles t, authors a, titleauthor ta
WHERE
a.au_id = ta.au_id AND
ta.title_id = t.title_id AND
t.title LIKE '%Computer%'



SELECT a.au_id, t.title
FROM authors a
INNER JOIN
titleauthor ta
ON
a.au_id = ta.au_id
INNER JOIN
titles t
ON
ta.title_id = t.title_id
WHERE t.title LIKE '%Computer%'

  
  
• Sätt inte prefixet “sp_” i namnet på dina egna lagrade procedurer, eftersom det prefixet är reserverat åt systemets lagrade procedurer som följer med SQL Server. Så fort som SQL Server stöter på en procedur som börjar med ”sp_” så försöker den först lokalisera proceduren i Masterdatabasen, sen försöker den se om det finns några ”qualifiers” (databasägare) tilldelade, och slutligen så försöker den sätta dbo som ägare. Så du kan verkligen spara tid när du lokaliserar en lagrad procedur genom att undvika prefixet ”sp_” i namnet.
  
• Vyer (views) används generellt sett till att visa specifika data eller specifika användare baserat på deras intresse. Vyer används också för att begränsa access till bastabellerna genom att sätta accessrättigheter på endast vyer. Ett annat viktigt användningsområde för vyer är att de kan förenkla dina SQL-satser. Bygg in sådana komplicerade relationer och beräkningar som används ofta i vyer, för att slippa repetera dessa JOINs eller beräkningar i alla dina SQL-satser. Låt de hämta ut data från vyn istället.
  
• Använd Användardefinierade datatyper (User Defined Datatypes) om en specifik kolumn ofta upprepas i tabellen. På så sätt så kan du hålla datatypen i kolumnen enhetlig i hela tabellen.
  
• Låt inte dina ”front-end” applikationer fråga ut eller manipulera data direkt genom att använda SELECT- eller INSERT/UPDATE/DELETE uttrycken. Skapa istället lagrade procedurer, och låt dina applikationer komma åt data från de lagrade procedurerna. På så sätt kan du hålla dataaccessen ren och enhetlig över alla moduler i din applikation, samtidigt som du håller arbetslogiken centraliserad i din databas.
  
• Försök att inte använda datatyperna TEXT eller NTEXT när du vill lagra stora textdata. Det finns vissa inbyggda problem associerade med datatypen TEXT. Du kan t ex inte skriva eller uppdatera textdata direkt genom att använda uttrycken INSERT eller UPDATE, utan du måste i så fall använda speciella uttryck som t ex READTEXT, WRITETEXT och UPDATETEXT. Det finns också en hel del buggar som förknippas med replikering av tabeller innehållande textkolumner. Så om du inte måste lagra mer än 8 kb text, så bör du istället använda någon av datatyperna CHAR(8000) eller VARCHAR(8000).
  
• Om du har ett val så bör du helst inte lagra binära filer eller bildfiler (Binary Large Object eller BLOBs) inuti databasen. Lagra istället sökvägen till filerna i databasen och använd den som pekare till den verkliga binära filen som ligger någonstans på Servern. Det är bättre att hämta in och manipulera dessa stora binära filer utanför databasen, eftersom en databas trots allt inte är avsedd för att lagra filer.
  
• Använd datatypen CHAR i en kolumn endast om kolumnen innehåller värden som inte är nollad (nullable). Om en CHAR kolumn är nollad, så har den ändå i SQL Server 7.0+ en fixerad längd. Så, om en CHAR(100) är NULL så kommer kolumnen ändå att äta upp 100 bytes, vilket är slöseri på plats. Använd då hellre VARCHAR(100) i sådana situationer. Dock så kräver variabla kolumnlängder lite mer processarbetslast än fixerade kolumnlängder, så välj varsamt mellan CHAR och VARCHAR beroende på längden på de data som du vill lagra.
  
• Undvik dynamiska SQL uttryck så mycket som möjligt. Dynamisk SQL tenderar att gå mycket långsammare än statisk SQL, eftersom SQL Servern måste generera en ny exekveringsplan varje gång som en dynamisk SQL-sats körs. För att undvika dynamiska SQL uttryck så kan du använda IF och CASE mer. En annan nackdel med dynamisk SQL är att de kräver direkta accessrättigheter till de objekt som de måste komma åt, t ex vyer och tabeller. Generellt sett så ges användarna accessrättigheter till de lagrade procedurer som hänvisar till tabellerna i fråga, men aldrig direkt mot tabellerna. I sådana fall så fungerar inte dynamisk SQL. Se över följande scenario där användaren ’dSQLuser’ läggs in i PUBS databasen och sen får accessrättigheter till proceduren ’dSQLproc’, men inte till någon annan tabell i PUBS databasen. Proceduren ’dSQLproc’ exekverar en direkt SELECT från ’title’ tabellen, och det fungerar. Det andra uttrycket kör samma SELECT mot ’title’ tabellen, men det använder dynamisk SQL och fallerar med följande Error:
  
  Server: Msg 229, Level 14, State 5, Line 1
  SELECT permission denied on object 'titles', database 'pubs', owner 'dbo'.
  
  
  För att få fram ovanstående problem så kör du följande kommandon:
  
sp_addlogin 'dSQLuser'
GO
sp_defaultdb 'dSQLuser', 'pubs'
USE pubs
GO
sp_adduser 'dSQLUser', 'dSQLUser'
GO
CREATE PROC dSQLProc
AS
BEGIN
SELECT * FROM titles WHERE title_id = 'BU1032' --This works
DECLARE @str CHAR(100)
SET @str = 'SELECT * FROM titles WHERE title_id = ''BU1032'''
EXEC (@str) --This fails
END
GO
GRANT EXEC ON dSQLProc TO dSQLuser
GO

  
  Du kan nu logga in på Pubsdatabasen med namnet ’dSQLuser’, och titta på proceduren ’dSQLproc’ för att se problemet.
  
• Se över följande nackdelar med att använda IDENTITY egenskapen för att generera primärnycklar, innan du använder den. IDENTITY är väldigt specifikt för SQL Server, och du kommer därför att få problem om du överför dina databasapplikationer till andra RDBMS. IDENTITY kolumner har även andra inbyggda problem. T ex så kommer IDENTITY kolumner någon gång få slut på siffror, beroende på vilken datatyp du har valt. Och efter att du har raderat en post så kan inte siffrorna återanvändas automatiskt. Dessutom så kommer inte Replikering och IDENTITY kolumner så bra överens.
  
  Så det du bör göra är att komma upp med en algoritm för att generera primärnycklar till ”front-end” eller inifrån den lagrade proceduren som ska köra INSERT. Det kan fortfarande uppstå vissa problem när du skapar egna primärnycklar, som t ex spridning medan du genererar nyckeln eller att värdena tar slut. Så överväg båda alternativen och välj det som passar dig bäst.
  
• Minimera användandet av NULL värden, eftersom de ofta kan förvirra ”front-end” applikationerna. Så till vida att de inte är kodade på ett intelligent sätt för att antingen eliminera NULL värden, eller helt enkelt konvertera dem till andra strukturer med vilket uttryck som helst som hanterar NULL resultat i NULL outputen. ISNULL och COALESCE är två värdefulla funktioner som tar hand om NULL värden. Här följer ett exempel som kan förklara problemet:
  
  Se på tabellen ’Customers’, i vilken vi lägger in namnet på kunderna. Det kan hända att mellannamnet blir ett NULL värde:
  
CREATE TABLE Customers
(
FirstName varchar(20),
MiddleName varchar(20),
LastName varchar(20)
)

  
  Lägg nu in namnet ’Tony Blair’ i tabellen, utan mellannamn:
  
INSERT INTO Customers
(FirstName, MiddleName, LastName)
VALUES ('Tony',NULL,'Blair')

  
  Följande SELECT sats kommer att returnera NULL istället för kundnamnet:
  
  SELECT FirstName + ' ' + MiddleName + ' ' + LastName FROM Customers
  
  
  För att undvika det problemet så kan du använda ISNULL, som nedan:
  
  SELECT FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName FROM Customers
  
  
  
• Om din databas inte bara ska lagra engelska karaktärer, utan en variation av karaktärer som används över hela världen, så bör du använda Unicode datatyper, som NCHAR, NVARCHAR eller NTEXT. Använd bara dessa datatyper när de verkligen behövs, eftersom de tar upp dubbelt så mycket plats som icke-Unicode datatyper.
  
• Använd alltid en kolumnlista när du använder INSERT uttryck. På så sätt kan du undvika problem om det skulle ske ändringar i tabellernas struktur (om du t ex lägger till eller raderar en kolumn). Här följer ett exempel som visar på problemet:
  
  Se följande tabell:
  
CREATE TABLE EuropeanCountries
(
CountryID int PRIMARY KEY,
CountryName varchar(25)
)

  
  Här följer ett INSERT uttryck utan kolumnlista, och den fungerar perfekt:
  
INSERT INTO EuropeanCountries
VALUES (1, 'Ireland')

  
  Låt oss nu lägga till en ny kolumn i tabellen:
  
ALTER TABLE EuropeanCountries
ADD EuroSupport bit

  
  Om du nu köra ovanstående INSERT uttryck så kommer SQL Server att generera följande Error:
  
  Server: Msg 213, Level 16, State 4, Line 1
  Insert Error: Column name or number of supplied values does not match table definition.
  
  
  Du kan undvika det här problemet genom att skriva ett INSERT uttryck med en kolumnlista, som nedan:
  
INSERT INTO EuropeanCountries
(CountryID, CountryName)
VALUES (1, 'England')

  
  
• Utför alltid dina hänvisande integritetskontroller och datavalideringar med hjälp av restriktioner (externa nyckel- och kontrollrestriktioner) istället för triggers, eftersom de är snabbare. Begränsa ditt användande av triggers till de analyser, vanliga uppgifter och valideringar som du inte kan utföra med restriktioner. Eftersom du inte själv behöver skriva någon kod för dessa valideringar (RDBMS gör allt arbete åt dig) så sparar du även in tid där.
  
• Fförsöka att låta alla dina lagrade procedurer och triggers accessa tabeller i samma ordning. På så sätt kan du undvika Deadlocking. Andra saker du bör tänka på för att undvika Deadlocks är; Håll dina transaktioner så korta som möjligt. Behandla så lite data som möjligt under en transaktion. Vänta aldrig någonsin på användar-inputs mitt under en transaktion. Använd inte någon högre nivå av låsningsindikeringar eller restriktiva isoleringsnivåer, om de verkligen inte behövs. Gör så att din ”front-end” applikation blir ”Deadlock”-intelligent, vilket innebär att dem bör få återuppta en transaktion ifall den förra transaktionen fallerade med ett Error 1205. I dina applikationer så bör du låta alla resultat returnera till SQL Server omedelbart, så att inte låsningarna på de behandlade posterna släpps och skapar blockeringar.
  
• Lasta av uppgifter såsom stringmanipulering, sammanlänkningar, postnumrering, case skiften eller typ skiften, osv, till dina ”front-end” applikationerna, ifall dessa operationer kommer att förbruka mer CPU cykler på databasservern. Försök också att utföra de grundläggande valideringarna i själva ”front-end” medan data skrivs in. Det kan spara in på onödiga nätverksrundor.
  
• Om du oroar dig för ”back-end” portabilitet så bör du undvika att manipulera bits med T-SQL, eftersom det är väldigt specifikt för RDBMS. Vidare så bör du använda bitmaps för att representera olika stadier av en specifik konflikt med normaliseringsreglerna.
  
• Lägg alltid till en @debug parameter till dina lagrade procedurer. Den kan vara av datatypen BIT. När en 1 går igenom den här parametern så skriver du ut alla mellanliggande resultat samt innehållet i variablerna med ett SELECT- eller PRINT uttryck, och när en 0 går igenom så skriver du inte ut någonting. På så sätt kan du snabbt genomföra en debugging av dina lagrade procedurer, utan att behöva lägga till eller ta bort dina SELECT/PRINT uttryck varken före och efter en sådan här felsökning.
  
• Anropa inte funktioner upprepade gånger inom dina lagrade procedurer, triggers, funktioner eller batches. Om du t ex behöver en längden på en sträng på flera ställen i din procedur så kan du istället för att anrop LEN funktionen varje gång den behövs, anropa LEN funktionen en gång, och sedan lagra resultatet i en variabel för senare bruk.
  
• Försäkra dig om att dina lagrade procedurer alltid returnerar ett värde som indikerar på dess status. Effektivisera de returnerade värdena från de lagrade procedurerna för framgång eller falleranden. RETURN uttrycket används bara för att returnera exekveringsstatusen, inga data returneras. Om du vill att data ska returneras så får du använda OUTPUT parametrarna.
  
• Om din lagrade procedur alltid returnerar en ensam rad med resultatset, så bör du överväga att returnera resultatsetet med OUTPUT parametern istället för SELECT uttrycket. Det beror på att ADO hanterar output-parametrar mycket snabbare än resultatseten från SELECT uttrycken.
  
• Kontrollera alltid den globala variabel @@ERROR omedelbart att ha exekverat ett datamanipulerande uttryck (såsom INSERT/UPDATE/DELETE), så att du kan rulla tillbaka transaktionen ifall ett Error uppstod (vid Error så står @@ERROR värdet på mer än ”0”). Det här är viktigt, eftersom inte SQL Server automatiskt rullar tillbaka några förändringar i en transaktion, om ett specifikt uttryck fallerade. Det beteendet kan du däremot ändra på genom att exekvera kommandot SET XACT_ABORT ON. Variabeln @@ROWCOUNT spelar också en viktigt roll när det gäller att bestämma hur många poster som påverkades av datamanipuleringsuttrycket (eller uthämtningsuttrycket). Baserade på dessa siffror så kan du välja hur långt du vill rulla tillbaka en specifik transaktion.
  
• För att göra dina SQL-satser mer läsbara så bör du börja varje ny klausul på en ny rad och indentera när det behövs. Se här:
  
  
SELECT title_id, title
FROM titles
WHERE title LIKE '%Computer%' AND
title LIKE '%cook%'

  
  
• Trots att vi överlevde millennieskiftet så bör du alltid lagra datum med fyrsiffriga årtal (speciellt då du använder kolumner med datatyperna cCHAR eller INT), istället för tvåsiffriga årtal. På så sätt kan du undvika förvirring och problem. Det här är dock inga problem då du använder DATETIME kolumner, eftersom de även lagrar århundraden trots att du bara har angivit ett tvåsiffrigt årtal. Men det är alltid en bra övning att alltid specificera ett fyrsiffrigt årtal även med DATETIME kolumner.
  
• Precis som med alla andra programmeringsspråk så bör du varken använda GOTO eller snåla med kodning. Överdriven användning av GOTO kan leda till att koden blir svårläslig, det blir svårt att förstå vad som händer i koden.
  
• Glöm inte att tvinga fram unika restriktioner på alternativa nycklar.
  
• Var alltid enhetlig när du använder case (uCase(lCase) i dina koder. På en case-känslig Server så kanske din kod fungerar perfekt, men det kommer att fallera på en case-känslig SQL Server om din kod inte är enhetlig med case. Om du t ex skapar en tabell i SQL Server eller i en databas, som har en case-känslig eller binär sorteringsordning, så måste alla referenser till tabellen ha samma case som specificerades i CREATE TABLE uttrycket. Om du namnger din tabell ’MinTabell’ i CREATE TABLE uttrycket men skriver ’mintabell’ i SELECT uttrycket. så kommer du att få ett ’object not found’ Error.
  
• Trots att inte T-SQL har något begrepp om konstanter (som de i C-språket), så kan variabler tjäna samma syfte. Genom att använda variabler istället för konstanta värden i dina SQL-satser, så kan du förhöja läsbarheten och uppehållet i din kod. Se över följande exempel:
  
SELECT OrderID, OrderDate
FROM Orders
WHERE OrderStatus IN (5,6)

  
  Samma SQL-sats kan skrivas om till något mer läsbart, se nedan:
  
DECLARE @ORDER_DELIVERED, @ORDER_PENDING
SELECT @ORDER_DELIVERED = 5, @ORDER_PENDING = 6

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderStatus IN (@ORDER_DELIVERED, @ORDER_PENDING)

  
  
• Använd inte kolumnnumret i ORDER BY klausulen. Se över följande exempel, där den andra satsen är mer förståelig än den första:
  
SELECT OrderID, OrderDate
FROM Orders
ORDER BY 2



SELECT OrderID, OrderDate
FROM Orders
ORDER BY OrderDate

  

1 Kommentarer

1. 
   Robert Johansson
   

   3 feb 2006

   Jag tycker att denna artikel var bra. Det är sådana artiklar som saknas. Det ger information om hur en "riktigt" programmerare gör och inte det som görs i skolsalen! Kanonbra