[VB/Access] SQL-query sneller?

[Demo]

Op mijn stage moet ik de volgende query sneller maken (wegens klachten dat ons prog te langzaam zou zijn):
SELECT CL_ActionPerson.*,Company.[Company-ID],Company.Company,Company.Address1,Company.Address1Number,Company.Address1Postfix,Company.Telephone,Company.City1,Company.Post1,Action.*,Person.[Person-ID],Person.PersonName,Person.Midname,Person.Capitals,T_ActionType.ActionValue FROM ((((Action LEFT JOIN Project ON Action.[Project-ID] = Project.[Project-ID]) LEFT JOIN Company ON Action.[Company-ID] = Company.[Company-ID]) LEFT JOIN T_ActionType ON Action.[ActionType-ID] = T_ActionType.[ActionType-ID]) LEFT JOIN CL_ActionPerson ON Action.[Contact-ID] = CL_ActionPerson.[Contact-ID]) LEFT JOIN Person ON CL_ActionPerson.[Person-ID] = Person.[Person-ID] WHERE ((((Action.[ActionType-ID]) In (" & sActFilter$ & ")) AND (((Person.[Person-ID]) In (" & sPerFilter$ & ")) AND ((CL_ActionPerson.[Person-ID]) In (" & sPerFilter$ & "))) AND ((Action.Actiondate>=#" & sDateBegin$ & "#) AND (Action.Actiondate<=#" & sDateEnd$ & "#))) AND (Action.Schedule=True)) ORDER BY Action.Actiondate ASC, Action.Actiontime ASC

Ik heb echter geen bal verstand van SQL, dus ik zou niet weten wat ik moet doen om hem sneller te krijgen...

[Phenomenon]

......... Heb je alle velden van CL_actionperson nodig? Kies anders een alleen de velden die je nodig bent. Hoe is de koppeling naar de database? Via odbc? Weet je zeker dat het alleen de query is die langzaam gaat?

[xtra]

Demoniac schreef op 11 November 2002 @ 17:20:
Op mijn stage moet ik de volgende query sneller maken (wegens klachten dat ons prog te langzaam zou zijn):

[...]

Ik heb echter geen bal verstand van SQL, dus ik zou niet weten wat ik moet doen om hem sneller te krijgen...

Ik zou maar eens beginnen met basis SQL en dan verder zoeken op performance tuning etc...
Slotje?

[gorgi_19]

Ligt dat wel aan de query? (owke, het lijkt een onding.. )

Hoeveel gebruikers maken er gebruik van dat programmaatje? Heb je al getest of het specifiek aan deze query ligt? Heb je al indexen in je tabellen gemaakt?
Hoe groot is je database? (in mb en records (bij benadering)

[GarBaGe]

gorgi_19 schreef op 11 november 2002 @ 18:38:
Ligt dat wel aan de query? (owke, het lijkt een onding.. )

Hoeveel gebruikers maken er gebruik van dat programmaatje? Heb je al getest of het specifiek aan deze query ligt? Heb je al indexen in je tabellen gemaakt?
Hoe groot is je database? (in mb en records (bij benadering)

Een index kan inderdaad soms wonderen doen

[whoami]

Begin eens met je query wat duidelijker te schrijven (gestructureerder). Dan is hij heel wat leesbaarder en ook 'uitnodigender' om eens te bekijken.
Neem ook eens - zoals reeds gezegd - een basiscursus SQL bij de hand , en leer daar eens eea van.... Dan zul je er al wat meer verstand van hebben. (Zomaar je probleem hier kwakken en zeggen, kunnen jullie mij helpen want ik heb er geen bal verstand van, wordt niet echt geapprecieerd hier).
Zorg er ook voor dat je eens een execution plan van die query kunt bekijken. Dan kun je zien welke indexen er gebruikt worden en waar er geen indexen gebruikt worden. Zorg dan dat je goede indexen liggen hebt.
De volgorde van de condities in de WHERE clausule kan ook al de performance beinvloeden.

[Demo]

xtra schreef op 11 november 2002 @ 18:26:
[...]

Ik zou maar eens beginnen met basis SQL en dan verder zoeken op performance tuning etc...
Slotje?

Ja ik kan al nauwelijks basis SQL maar mijn baas verwacht van mij dat ik in een week dit onding een stuk sneller kan maken Bovendien weet ik alleen dat de traagheid in het openen van de recordset zit, dus het zou best in de koppeling oid kunnen zitten.

En het spijt mij zeer, maar ik heb 'm niet zomaar hier neergekwakt, maar ik zit er dus al anderhalve week tegenaan te kijken...

edit:
Het probleem is ook dat we op het moment geen echte VB-progger hebben. De ene is ineens verdwenen wegens relatieproblemen, de andere is ontslagen na lelijke beledigingen richting baas (werkte er pas een maand )

[whoami]

Tja, jouw probleem heeft eigenlijk niet zoveel met VB te maken, dus ik zie de relatie met een VB programmeur mbt jouw probleem niet echt in...

Met ertegen aan kijken kom je natuurlijk nergens, dus je zal wel wat moeten proberen... Beetje trial en error desnoods. Zoek eens uit of je geen execution plan kunt opvragen van die query enz.... Zoek eens hoe je hem kunt optimaliseren. (Zoek ook eens naar andere topics hier op GoT over het tunen van queries).
Als je zegt dat het bij de connectie ligt: hoe zit het met die connectie? Wordt er 1 connectie opengehouden door het programma of wordt er iedere keer een nieuwe connectie gemaakt als er naar de databank moet geaccessed worden en wordt deze dan opnieuw afgesloten?

[Dido]

code:

SELECT CL_ActionPerson.*,
       Company.[Company-ID],
       Company.Company,
       Company.Address1,
       Company.Address1Number,
       Company.Address1Postfix,
       Company.Telephone,
       Company.City1,
       Company.Post1,
       Action.*,
       Person.[Person-ID],
       Person.PersonName,
       Person.Midname,
       Person.Capitals,
       T_ActionType.ActionValue
  FROM ((((Action
            LEFT JOIN Project
               ON Action.[Project-ID] = Project.[Project-ID])
            LEFT JOIN Company 
               ON Action.[Company-ID] = Company.[Company-ID])
            LEFT JOIN T_ActionType 
               ON Action.[ActionType-ID] = T_ActionType.[ActionType-ID]) 
            LEFT JOIN CL_ActionPerson 
               ON Action.[Contact-ID] = CL_ActionPerson.[Contact-ID]) 
            LEFT JOIN Person
               ON CL_ActionPerson.[Person-ID] = Person.[Person-ID]
 WHERE ((((Action.[ActionType-ID]) In (" & sActFilter$ & ")) 
   AND (((Person.[Person-ID]) In (" & sPerFilter$ & "))
   AND ((CL_ActionPerson.[Person-ID]) In (" & sPerFilter$ & ")))
   AND ((Action.Actiondate>=#" & sDateBegin$ & "#)
   AND (Action.Actiondate<=#" & sDateEnd$ & "#)))
   AND (Action.Schedule=True))
ORDER BY Action.Actiondate ASC, Action.Actiontime ASC

Ik heb geprobeerd 'm wat duidelijker op te schrijven... wellicht dat ik morgenochtend een idee krijg over performance

[n0kn0k]

Hoeveel records komen er uit je query ? Access wordt namelijk nogal traag bij een grote hoeveelheid records in combinatie met een hoop joins.

[Demo]

n0kn0k schreef op 12 November 2002 @ 09:30:
Hoeveel records komen er uit je query ? Access wordt namelijk nogal traag bij een grote hoeveelheid records in combinatie met een hoop joins.

Uit de query komen de agenapunten voor 1 week. Dit zijn er meestal tussen de 0 en 5, niet veel dus. De database waar ze uit komen is ruim 6000 records.

[n0kn0k]

hmm dan is dat dus niet het probleem Ik zal er vanavond even naar kijken.

[Verwijderd]

Ik weet niet of dit werkt hoor maar het is een idee :

Ik heb onlangs geleerd dat access de tabellen pakt met carthetisch product. Met andere woorden als je achter je from hebt staan :

FROM Company, Agenda (ofzo)

dan maakt access eerst alle mogelijke combinaties van (Company, Agenda)

Met 6000 records en een hoop tabellen wordt dit dus een lomp groot ding. Je moet kijken wat je zoekt en dan de volgorde van je tabellen achter de from aanpassen.
want access gaat nu eerst alle company's na en plakt daar dan agenda's bij. Bijv :

Company heeft als records : {A B en C}
en Agenda heeft als records: {1 , 2 , 3}

dan gaat access eerst maken {A1, A2, A3, B1, B2, B3, C1, C2, C3} als je de from pakt zoals hierboven.

Als je dus bij een company een agenda zoekt dan werkt dit goed. Hij kijkt dan bijvoorbeeld of het eerste veld een A is en is dat het geval dan kijkt hij verder.

Als je nu echter bij een agenda een company zoekt dan werkt dit dus niet zo goed omdat access eerst de hele record moet lezen voordat gekeken kan worden of het onder de voorwaarde valt.

Ik hoop dat dit een beetje een duidelijk verhaal is, maar nogmaals ik weet dit niet 100 % zeker dus als dit niet klopt .. verbeter me aub


[edit]
Als ik zo naar je query kijk moet je die joins dus weggooien en daar gewoon tabellen neerzetten en bij de where die checks doen van A.id = B.id

[Janoz]

Mijn opmerking op de opgeschoonde query
Left join zijn niet de snelste joins. Kijk of ze werkelijk nodig zijn (wil je alle records of alleen die waarin alle velden ook werkelijk voorkomen)
IN is niet snel. Kan dat niet anders?

Daarna mijn opinie over deze vraag:

Waarom neem je deze opdracht aan als je geen reet van SQL weet????

[Demo]

Janoz schreef op 12 November 2002 @ 13:27:
Daarna mijn opinie over deze vraag:

Waarom neem je deze opdracht aan als je geen reet van SQL weet????

Ik moet wel, het probleem moet opgelost worden en er is niemand anders die dat kan doen. Ja mijn baas misschien, maar die weet er net zo veel (weinig ) van als ik en hij heeft ook nog een zaak die hij draaiend moet houden...

[Janoz]

Lijkt het je dan niet handiger dat je baas iemand in gaat huren die er wel verstand van heeft ipv er een stagaire die er niet zo veel verstand van heeft mee op te zadelen?

[Dido]

code:

SELECT CL.*,
       C.[Company-ID],
       C.Company,
       C.Address1,
       C.Address1Number,
       C.Address1Postfix,
       C.Telephone,
       C.City1,
       C.Post1,
       A.*,
       PE.[Person-ID],
       PE.PersonName,
       PE.Midname,
       PE.Capitals,
       T.ActionValue
  FROM Action A,
       Project P,
       Company C,
       T_ActionType T,
       CL_ActionPerson CL,
       Person PE
 WHERE A.[Project-ID]    = P.[Project-ID]
   AND A.[Company-ID]    = C.[Company-ID]
   AND A.[ActionType-ID] = T.[ActionType-ID] 
   AND A.[Contact-ID]    = CL.[Contact-ID] 
   AND CL.[Person-ID]    = PE.[Person-ID]
   AND A.[ActionType-ID] IN (" & sActFilter$ & ") 
   AND PE.[Person-ID]    IN (" & sPerFilter$ & ")
   AND CL.[Person-ID])   IN (" & sPerFilter$ & ")
   AND A.Actiondate BETWEEN #" & sDateBegin$ & "# AND #" & sDateEnd$ & "#
   AND A.Schedule = True
ORDER BY A.Actiondate ASC, A.Actiontime ASC

In ieder geval weer veel leesbaarder...
Weet niet zeker of dit uitmaakt voor de performance?

Janoz schreef op 12 November 2002 @ 14:25:
Lijkt het je dan niet handiger dat je baas iemand in gaat huren die er wel verstand van heeft ipv er een stagaire die er niet zo veel verstand van heeft mee op te zadelen?

Dan leert die er misschien ook nog wat bij
* Dido heeft zo SQL en ASM geleerd

[ErikRo]

Ach een beetje druk op iemand verricht wonderen, kijk eens wat hij (jullie) al bereikt worden.

ps mijn ervaring is dat alles een stuk sneller gaat als je eerst alles in tussen tabellen stopt en na gebruik weggooid.

[kenneth]

De query van Dido geeft een heel ander resultaat.
WHERE A = B is namelijk een inner join, en geen left outer join, die de TS nu gebruikt.

Dus eerst moet de vraag van Janoz beantwoord worden: zijn left outer joins echt nodig?

En dan mijn vraag: waarom WHERE gebruiken? een gewone A INNER JOIN B ON A.id = B.id is toch even snel? En IMO in ieder geval duidelijker.

[Dido]

mithalph schreef op 12 November 2002 @ 19:23:
De query van Dido geeft een heel ander resultaat.
WHERE A = B is namelijk een inner join, en geen left outer join, die de TS nu gebruikt.

Dus eerst moet de vraag van Janoz beantwoord worden: zijn left outer joins echt nodig?

En dan mijn vraag: waarom WHERE gebruiken? een gewone A INNER JOIN B ON A.id = B.id is toch even snel? En IMO in ieder geval duidelijker.

Inderdaad, vanmiddag ff over het hoofd gezien.

De vraag is inderdaad goed: zijn left joins nodig?

Overigens gebruik ik zelf liever deze constructie, dat vind ik dan weer overzichtelijker (maar over smaak valt niet te twisten )

edit:
Ik zie net de CL.*
Is CL_ActionPerson niet een zuivere koppeltabel?
maw, staat er iets in dat niet in Action en Person staat?
Zo ja, dan zou er wellicht nog eens naar het datamodel moeten worden gekeken (dat heet omhoogdelegeren ).
Zo nee, dan kan de CL.* weg uit de select. (Of dat 'm veel sneller maakt weet ik niet)

[Mickman]

Weleens van een query optimiser gehoord. Wellicht is er een te vinden voor je DBMS (als deze nog niet standaard geintegreerd is).

[TukkerTweaker]

Maak eventueel nieuwe tabellen die de relaties beschrijven. Dit pas ik vaak toe in Oracle.