Finde IDer i en tabel der IKKE er i en anden

Sad og søgte lidt på dit spørgsmål, og faldt over denne artikel, hvor der kigges på forskellige strategier, kan være det kan hjælpe:

http://www.dba-oracle.com/t_convert_set_to_join_sql_parameter.htm

Prøv evt. dette:

select a.id from a where a.id not in (select b.id from b where b.id = a.id)

Næsten det samme som din query, med den forskel, at not in querien højst kan returnere 1 række.

Alternativt kan du bruge denne join query:

select a.id from a left join b on a.id = b.id where b.id = null

Den hiver alle de rækker ud fra joinet hvor der ikke var en værdi for b. Burde også virke.

Spændt på at høre om det hjælper på performance.

nummer 2 skulle være:


select a.id from a left join b on a.id = b.id where b.id is null

altså IS i stedet for =

Det giver ikke noget, jeg håbede der var en eller anden indbygget funktionalitet der kunne bruges smart her... Det er faktisk langsommere at joine end det er at lave en inner select... ved ikke hvorfor?

/Jonas

#svarrenabe
Det kan der være mange grunde til ... og uden at kende query og struktur på begge så vil det kun være gæt.

Query optimizeren gætter jo på hvad den tror er bedst og er dine statistics og måske index ikke i orden, så kan det gå galt.

mvh

#svarrenabe
Btw, hvad mener du med "Det giver ikke noget" ?

Og jo, der er indbyggede funktioner som kan bruges, som ikke til så simple ting som er sørge for at ens struktur er iorden. Så kunne der jo laves ting for alt :-)

Der kan gøres mange mange ting...

Først, hvor mange rækker snakker vi om?

Noget andet....
select ID from Table1
where ID not in (select ID from Table2)

Vil altid give en full table scan som jo af naturlige årsager tager tid når der er mange rækker i Table1, der gælder det samme for Table2. Dvs. glem alt om din indeksering, den kommer aldrig i sving.


#Frederik -> det samme gælder ved left joins ;)
#svarrenabe -> "Det er faktisk langsommere at joine end det er at lave en inner select... ved ikke hvorfor?" - Du mener subselect :) Og både ja og ned som buzzz påpeger så kan det skyldes flere ting, men forskellen burde være den samme hvis strukturen er i topshape.


Nåmen... hvor mange rækker? Og hvilket setup kører du med? Er det bare noget du ikke gider vente på, eller skal det bruges i produktion? Hvor lang tid tager det nu og hvor vil du gerne hen? Hvor mange fysiske diske/ logiske diske råder du over? Og hvordan er de formateret?

Der kan gøres mange mange ting...

Først, hvor mange rækker snakker vi om?

Noget andet....
select ID from Table1
where ID not in (select ID from Table2)

Vil altid give en full table scan som jo af naturlige årsager tager tid når der er mange rækker i Table1, der gælder det samme for Table2. Dvs. glem alt om din indeksering, den kommer aldrig i sving.


#Frederik -> det samme gælder ved left joins ;)
#svarrenabe -> "Det er faktisk langsommere at joine end det er at lave en inner select... ved ikke hvorfor?" - Du mener subselect :) Og både ja og ned som buzzz påpeger så kan det skyldes flere ting, men forskellen burde være den samme hvis strukturen er i topshape.


Nåmen... hvor mange rækker? Og hvilket setup kører du med? Er det bare noget du ikke gider vente på, eller skal det bruges i produktion? Hvor lang tid tager det nu og hvor vil du gerne hen? Hvor mange fysiske diske/ logiske diske råder du over? Og hvordan er de formateret?

Dit dataset og din datamængde, er det noget der ofte bliver ændret i form af insert, update eller delete?

For hvis den er mere statisk og konstant, så kan du overveje at bruge et view eller et Materialized view. Det vil bringe hastigheden betydeligt ned.

Ellers, har du prøvet at lave indexes for at optimere dine queries?

public2-> læs lige hvad jeg skriver, det handler ikke om indeksering i de queries der er foreslået indtil videre.

