Forum Mjukvara Programmering och digitalt skapande Tråd Inlägg

SQL och indexproblem

1
Skriv svar
Permalänk
Medlem
●

SQL och indexproblem

Brottas lite med en sql-fråga som inte vill använda befintliga index, orsaken är den här biten:
.. AND xxxId IN (SELECT xxxId FROM Y WHERE bla=@bla)
Den totalsabbar all prestanda i frågan (som i övrigt i sig inte är speciellt komplex). Vet inte om det är för svårt att ge några tips utifrån detta men nån som kan ha nån idé hur man kan göra för att dra nytta av befintliga index?

Visa signatur

Dator: MB: Gigabyte B760 Gaming X | CPU: Intel Core i7 14700 2.1 GHz 61MB | RAM: Corsair 32GB (2x16GB) DDR5 4800MHz CL40 Vengean | Grafikkort: ASUS Dual GeForce RTX 4060 EVO 8GB OC | Chassi: be quiet! Pure Base 600 Silver | PSU: Corsair RM750e ATX 3.0 750W V3 | Optisk: ASUS BW-12B1ST Blu-Ray/DVD brännare | CPU-kylare: Thermalright Peerless Assassin 120 SE | Operativ: Windows 11 | Scanner: Canon Canoscan 9000F
Övrigt: Nintendo Switch, NES Mini, SNES Mini, Nintendo New 3DS, NES, Famicom AV, PS3, PS5, AppleTV 4K, Synology NAS DS923+ (32GB), iPhone 16 Pro 128GB, LG OLED 55C2,

Redigera
Citera flera Citera
Permalänk
Medlem ★
●

Du ger ett halvt query som exempel och väntar dig svar? Tjena... Fiddla upp ett konkret exempel. https://www.db-fiddle.com/

Visa signatur

Spela Swemantle! Du vet att du vill.

Ibland har jag fel, men då är det någon annans fel.

Redigera
Citera flera Citera
Permalänk
Medlem ★
●

Detta kanske?
https://stackoverflow.com/questions/17715521/why-index-is-not...

Framgår inte vad du kör, men är det MSSQL/Sql server management studio så kan du köra en execution plan för att försöka förstå vad som faktiskt händer:
https://docs.microsoft.com/en-us/sql/relational-databases/per...

Borde finnas liknande grejor i andra miljöer/IDE antar jag.

Redigera
Citera flera Citera
Permalänk
Medlem
●

StackOverflow-länken var bra men jag fick det inte att fungera med EXISTS iallafall. Här är kompletta sql-koden, något obfuskerad dock. Det är alltså sista biten som ställer till det.. Har kollat lite på execution-plan (jag använder Microsoft SQL Server förövrigt) men jag är inte tillräckligt händig på att fatta alla parametrar där (även om jag fattar att den inte använder nått index).
SELECT
S,
RId
FROM [N]
WHERE S > @lS
AND NOT (UId = @UId AND Ut = @Ut)
AND RId IN (SELECT RId FROM SS WHERE SSId= @SSId)

Senast redigerat
Visa signatur

Dator: MB: Gigabyte B760 Gaming X | CPU: Intel Core i7 14700 2.1 GHz 61MB | RAM: Corsair 32GB (2x16GB) DDR5 4800MHz CL40 Vengean | Grafikkort: ASUS Dual GeForce RTX 4060 EVO 8GB OC | Chassi: be quiet! Pure Base 600 Silver | PSU: Corsair RM750e ATX 3.0 750W V3 | Optisk: ASUS BW-12B1ST Blu-Ray/DVD brännare | CPU-kylare: Thermalright Peerless Assassin 120 SE | Operativ: Windows 11 | Scanner: Canon Canoscan 9000F
Övrigt: Nintendo Switch, NES Mini, SNES Mini, Nintendo New 3DS, NES, Famicom AV, PS3, PS5, AppleTV 4K, Synology NAS DS923+ (32GB), iPhone 16 Pro 128GB, LG OLED 55C2,

Redigera
Citera flera Citera
Permalänk
Medlem
●

Det framgår inte vilka index som finns och vilka kolumner som har unika värden. Försök använda inner join istället.

Skrivet av oRBIT2002:

AND RId IN (SELECT RId FROM SS WHERE SSId= @SSId)

Gå till inlägget

Är SSId primärnyckel i tabellen SS? I så fall kan du välja ut ett unikt värde @RId från SS och ersätta med
AND RId = @RId
Om @RId är unikt även i tabellen N är det toppen.

Redigera
Citera flera Citera
Permalänk
Medlem
●

Både RId samt SSId är unika nycklar i den tabellen. Lite osäker hur jag ska skriva. :/
Byter jag ut det mot en inner join i stil med nedan så används inte indexet iallafall.

INNER JOIN [SS] s ON
s.RId= n.RId
AND
s.SSId = @SSId

