[SQL/Oracle] Query veranderd, nu erg langzaam

[TweakersOnly]

In een oracle database bestaat een tabel klant met daarin het klantnummer en de registratiedatum. Oorspronkelijk had ik een programma waarin de volgende query werd uitgevoerd

SQL:

select * from klant where registratiedatum >= #7/6/2003#
and registratiedatum <= #8/6/2003#

Vanwege diverse redenen moet de query nu aangepast worden. Allereerst worden verschillende bestanden uitgelezen om de gezochte klantnummers te achterhalen. Deze klantnummers worden in een string gezet. Bijvoorbeeld

strKlantnummer = "1,2,3,4, ... , 10000, 10001"

Vervolgens ga ik in de query de klanten achterhalen, van wie het klantnummer in de variabele strKlantnummer voorkomt.

SQL:

SELECT * FROM Klant 
WHERE Klantnummer IN (" & strKlantnummer & ")"

Het uitvoeren van deze query duurt erg lang, soms wel 5 minuten. Weet iemand hoe ik de query kan versnellen?

[ Voor 4% gewijzigd door TweakersOnly op 06-08-2003 12:59 ]

[mkleinman]

Makkelijkste is denk ik om een Index op Registratiedatum te zetten.

Nog een simpele tip ipv een select * een select a,b,c,d,e,f,g helpt de performance ook ietsje.

Dat de tweede query traag is is logisch, je gaat op een varchar2 veld zoeken met een IN operator, mocht er al een index op Klantnummer zetten dan gooi je die op dat moment overboord..

Beste is IMHO om de strKlantnummer op te slaan in een al dan niet tijdelijke tabel(uiteraard correct geindexeerd). Daarna kan je die twee tabellen joinen en zou hij in een 0.2s klaar moeten zijn ongeveer.

* mkleinman heeft geen Oracle Client install draaien hier thuis atm, dus ik kan ff niet connecten met een db om er een stukje code bij te bakken.

[ Voor 11% gewijzigd door mkleinman op 06-08-2003 13:05 ]

[justmental]

Hoeveel rijen heeft de tabel klant?
Heb je een explain of een trace gedraaid op de query? -> wat was de uitvoer daarvan?
Wat zijn de bestanden waar je het over hebt? -> zijn dat ook tabellen?
Hoe staan je indexes?

[TweakersOnly]

Makkelijkste is denk ik om een Index op Registratiedatum te zetten.

De desbetreffende database is een onderdeel van de concerninformatiesystemen en mag/kan ik niet aanpassen.

Nog een simpele tip ipv een select * een select a,b,c,d,e,f,g helpt de performance ook ietsje.

Dit had ik al gedaan, ik had alleen geen zin om de hele lap tekst over te typen.

Dat de tweede query traag is is logisch, je gaat op een varchar2 veld zoeken met een IN operator, mocht er al een index op Klantnummer zetten dan gooi je die op dat moment overboord..

Jouw bewering klopt niet. De variabele strKlantnummers bevat een string. Deze string wordt een onderdel van het select-statements, de klanten in strKlantnummers worden door de database als integers gezien.

[whoami]

Een index op Registratiedatum zal de 2de query niet versnellen, aangezien er niet op registratie-datum gefliterd wordt.
Leg een index op klantnr, en maak -als je kan- gebruik van de BETWEEN operator ipv de IN operator. (Die between kan je enkel gebruiken als je alle klanten wil waarvan het klantid binnen een bepaalde range ligt).

[Harmen1975]

Aangezien je geen indexen aan mag maken, heb je alleen iets aan de tip van Justmental. Werk met explain plan en trace om performance te winnen.
Ik heb geen ervaring met de DBA-module van TOAD (vast wel bekend tooltje). Maar van collega's heb ik gehoord dat hierin ook explain plan en trace achtige opties zitten.

Je hebt het over "een programma"...geef eens wat meer details. Waarschijnlijk zijn er ook oplossingen met PL/SQL (evt. in combinatie met een PL/SQL-tabel) mogelijk.

[JaQ]

whoami schreef op 06 August 2003 @ 14:02:
Een index op Registratiedatum zal de 2de query niet versnellen, aangezien er niet op registratie-datum gefliterd wordt.
Leg een index op klantnr, en maak -als je kan- gebruik van de BETWEEN operator ipv de IN operator. (Die between kan je enkel gebruiken als je alle klanten wil waarvan het klantid binnen een bepaalde range ligt).

