Optimera SQL, matcha datum mha MAX

[sundberg76]

Jag hoppas att det finns någon som vet ett sätt att få fram den här informationen snabbare. 

Beskrivningen är något förenklad, men kärnfrågan är kvar; Hur kan jag optimera SQL-syntaxen så att frågan körs snabbare. Jag antar att det framförallt är kruxet med att det bara finns ett startdatum i tabellen Categories som gör att det tar tid.

Idag körs frågorna i Access, men möjlighet finns att helt eller delvis ta Oracle-db till hjälp (t.ex. exportera data nattetid till en tabell.. kanske Categories med från- och tilldatum om det skulle underlätta).

 

Jag har en databas med tre tabeller

UserData

UserHistory

Categories

 

i UserData finns följande kolumner:

UserName & UserID

ex:

John, 12345
Billy, 54321 

i Categories finns följande kolumner

UserID, Category, AsOf, Data

ex:

12345, color, 1000, blue
12345, color, 1500, white
12345, height, 1200, 60
54321, color, 600, red
54321, height, 800, 18

AsOf är antalet dagar från 1990-01-01 (1000 är alltså: 1992-09-27)

 

I UserHistory finns följande kolumner

UserName, UHDate,  & Color(som skall uppdateras/populeras).

ex:

John, 2015-10-01,
John, 2015-10-02,
John, 2015-10-03,

För varje datum vill jag veta vilket värde personen har (i det här fallet i kategorin "color"). I ovanstående exempel blir det

12345, 2015-10-01, white
12345, 2015-10-02, white
12345, 2015-10-03, white

eftersom det värde som gäller för dessa datum i tabellen Categories för 12345, John är white. 

 

Syntaxen som den ser ut idag är som följer:

UPDATE UserData AS UD, UserHistory AS UH, Categories AS CAT SET UH.Color = CAT.Data
WHERE CAT.UserID = UD.UserID AND
UD.UserName = UH.UserName AND CAT.Category = "Color" AND
CAT.AsOf = (SELECT MAX(CAT2.AsOf) FROM Categories AS CAT2 WHERE CAT.UserID = CAT2.UserID AND CAT2.AsOf <= DateDiff('d', #1/1/1990#,UH.UHDate) AND CAT2.Category = "Color");

Den här SQL'en ingår tillsammans med 9 st liknande i en rapport och den tar 8-10 timmar att ta fram. Ovanstående exempel går relativt snabbt - 8 minuter Baserat på 738 records i UserData, 46633 records i Category och 22333 records i UserHistory.

 

Vore ytterst tacksam om någon kunde bistå med hjälp eller med något förslag om var man kan vända sig.

 

Hälsningar

Joel

[Mr Andersson]

Det första du ska göra är att skapa index för de fält du gör sökningar på.

[sundberg76]

Det första jag ska göra är att kyssa dina fötter!

Från 2½ timme till <1 minut på en av frågorna!

Ett herrejistanes stort tack till dig, Mr Andersson! =))

 

Nu skall jag bara komma på hur jag får till indexering per automagik;

Somliga tabeller skapas via länkade tabeller från Oracle. ex:

SELECT lnk_UserContracts.* INTO UserContractsFROM lnk_UserContracts; 

Andra tabeller finns redan i Access-databasen men är tomma (en default-databas kopieras vid start av rapport-programmet) - där borde jag kunna ange indexering.

 

Hälsningar

Joel

[sundberg76]

Och de var ju busenkelt att applicera index på tabellerna genom "CREATE INDEX". Nu tar rapporten c:a 5 minuter att köra. Helt galet!

Ett superduperstort tack Mr Andersson!

 

Hälsningar

Joel

[Mr Andersson]

Tack för alla vänliga ord och kul att det löste sig,