Senast redigerat
Visa signatur

Dator: MB: Gigabyte B760 Gaming X | CPU: Intel Core i7 14700 2.1 GHz 61MB | RAM: Corsair 32GB (2x16GB) DDR5 4800MHz CL40 Vengean | Grafikkort: ASUS Dual GeForce RTX 4060 EVO 8GB OC | Chassi: be quiet! Pure Base 600 Silver | PSU: Corsair RM750e ATX 3.0 750W V3 | Optisk: ASUS BW-12B1ST Blu-Ray/DVD brännare | CPU-kylare: Thermalright Peerless Assassin 120 SE | Operativ: Windows 11 | Scanner: Canon Canoscan 9000F
Övrigt: Nintendo Switch, NES Mini, SNES Mini, Nintendo New 3DS, NES, Famicom AV, PS3, PS5, AppleTV 4K, Synology NAS DS923+ (32GB), iPhone 16 Pro 128GB, LG OLED 55C2,

Redigera
Citera flera Citera
Permalänk
Medlem
●

Vi behöver lite mer information för att hjälpa till.
Hur ser index ut?
Vilka datatyper används?

Redigera
Citera flera Citera
Permalänk
Medlem
●

På tabellen N sitter det index på: RId (uniqueidentifier), UId (uniqueidentifier) samt Ut(int). PK är S (bigInt)
På tabellen SS är det index på RId, SSId (uniqueidentifier). PK är en kombination av SSId samt RId.

Visa signatur

Dator: MB: Gigabyte B760 Gaming X | CPU: Intel Core i7 14700 2.1 GHz 61MB | RAM: Corsair 32GB (2x16GB) DDR5 4800MHz CL40 Vengean | Grafikkort: ASUS Dual GeForce RTX 4060 EVO 8GB OC | Chassi: be quiet! Pure Base 600 Silver | PSU: Corsair RM750e ATX 3.0 750W V3 | Optisk: ASUS BW-12B1ST Blu-Ray/DVD brännare | CPU-kylare: Thermalright Peerless Assassin 120 SE | Operativ: Windows 11 | Scanner: Canon Canoscan 9000F
Övrigt: Nintendo Switch, NES Mini, SNES Mini, Nintendo New 3DS, NES, Famicom AV, PS3, PS5, AppleTV 4K, Synology NAS DS923+ (32GB), iPhone 16 Pro 128GB, LG OLED 55C2,

Redigera
Citera flera Citera
Permalänk
Medlem
●
Skrivet av oRBIT2002:

På tabellen N sitter det index på: RId (uniqueidentifier), UId (uniqueidentifier) samt Ut(int).

Gå till inlägget

Jag tolkar det som att det är tre separata "smala" index - ett index för varje kolumn. Då kanske query-optimizern resonerar i stil med "även om jag använder indexet på RId för att få fram rätt rader måste jag göra en massa key lookups, och det är en massa andra villkor, så jag gör en linjär sökning genom hela tabellen N".

Skrivet av oRBIT2002:

På tabellen SS är det index på RId, SSId (uniqueidentifier). PK är en kombination av SSId samt RId.

Gå till inlägget

(SSId, RId) är PK i den ordningen?

Om du absolut vill använda index på RId kan du dela upp queryn i tre små separata queries (mellanlagra resultat i temptabeller eller tabellvariabler, om det inte är för många rader):
1. SELECT RId FROM SS WHERE SSId= @SSId (förhoppningsvis inte för många rader).
2. Inner join av resultatet från 1. med N.
3. Filtrera resultatet från 2. på UId, Ut och S.

Redigera
Citera flera Citera
Permalänk
Medlem
●

+1 på den.
Eventuellt en konstruktion med en cte.

Men är datatyperna samma för kolumnen och variabeln?

Skrivet av Xenoo:

Jag tolkar det som att det är tre separata "smala" index - ett index för varje kolumn. Då kanske query-optimizern resonerar i stil med "även om jag använder indexet på RId för att få fram rätt rader måste jag göra en massa key lookups, och det är en massa andra villkor, så jag gör en linjär sökning genom hela tabellen N".
(SSId, RId) är PK i den ordningen?

Om du absolut vill använda index på RId kan du dela upp queryn i tre små separata queries (mellanlagra resultat i temptabeller eller tabellvariabler, om det inte är för många rader):
1. SELECT RId FROM SS WHERE SSId= @SSId (förhoppningsvis inte för många rader).
2. Inner join av resultatet från 1. med N.
3. Filtrera resultatet från 2. på UId, Ut och S.

Gå till inlägget
Redigera
Citera flera Citera
Permalänk
Medlem
●

Mecka med temptabeller kändes spontant som en potentiell prestandasänkning?

Visa signatur