Het between verhaal t.o.v. in geldt niet meer vanaf Oracle 9 (qua performance dan). Als het dus een 9.0.1 of nieuwere Oracel db is, dan kan je in zorgeloos gebruiken.

Een explain plan van je query zou je veel moeten kunnen vertellen. (en dan kom je waarschijnlijk tegen, wat hier al vaker is genoemd, gebrek aan een index op klantnr)

edit:
woeps.. indexen maken mag niet... dan heb je nu dus officieel een probleem

[ Voor 6% gewijzigd door JaQ op 06-08-2003 14:24 ]

[whoami]

Harmen1975 schreef op 06 August 2003 @ 14:22:
Aangezien je geen indexen aan mag maken, heb je alleen iets aan de tip van Justmental. Werk met explain plan en trace om performance te winnen.
Ik heb geen ervaring met de DBA-module van TOAD (vast wel bekend tooltje). Maar van collega's heb ik gehoord dat hierin ook explain plan en trace achtige opties zitten.

Je hebt het over "een programma"...geef eens wat meer details. Waarschijnlijk zijn er ook oplossingen met PL/SQL (evt. in combinatie met een PL/SQL-tabel) mogelijk.

Met een plan explain kan je zien welke indexen er gebruikt worden, waar er table scans gedaan worden etc....
Aangezien hij geen indexen kan aanmaken, kan hij wel de oorzaak vinden, maar er geen gevolg aan geven.

[justmental]

whoami schreef op 06 August 2003 @ 14:30:
Met een plan explain kan je zien welke indexen er gebruikt worden, waar er table scans gedaan worden etc....
Aangezien hij geen indexen kan aanmaken, kan hij wel de oorzaak vinden, maar er geen gevolg aan geven.

Er zijn meer oplossingen mogelijk dan het toevoegen van indexen, denk aan optimizer hints, het updaten van statistics ed.
Ik vemoed namlijk dat er wel een index op dat klantnr ligt.
Maar eerst moet de TS eens wat van de vragen beantwoorden.

[P_de_B]

De enige goede oplossing zou zijn de lijst met integers in een (tijdelijke) tabel te zetten, en op die kolom en op de kolom waarop je zoekt een index op te bouwen.

Ik vind het heel vreemd dat je wel queries mag veranderen, maar geen index mag aanmaken. Hoe kun je nu goed je werk doen als je hetgene wat je moet doen, niet mag doen?

edit: wat mkleinman dus ook al zei, sorry had ik niet gezien.

[ Voor 10% gewijzigd door P_de_B op 06-08-2003 14:38 ]

[whoami]

justmental schreef op 06 augustus 2003 @ 14:35:
[...]

Er zijn meer oplossingen mogelijk dan het toevoegen van indexen, denk aan optimizer hints, het updaten van statistics ed.

Updaten van statistics zal alleen maar nut hebben als de statistics niet meer up to date zijn. Aangezien de TS niet vermeldt dat hij een hele hoop wijzigingen in de tabel heeft aangebracht, denk ik niet dat dit verschil zal maken.

[whoami]

P_de_B schreef op 06 August 2003 @ 14:36:
De enige goede oplossing zou zijn de lijst met integers in een (tijdelijke) tabel te zetten, en op die kolom en op de kolom waarop je zoekt een index op te bouwen.

Ik vind het heel vreemd dat je wel queries mag veranderen, maar geen index mag aanmaken. Hoe kun je nu goed je werk doen als je hetgene wat je moet doen, niet mag doen?

edit: wat mkleinman dus ook al zei, sorry had ik niet gezien.

Waarom zou een IN geen gebruik maken van indexen?

code:

select * from tabel where id IN (1, 2, 4, 5)

[P_de_B]

whoami schreef op 06 August 2003 @ 14:41:
[...]


Waarom zou een IN geen gebruik maken van indexen?

code:

select * from tabel where id IN (1, 2, 4, 5)

Je hebt gelijk, ik dacht dat hij een string opbouwde in de SQL code, en deze dan uitvoerde. (vgl. SQL Server: Exec ( string ) )