Alt afhængig af svarrenabes svar kan vi spore os ind på hvad der skal til, om det ender ud med et indexeret view... tjaa måske :)  udmærket forslag faktisk - (også kaldet materialized view i oracle)
Men lad os vente med at gætte, vi mangler rigtigt mange svar førend vi kan hjælpe ham.

Fra 2005 kan du bruge:
SELECT productTypeId, name, price FROM NewProducts
EXCEPT
SELECT productTypeId, name, price FROM Products

teepee-> Except giver da indexscan på samme måde som alle de andre forslag = Ingen forskel :)

Har du lavet en explain plan for begge løsninger? Iøvrigt kommer man jo ikke udenom at skulle se på alle data, når man nu engang skal finde forskellen, doh!

Alt efter hvad det skal bruges til og hvor ofte der bliver ændret i datasættet, så kunne man overveje notationen om denormalisering til at finde de ID'er.

Men det kræver jo noget mere viden om brugen og det "flow" der er i datasættet og ikke mindst tabel strukturene i databasen

Hej igen

Janus, svar på dine spørgsmål:

Det drejer sig om to tabeller i hver sin database, som er linket imod hinanden med GUIDer.. Der er omkring 30-50 mill rækker i hver tabel, og der er stor udskiftning i dataene. Alle data bliver skiftet løbende ca. hver 4 uge. Der er 1:1 relation mellem de to tabeller, og jobbet der skal udføres er, at der løbende skal slettes data i begge tabeller.

De 2 databaser er hhv en produktionsdatabase der er relativt let at vedligeholde, og hvor der kører et oprydningsjob der sletter produktionsdata når de ikke længere skal bruges længere. Den anden database er en enormt tung database der indholder billeder, så derfor ligger den for sig selv, på nogle andre fysiske drev.

Det job jeg skal lave skal "synkronisere" data mellem de to databaser, dvs. det skal slette de data der ikke længere har referencer fra den anden database.

Systemet kører i drift, og vedligeholdelsesjobbet må ikke låse databaserne i længere tid.

Den simple select jeg har startet ud med tager 10-12 minutter, og det er med friske indexes på GUID kolonnerne i begge databaser

Formateringen på alle diskene er NTFS, kan det have indflydelse?

Håber det udfylder nogle af hullerne...

Jeg er også åben overfor andre løsninger, men jeg har overtaget opgaven fra en der havde sat en trigger på produktionsdatabasen til at slette i billeddatabasen, og det er IKKE løsningen skal jeg hilse og sige...

/Jonas

Hardware er helt klart en måde at forbedre ydelse, især RAM og Harddisk type kan have en indvirkning. Flere ram betyder (eller kan betyde) færre I/Os og en harddisk af SSD type kan være hurtigere end de almindelige harddiske.

Dog er Hardware og filsystem ikke de største og første områder at kigge på optimering og tuning af en database.

Du siger alt data bliver udskiftet hver 4. uge. Deraf antager jeg at de er statiske i fire uger af gangen.

Når disse data bliver rullet ind, der kan man lave mekanismer der med det samme tjekker op på de her betingelser og ID'er som du ønsker.

Du kan evt. bruge min notation om denormalisering jeg har skrevet oppe og simpelthen tage alle ID'er du vil vælge, og sætte dem i en tabel for sig selv.
Det nedbryder idéen om normalisering, men det er også derfor det er kaldet for denormalisering.

Du kan lave triggers, du kan lave "regler" for din data der bliver indrullet.

Noget andet kan være at når alt data er sat ind, så at køre en query én gang (der måske tager noget tid, men kun gør det én gang) som laver et materialized view med alle de ID'er du skal bruge, hvorefter du senere blot kan gennemgå dit view.

Nogle gange kan man ikke komme uden om at vedligeholdelse eller søgning tager tid, men kan man gøre det få gange og på tidspunkter der er mest gunstige, så er det klart at foretrække.

Du kan også få en applikation (f.eks. Python-script) til at gennemgå din database og på den måde separere din work-load på databasen og sprede den ud.

Desværre er der løbende dataudskiftning... der kommer hele tiden nye data ind, og gamle data bliver slettet, data er gamle nok til at blive slettet når de er ca. 4-5 uger gamle, så jeg tror ikke at views kan bruges.
Tanken er at oprydningen skal køre relativt ofte (hver 10. minut), for at håndtere så få data som muligt af gangen, men hvis en bedre løsning er at køre en gang i døgnet vil det også kunne bruges, jeg håber dog stadig på at der findes en løsning der ikke spærrer databaserne mens dataene findes frem.