Dator: MB: Gigabyte B760 Gaming X | CPU: Intel Core i7 14700 2.1 GHz 61MB | RAM: Corsair 32GB (2x16GB) DDR5 4800MHz CL40 Vengean | Grafikkort: ASUS Dual GeForce RTX 4060 EVO 8GB OC | Chassi: be quiet! Pure Base 600 Silver | PSU: Corsair RM750e ATX 3.0 750W V3 | Optisk: ASUS BW-12B1ST Blu-Ray/DVD brännare | CPU-kylare: Thermalright Peerless Assassin 120 SE | Operativ: Windows 11 | Scanner: Canon Canoscan 9000F
Övrigt: Nintendo Switch, NES Mini, SNES Mini, Nintendo New 3DS, NES, Famicom AV, PS3, PS5, AppleTV 4K, Synology NAS DS923+ (32GB), iPhone 16 Pro 128GB, LG OLED 55C2,

Redigera
Citera flera Citera
Permalänk
Medlem
●
Skrivet av oRBIT2002:

Mecka med temptabeller kändes spontant som en potentiell prestandasänkning?

Gå till inlägget

Det behöver det nödvändigtvis inte vara även om det kanske inte är en ”snygg” lösning

Hur blir resultatet med with (recompile) / with (index ...)?

Frågar igen; Vad är det för datatyper inblandat?

Redigera
Citera flera Citera
Permalänk
Medlem
●

S = int
Ut = int
Uid = uniqueidentifier
SSID = uniqueidentifier

Har inte testat recompile with index.

Visa signatur

Dator: MB: Gigabyte B760 Gaming X | CPU: Intel Core i7 14700 2.1 GHz 61MB | RAM: Corsair 32GB (2x16GB) DDR5 4800MHz CL40 Vengean | Grafikkort: ASUS Dual GeForce RTX 4060 EVO 8GB OC | Chassi: be quiet! Pure Base 600 Silver | PSU: Corsair RM750e ATX 3.0 750W V3 | Optisk: ASUS BW-12B1ST Blu-Ray/DVD brännare | CPU-kylare: Thermalright Peerless Assassin 120 SE | Operativ: Windows 11 | Scanner: Canon Canoscan 9000F
Övrigt: Nintendo Switch, NES Mini, SNES Mini, Nintendo New 3DS, NES, Famicom AV, PS3, PS5, AppleTV 4K, Synology NAS DS923+ (32GB), iPhone 16 Pro 128GB, LG OLED 55C2,

Redigera
Citera flera Citera
Permalänk
Medlem
●

Tänk på att du bara kommer använda ett index per tabell. Jag tippar på att query plannern väljer din primary key "S" på [N] och därefter så gör den en scan på alla rader som matchar. Testa att lägga till ett composite index (S, RId) på [N].

För att få så bra hjälp som möjligt så hade det bästa varit om du skapade en fiddle, som LemonIllusion, föreslog tidigare, eller om du åtminstone klistrade in query för att återskapa tabellerna med fulltständiga index.

Output från en explain hade ju varit bra också för att bl.a. se storleksordningen (kardinaliteten) på tabeller och index. Oftast så beter det sig väldigt olika beroende på om det är 1000 eller 100 miljoner rader som ska sökas igenom.

Som Xenoo antydde så är det väldigt viktigt att du har rätt ordning på kolumnerna i dina composite indexes. Om din primary key har ordningen (RId, SSId) så kommer den aldrig användas om du bara filtrerar på SSId.

Redigera
Citera flera Citera
Permalänk
Medlem
●

Ska kika på det där rörande fiddle... Jag vill verkligen bli bättre på det här med index, det är inte helt självklart när man googlar oftast. Jag har väl nån form av grundläggande kunskap men vid knepigare frågor, ja, då blir det lite gissande bitvis.

Visa signatur

Dator: MB: Gigabyte B760 Gaming X | CPU: Intel Core i7 14700 2.1 GHz 61MB | RAM: Corsair 32GB (2x16GB) DDR5 4800MHz CL40 Vengean | Grafikkort: ASUS Dual GeForce RTX 4060 EVO 8GB OC | Chassi: be quiet! Pure Base 600 Silver | PSU: Corsair RM750e ATX 3.0 750W V3 | Optisk: ASUS BW-12B1ST Blu-Ray/DVD brännare | CPU-kylare: Thermalright Peerless Assassin 120 SE | Operativ: Windows 11 | Scanner: Canon Canoscan 9000F
Övrigt: Nintendo Switch, NES Mini, SNES Mini, Nintendo New 3DS, NES, Famicom AV, PS3, PS5, AppleTV 4K, Synology NAS DS923+ (32GB), iPhone 16 Pro 128GB, LG OLED 55C2,

Redigera
Citera flera Citera
Permalänk
Medlem
●

Prova att få med hela primärnyckeln where satsen, typ where RiD=RiD and Ssid=@Ssid för att optimizern ska träffa hela indexet.

Redigera
Citera flera Citera
1
Skriv svar