Verkeerd gelezen....

[Verwijderd]

Ik snap sowieso je syntax niet helemaal, nooit gezien eik, met die hekjes.

Maar ervanuitgaande dat je de syntax:

code:

select * from tabel where id IN ( 1, 2,  ..., n )

gebruikt:
je kan overwegen om een pl/sql tabel aan te maken waar je alle id's in zet en die door te zoeken in je query. Heb eigenlijk nooit geprobeerd of het werkt in de where-clause, dus is nog wel interessant om te proberen.
Nu weet ik niet of je alleen op datum zocht en nu alleen op klantnr wil zoeken, of dat dit een extra clause wordt. Het laatste lijkt me niet waarschijnlijk omdat het de query alleen maar sneller zou maken.

Tsja, als je een forse tabel hebt en je mag geen indexje plaatsen op je meestgebruikte kolommen, dan worden je handen er natuurlijk spreekwoordelijk afgehakt.

[whoami]

Verwijderd schreef op 06 August 2003 @ 15:30:
Ik snap sowieso je syntax niet helemaal, nooit gezien eik, met die hekjes.

Waar zie jij hekjes?

Nu weet ik niet of je alleen op datum zocht en nu alleen op klantnr wil zoeken, of dat dit een extra clause wordt. Het laatste lijkt me niet waarschijnlijk omdat het de query alleen maar sneller zou maken.

Niet noodzakelijk. Alles hangt af van de indexen die kunnen gebruikt worden

[Verwijderd]

whoami schreef op 06 August 2003 @ 15:46:
[...]

Waar zie jij hekjes?

[...]

Niet noodzakelijk. Alles hangt af van de indexen die kunnen gebruikt worden

TweakersOnly schreef op 06 August 2003 @ 12:58:

SQL:

select * from klant where registratiedatum >= #7/6/2003#
and registratiedatum <= #8/6/2003#

Het hangt zeker van de indexen af. Maar ik wil graag even het totaalplaatje zien (dus de grootte van de tabel, de reeds bestaande indexen, de oude en nieuwe query etc). En liefst een explain plan natuurlijk. Dan kan je ook zeggen of het zonder nieuwe indexen nog versneld kan worden.

Dus TweakersOnly, we wachten met smart

[ Voor 3% gewijzigd door Verwijderd op 06-08-2003 15:56 ]

[whoami]

Die hekjes zijn een manier om aan te duiden dat het om een datum gaat. Dit kan in SQL Server gebruikt worden. Ik weet niet hoe Oracle er tegenover staat.
Trouwens, het gaat niet meer om die query, maar om die tweede.

[P_de_B]

offtopic:
>>> Die hekjes zijn een manier om aan te duiden dat het om een datum gaat. Dit kan in SQL Server gebruikt worden

SQL Server gebruikt geen #, maar ' als zgn datedelimiters, Access gebruikt deze wel

[mkleinman]

TweakersOnly schreef op 06 augustus 2003 @ 13:39:
[...]

Jouw bewering klopt niet. De variabele strKlantnummers bevat een string. Deze string wordt een onderdel van het select-statements, de klanten in strKlantnummers worden door de database als integers gezien.

Laadt dit geintje maar eens in in Toad of Plsqldeveloper en druk eens op F9 wanneer je deze query uitvoert (Plan table). wedden dat het een table access full is.

zodra je een IN operator gebruikt met varchars of '%' als like gebruikt gooi je je indexen overboord.

de Strklantnummers is voor 99% zeker een String (varchar2 in oracle). en de db gaat daar ook echt als een string mee op en niet als integers.

[justmental]

mkleinman64 schreef op 06 August 2003 @ 17:35:
de Strklantnummers is voor 99% zeker een String (varchar2 in oracle). en de db gaat daar ook echt als een string mee op en niet als integers.

Dat is een string, maar hij plakt hem in een dynamische query en dus staan er gewoon integers.

[TweakersOnly]

Ff een update: De klantentabel bevat ong. 55000 records. Het klantnummer is de primaire sleutel en is dus automatisch geindexeerd. In een van de topics werd gesuggereerd dat in de query integers worden omgezet naar varchar's. Dit is pertinent onjuist.

[DigiK-oz]

