Forskjellige måter å opprette primærnøkler?

[pengolf]

Jeg har laget tabellen:

 

CREATE TABLE plattform (

id int NOT NULL AUTO_INCREMENT PRIMARY KEY,

navn varchar(255),

utgivelsesaar int);

 

Kunne jeg like godt gjort det slik:

 

CREATE TABLE plattform (

id int NOT NULL AUTO_INCREMENT,

navn varchar(255),

utgivelsesaar int);

PRIMARY KEY(id)

 

?

 

Eller er det noen regler for når PRIMARY KEY skal stå på samme linje, og helt til slutt?

Endret 20. november 2007 av pengolf

[Theoneask]

CREATE TABLE plattform (

id NUMBER ( 4,0)

navn VARCHAR (255),

utgivelsesarr INT,

CONSTRAINT plattform_id_pk PRIMARY KEY (id));

 

Slik lager eg mine tabeller.

[Manfred]

I say potato, you say potato... Alle veier fører til Rom, osv...

 

Det er vel strengt tatt ingen forskjell å snakke om, så lenge metoden fungerer på den typen sql server du bruker.

[roac]

Akkurat hvor du setter primary key setningen spiller forsvinnende liten rolle, men det som spiller en rolle er at du kjenner til de valgmulighetene du har på primærnøkkelen, samt hva som er standardvalg. Ta for eksempel i MSSQL:

 

create table epostlog (
 meldings_hash binary(16) not null primary key,
 avsender varchar(120),
 mottaker varchar(120),
 emne varchar(120),
 tid datetime2(0)
)

 

Noen vil sikkert si at en primærnøkkel på 16 byte er for stort, men hvis vi ser bort i fra det, er det noen som ser hva det ytelsesmessige problemet med primærnøkkelen er i dette tilfellet?

[pengolf]

Burde kanskje vært tid/dato som primærnøkkel?

[blackbrrd]

... og for postgres så vil en slik deklarasjon være ok, (la oss si at vi skal lage en tabell ordre med referanse til en kunde tabell som er lagd tidligere)

 

CREATE TABLE order (

orderid serial PRIMARY KEY,

id_contactid integer REFERENCES contact

);

 

Det vil bli lagt en primary key constraint på orderid og en foreign key constraint på id_contactid som vil referere til primary key-en i contact tabellen.

 

Kjappt å skrive, lett å lese

[blackbrrd]

Akkurat hvor du setter primary key setningen spiller forsvinnende liten rolle, men det som spiller en rolle er at du kjenner til de valgmulighetene du har på primærnøkkelen, samt hva som er standardvalg. Ta for eksempel i MSSQL:

 

create table epostlog (
 meldings_hash binary(16) not null primary key,
 avsender varchar(120),
 mottaker varchar(120),
 emne varchar(120),
 tid datetime2(0)
)

 

Noen vil sikkert si at en primærnøkkel på 16 byte er for stort, men hvis vi ser bort i fra det, er det noen som ser hva det ytelsesmessige problemet med primærnøkkelen er i dette tilfellet?

 

Hmmm... aner ikke noe om sql server, men det kan vel være at sammenligning av binary datatyper er tregt? Kanskje binary felter blir lagret utenfor resten av tabellen? Med 64bits prosessorer på 64 bits os med 64 bits programmer så vil all sammenligning av data på mer enn 8 byte ta vesentlig lengre tid enn datatyper på mindre enn 8 byte...

 

Så, gjettet jeg riktig på noen av punktene?

[kaffenils]

Noen vil sikkert si at en primærnøkkel på 16 byte er for stort, men hvis vi ser bort i fra det, er det noen som ser hva det ytelsesmessige problemet med primærnøkkelen er i dette tilfellet?

 

Du tenker vel på at PRIMARY KEY defaulter til CLUSTERED siden det ikke er definert andre UNIQUE constraints som CLUSTERED. Og siden kolonnen sannsynligvis er en hashverdi, fra navnet å dømme, så er jo det ytelsesmessige problemet åpenbart.

 

Må forresten få si at "Tuning og Optimization" kurset jeg er på hos MS er veldig interessant. Kursholder er en "gal" (i positiv forstand) inder som tidligere jobbet i SQL Server utviklingsteamet i Redmond (vel og merke ikke med 2005). Han vet de snodigste ting