/Jonas

Hej Jonas

Det er selvfølgelig uheldigt at tabellerne indeholder en guid, men mon ikke vi løser det alligevel.

Jeg hæfter mig lige ved dette :
1) "Alle data bliver skiftet løbende ca. hver 4 uge", vil det sige at de får en slags timestamp eller kan få?
2) Data må ikke låses imens jobbet kører.

Filsystemet og clustersize er af allerhøjeste betydning for hvordan databasen performer. Et forkert clustersize kan teoretisk reducere performance med faktor 16 *GISP*
- Er det en Windows Server 2008?
- Du skal sikre dig at diskene er formateret med 64kb clustersize.

NB: Timestamp må ikke forveksles med rowversion :), vi snakker helt konkret... en datetime type :)

public2-> python? Det er vidst noget php-halløj, vi har med en SqlServer at gøre :), hvad ville du lave i python?
Triggers er blevet nævnt af svarrenabe til ikke at være vejen frem, hvorfor foreslår du det?
Jeg forstår ikke din notation om denormalisering? Hvad mener du helt præcist? Kan være jeg misforstår noget godt :)
SSD er fantastisk til læsning, men forfærdeligt til skrivning.
- Det virker ikke helt som om vi samarbejder om at finde den bedste løsning, men mere at du kommer med et hav af forslag uden at kende til systemet!

Hvis dit primære problem er, at du ikke vil/må låse tabellen i længere tid, ville jeg gøre følgende:

1 gang i døgnet ville jeg lave en select, som giver alle de id'er som du vil slette fra den pågældende tabel. Disse id'er ville jeg gemme en en temp-tabel 1:

select ID into #tmp1 from Table1
where ID not in (select ID from Table2)

Dernest vil jeg lave en ekstra tmp-tabel:

create table #tmp2 ( id uniqueidentifier)

og så vil jeg lave en loop, som sletter fx 100 rows ad gangen:

declare @AntalDerSkalSlettes int
set @AntalDerSkalSlettes = (select count(*) from #tmp)

WHILE (@AntalDerSkalSlettes > 0)
BEGIN
  INSERT INTO #tmp2 (id)
  SELECT TOP 100 id FROM #tmp

  DELETE FROM Table1 WHERE id IN (SELECT id FROM #tmp2)
  --Ovenstående bør være ok hurtig, hvis der er index på id-kolonnen i Table1. Dermed vil tabellen kun låses i meget korte perioder.

  --Fra #tmp sletter vi nu de 100 rows som ER blevet slettet
  DELETE FROM #tmp WHERE id in (select id from #tmp2)

  --Vi tømmer #tmp2, så den er klar til næste loop.
  TRUNCATE TABLE #tmp2

  --vi tæller variablen 100 ned, så while-løkken ikke kører uendeligt.
  SET @AntalDerSkalSlettes = @AntalDerSkalSlettes - 100
END

Idéen er, at udvælgelsen af de rækker der skal slettes, nu kun laver en select, hvilket ikke bør låse din tabel (ellers bør du overveje at slå read_committed_snapshot til på dine databaser).
Dernæst plukkes en lille mængde ud i en temp-tabel, og så sletter du denne lille delmængde. Ved at delmængden er tilpas lille, kan man nu drage nytte af dit index. Og selvom while-løkken måske kører længe, så vil alle delete's være små transaktioner, og dermed vil live-miljøet ikke blive så slemt berørt af locking issues.

øhh sjang...

select ID into #tmp1 from Table1
where ID not in (select ID from Table2)

Er jo netop det som tager tid, og hvis det skal køre hver time så.... og måske endnu oftere, det er en full table scan på 30-50mill rækker!

Måske du kan bruge noget som dette

SELECT Tabel1.Id, Tabel1.Felt1
FROM Tabel1 LEFT JOIN Tabel2 ON Tabel1.[Id] = Tabel2.[Id]
WHERE (((Tabel2.Id) Is Null));

Hilsen

Måske du kan bruge noget som dette

SELECT Tabel1.Id, Tabel1.Felt1
FROM Tabel1 LEFT JOIN Tabel2 ON Tabel1.[Id] = Tabel2.[Id]
WHERE (((Tabel2.Id) Is Null));

Hilsen

janus_007: I know. Men det kommer man jo bare ikke udenom. Hvis man er nødt til at sammenligne 2 store datamængder, så kan man ikke forvente at det kan klares på 1 sek - det vil være utopi.

Mit forslag gik på at undgå locking issues, så i stedet for én stor delete transaktion, som låser tabellen i mere end 10-15 minutter, så laver den nu kun én tung select, og så mange bitte små delete transaktioner.

Jeg kan ikke umiddelbart se et problem I kun at køre oprydning én gang i døgnet, men det kan være jeg misser noget.

Har vi egentlig hørt noget om hvordan det job der sletter selve produktionsdataene ser ud? Måske man er ude i en kombi-øvelse. Hvis de rækker der skal slettes fra den første tabel er relativt hurtige at udvælge, så burde men ved at bruge loop-teknikken hurtigt kunne udvælge små chunks fra billedtabellen også, så man hele tiden sletter først 100 rows i den første tabel, så de tilhørende rows i den anden tabel, og så looper igen. Evt. med endnu mindre chunks fra den første tabel.

Jeg begynder at indse at der nok ikke er en 10 sekunders løsning på mit spørgsmål...

Jeg vil dog gerne vide lidt mere om hvad den her Clustersize på diskene betyder, den er pt på 4K... vil man kunne fremme performance ved at få det ændret?

Jeg begynder at hælde til en løsning der skal køre et bestemt tidspunkt hvert døgn(hvis det er muligt rent produktionsmæssigt). For når sammenligningen kræver fuld table scan på begge databaser hver gang, så er det nok det der tager længst tid, og ikke om der skal slettes 2000 eller 200.000 rækker.

Kan man ikke trække dataene ud uden at låse databasen imens? Uden at risikere at læse uncommitede data vel og mærke!

Hvis jeg bruger kommandoen with(nolock) på tabellerne kan jeg vel risikere at data ikke er committed, eller hvad?

/Jonas

Jo det kan du godt, ved at slå read committed snapshot til på din database. Det kaldes også "row level versioning", og gør at du netop kan læse kun committed data (dvs ingen dirty reads), selvom en update er i gang et andet sted. Det sætter lidt mere pres på TempDB, men ikke noget jeg nogensinde har set som et problem.

Det gøres ved at sige:

ALTER DATABASE DinDatabase
SET READ_COMMITTED_SNAPSHOT ON

Databasen må dog ikke have active connections mens du gør det, og det kan være du bliver nødt til at sætte databasen i single user mode først. Så dette skal gøres mens du har et lille service vindue.

Selve operationen at slå read committed snapshot til, er en lille hurtig operation, så den skal ikke til at læse alle data eller noget.

Hej Jonas

4K er en GRANDE fejl :) og kan have stor indflydelse på hvor hurtigt databasen arbejder. Diske til data SKAL være formateret med 64kb!

Du svarede aldrig på om der var en lastupdated column? hvis der er noget sådant så kan du bruge table partitioning og 100% undgå locking.

Hmmm, så kan det alligevel godt betale sig at lytte med :-)

Men det er nok mest til Janus dette lille addon spm, da han er inde på det med BlockSize.

Nu er diske jo per default sat til 4k som block size. Men hvad er det som bliver hurtigere? Synes aldrig jeg er stødt på nogen steder hvor der står noget om at man bør sætte den til 64k. Jeg kan godt se logikken i det, da der ikke findes små filer ... og derfor kan man ligeså godt bruge 64k blokke.

Men har du noget der støtter op omkring det her, artikler, performance ... for så burde jeg da klart overveje at lave en større omrokering af data mens det alligevel er ferie, man kan jo ligeså godt få det meste ud af hardwaren.

Nu er denne vist blevet syltet længe nok, sorry...

Jeg fik aldrig løst problemet med de foreslåede løsninger, men dem der har lagt svar får lov at dele points, håber det er ok...

Tak for deltagelsen