Oke, maar wat is je tabeldefinitie voor dat klantnummer veld? Als dit varchar is, ga je er met jouw SELECT (waar de klantnummers niet tussen ' ' staan als ik het goed zie) voor zorgen dat er een impliciete conversie plaatsvindt. Met voorkeur voor integer. Dus, dan moet elk klantnummer in de tabel eerst worden geconverterd naar integer, met als gevolg full table scan.


Als in je tabeldefinitie je klantnummer ook integer is, heb ik niks gezegd

Anders dus in je query ervoor zorgen dat je klantnummer tussen quotes (enkele) komt te staan :

where klantnr in ('1','2','3')

[TweakersOnly]

sjis schreef op 06 August 2003 @ 20:51:
Als in je tabeldefinitie je klantnummer ook integer is, heb ik niks gezegd

In de tabeldefinitie is mijn klantnummer idd een integer/long, om precies te zijn number(15,0)

[TweakersOnly]

P_de_B schreef op 06 August 2003 @ 14:36:Ik vind het heel vreemd dat je wel queries mag veranderen, maar geen index mag aanmaken. Hoe kun je nu goed je werk doen als je hetgene wat je moet doen, niet mag doen?

Zo'n onzin vind ik het helemaal niet. De database wordt icm met een ander groot programma gebruikt als Concern Informatie Systeem. Hieraan zijn allerlei regeld ed. gebonden. De applicatie die ik schrijf is een kleine applicatie die alleen de klantendatabase hoeft te benaderen en mag totaal geen invloed op de C.I.S. hebben.

[justmental]

D'r gaat toch iets grondig mis, vergelijk dit zonder index op m'n pc-tje:

code:

SQL> create table klant (klantid number, test varchar2(100));

Tabel is aangemaakt.

SQL> exec for i in 1..55000 loop insert into klant values(i,'dit is een test'); end loop;

PL/SQL-procedure is geslaagd.

SQL> analyze table klant compute statistics;

Tabel is geanalyseerd.

SQL> set timing on
SQL> select * from klant where klantid in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,100,200,1000,1100,5000,13000,40000);

*knip*

   KLANTID
----------
TEST
--------------------------------------------------------------------------------
     13000
dit is een test

     40000
dit is een test


23 rijen zijn geselecteerd.

Verstreken: 00:00:00.02
SQL>

Met index op klantid:

code:

SQL> create unique index tst_idx on klant (klantid);

Index is aangemaakt.

Verstreken: 00:00:00.04
SQL> select * from klant where klantid in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,100,200,1000,1100,5000,13000,40000);
*knip*
   KLANTID
----------
TEST
--------------------------------------------------------------------------------
     13000
dit is een test

     40000
dit is een test


23 rijen zijn geselecteerd.

Verstreken: 00:00:00.01
SQL>

[ Voor 25% gewijzigd door justmental op 06-08-2003 22:54 ]

[whoami]

Heb je al eens naar het execution plan gekeken van die query?

Ben je wel zeker dat er in Oracle op een PK automatisch een index gelegd wordt?

[justmental]

whoami schreef op 06 August 2003 @ 23:37:
Ben je wel zeker dat er in Oracle op een PK automatisch een index gelegd wordt?

Dat kan ik bevestigen, Oracle bewaakt de primary key door middel van de index erop.
Dat executie-plan ben ik nou ook wel nieuwsgierig naar.

[Verwijderd]

Ik sluit me bij jou aan, justmental, 55000 records is een lachertje in Oracle en die zou helemaal niet zo traag moeten zijn. Nu begin ik me af te vragen, parse jij die query vanuit VB ofzo? Of run je 'm vanaf de sql-prompt?
Daarnaast, die discussie over integers of varchar2 wordt steeds onduidelijker voor mij
als het een number in de tabel is, dan ga je toch geen kwoots rond de waarden zetten? Impliciete typeconversie staat in de 10 geboden van dingen die je niet moet doen in Oracle. tenzij je veel tijd wilt hebben om koffie te halen! Lama, ik zie het al. Number that is, nix meer aan doen.
Welke Oracle versie gebruik je? (7.3, 8.1.[67], 9i?).

[ Voor 12% gewijzigd door Verwijderd op 08-08-2003 08:37 ]