[roac]

Kaffenils har helt rett. SQL Server defaulter til clustered primary key, og det åpenbare problemet er at alle dataene blir liggende fysisk sortert etter primærnøkkelen, som er tilfeldig av natur. SQL Server vil derfor bruke mye tid og ressurser på å sortere dataene, og du vil få, som Kimberly Tripp så fint kaller det, a perfectly fragmented database.

[Manfred]

Vil det da strengt tatt bli en bedre ytelse dersom man bruker en pk_id kolonne som er en auto increment, og heller ha hashen som en index i tabellen? Altså, om du skal bruke denne hashen til søking, vel og merke.

[roac]

Vil det da strengt tatt bli en bedre ytelse dersom man bruker en pk_id kolonne som er en auto increment, og heller ha hashen som en index i tabellen? Altså, om du skal bruke denne hashen til søking, vel og merke.

I dette tilfellet, en type loggtabell, ja. Men, du kan også spesifisere at primærnøkkelen skal være nonclustered for å unngå det problemet jeg beskrev. For tabeller som typisk har meget stor andel selects, og dertil lite insert, update og delete, så vil en clustered index være hensiktsmessig, men det fordrer jevnlig defragmentering av databasen. For å slippe dette kan man, som du også nevner, bruke en surrogatnøkkel, en autoinkrementerende verdi.

[kaffenils]

Vil det da strengt tatt bli en bedre ytelse dersom man bruker en pk_id kolonne som er en auto increment, og heller ha hashen som en index i tabellen? Altså, om du skal bruke denne hashen til søking, vel og merke.

 

Hvis du fortsatt skal bruke hashen til oppslag som f.eks. select * from epostlog where epost_hash=0x012345... så vil du få degradert ytelse hvis du i tillegg introduserer en pk_id indentity clustered.

 

Grunnen til dette er at SQL Server vil utføre flere logiske reads enn hvis meldings_hash hadde vært pk nonclustered.

[roac]

Korrekt. Men, for en typisk loggtabell som har stor grad av innsetting, så vil den totalt sett kunne tjene på dette fremfor å ha epost_hash clustret. De ekstra read du snakker om er forbundet med å hente opp raden etter at den er funnet i indeks, ikke sant?

[kaffenils]

Ja. Tenker på at du først må utføre en index seek for å finne epost_hash (2-3 reads avhenging av b-tree dybde) og deretter gjøre et clustered index seek (2-3 reads avhenging av index dypde).

[roac]

Nå begynner det riktignok å bli ganske off topic, men dog... Dette er en problemstilling som vi fort kan komme til å se mer til når LINQ begynner å tas i bruk. LINQ er ganske så fantastisk, men uvettig bruk (les: quick'n dirty kode) kan fort føre til slike problemstillinger. Andre morsomme problemstillinger vi kan se etter hvert kan inkludere at hele recordsettet sendes over nettverket til klienten, for så å behandles der.

Endret 21. november 2007 av roac

[blackbrrd]

Roac: LINQ er såvidt jeg kan se veldig likt NHibnernate som er .Net versjonen av Hibernate?

[kaffenils]

Roac: LINQ er såvidt jeg kan se veldig likt NHibnernate som er .Net versjonen av Hibernate?

 

Har brukt NHibernate og det er maaaange fallgruver en kan gå i. F.eks. å sette lazy read (eller hva det nå het) off. Plutselig skal store objekttrær leses og 1000vis av SELECT statements genereres. Hadde en skrevet SELECTen selv så kunne en gjort det samme med én enkel SELECT. Er blitt veldig skeprit til ORM-ing

[blackbrrd]

kaffenils: skriv HQL (ligner veldig på SQL, men du ender opp med objekt-trær og du kan spesifisere nøyaktig hva du skal hente ut)

 

f.eks:

 

Contact contact = (Contact)session.createQuery("FROM contact a LEFT JOIN FETCH a.orders b WHERE a.contactid = :contactid")

.setParameter("contactid ", 5)

.uniqueResult();

 

Det er forresten så å si de samme fallgruvene man kan gå i med ORM som med SQL, det er bare at du har litt mer erfaring. F.eks SELECT * FROM contact, order er en heller lite heldig select

Endret 21. november 2007 av blackbrrd