[SQL2K]View of iets anders?

[evol]

Ik heb hier een database in SQL2000 Server. Daarin zit een view. Die view kost als ik de select eruit loslaat in de query analyzer zo'n 20 seconden.

In een applicatie wordt een report gegenereerd aan de hand van gegevens uit die view. Er worden 3 queries losgelaten op de view. Elke query duurt zo'n 15 seconden. Het duurt dus in totaal allemaal erg lang. (Valt absoluut gezien wel mee, maar de applicatie en de database zijn niet van mega formaat)

Op welke manier kan ik hier snelheid winnen? Ik heb geprobeerd de view te indexeren, maar dat wil niet echt. Er zit een illegal construction in de view (select in select), plus ik vraag me af of dit veel oplevert.
Zou een stored procedure hier uitkomst kunnen bieden? Of zijn er nog andere mogelijkheden?

[P_de_B]

Je moet de onderliggende tabellen indexeren, niet de view.

In query analyzer zit een handige wizard die je daarbij kan begeleiden. De Index tuning wizard.

^{ja, ik weet dat indexed views mogelijk zijn}

[Verwijderd]

Er is zoiets als een Query Analyzer in SQL, heb je die al gebruikt? Kun je zien waar de bottlenecks zitten.

Een SP zou winst kunnen opleveren omdat het (na de 1e keer) gecompileerde stukken code zijn, die in de regel sneller worden uitgevoerd dan queries.

[whoami]

Heb je indexes liggen op die tabel(len)?
Zijn je indexen bruikbaar?
Zijn je statistieken op de tabellen up to date ?

Heb je al eens het execution plan van die query bekeken?

[whoami]

Verwijderd schreef op 03 maart 2004 @ 16:10:
Een SP zou winst kunnen opleveren omdat het (na de 1e keer) gecompileerde stukken code zijn, die in de regel sneller worden uitgevoerd dan queries.

De execution plans van queries worden ook gecached. Een SP zal dus geen voordeel opleveren.

Hoe ziet jouw query er trouwens uit ?
Hoe ziet de query er uit die je VIEW bepaald ? Hoe zit het met die SELECT in die SELECT (subselect) ?
Ben je zeker dat je niet met een carthesiaans product te maken hebt?

[ Voor 31% gewijzigd door whoami op 03-03-2004 16:13 ]

[P_de_B]

Verwijderd schreef op 03 maart 2004 @ 16:10:
Er is zoiets als een Query Analyzer in SQL, heb je die al gebruikt? Kun je zien waar de bottlenecks zitten.

En eventueel zou je Profiler ook kunnen gebruiken

Een SP zou winst kunnen opleveren omdat het (na de 1e keer) gecompileerde stukken code zijn, die in de regel sneller worden uitgevoerd dan queries.

Dit is inmiddels redelijk achterhaald.

[evol]

whoami schreef op 03 maart 2004 @ 16:11:
Heb je indexes liggen op die tabel(len)?
Zijn je indexen bruikbaar?
Zijn je statistieken op de tabellen up to date ?

Heb je al eens het execution plan van die query bekeken?

Jep indexen liggen er op de tabellen. Bruikbaar? Ja, volgens mij wel, query analyzer execution plan laat wel zien dat ie ze (de indexen op de onderliggende tabellen) gebruikt. Maar bij sommigen staat 0% (lijkt me prima ) en anderen staat 70%.
Statistieken op de tabellen? Que-ce-que c'est?

/edit: Overigens heeft de index tuning wizard niets aan te merken op mijn indexen.

[ Voor 8% gewijzigd door evol op 03-03-2004 16:15 ]

[EfBe]

Een indexed view zal VEEL snelheid opleveren. Nadeel van indexed views is dat inserts e.d. in de tables die in de view zitten anders moeten worden gedaan (je moet een statement meegeven anders krijg je een error).

Een view wordt in de query gemapped at runtime. Dus als jij doet: SELECT * FROM myView dan zal de complete select list van myView en de query daarin in die select worden gemapped. Dit is op zich voordelig, want omdat de view als een execution plan heeft van bv een vorige keer dat de view is gebruikt, is de query sneller en kan makkelijker worden geoptimaliseerd.

Een stored procedure levert je 0.0 snelheidswinst op, want sqlserver compileert stored procedures niet van te voren maar cached execution plans, dus dat levert geen snelheidswinst op. Laat je niet in de luren leggen door de stored proc fetisjisten

Ik begrijp niet echt wat een illegal instruction is: want als het iets is wat niet compileert, dan werkt het geheel toch niet?

Wat ook nog wel eens wil helpen is subqueries omschrijven naar joins. Maar post de view sql eens, dan is er wellicht wat over te zeggen. (en goed naar het execution plan kijken van de view zelf!

[whoami]

EfBe schreef op 03 maart 2004 @ 16:14:
Een indexed view zal VEEL snelheid opleveren. Nadeel van indexed views is dat inserts e.d. in de tables die in de view zitten anders moeten worden gedaan (je moet een statement meegeven anders krijg je een error).

Hij kan geen indexed view gebruiken, aangezien hij met een subselect zit.

Ik begrijp niet echt wat een illegal instruction is: want als het iets is wat niet compileert, dan werkt het geheel toch niet?

Om een indexed view te kunnen maken, moet je query die als basis dient voor die view aan nogal wat voorwaarden voldoen. Ik kan ze niet van buiten opnoemen, maar ik kan me wel inbeelden dat een subselect daartoe behoort.

[EfBe]

whoami schreef op 03 maart 2004 @ 16:18:
[...]

Hij kan geen indexed view gebruiken, aangezien hij met een subselect zit.

... maar die is om te poken naar een join set

[whoami]

.... misschien, misschien niet.

Je mag alleszins geen OUTER JOINS, ORDER BY, DISTINCT, TOP, UNION gebruiken in je indexed view query.

[evol]

Laat ik er dit bij zeggen, de view en de database zijn niet door mij opgezet. Dit is een bestaande applicatie die ik omzet naar sql server 2000.

De view, licht aangepast:

code:

CREATE view Test
with schemabinding
as
select COALESCE(a.TNummer, '0000000')+COALESCE(Convert(char(8), a.IDat, 112), '00000000')+a.Val AS Kop,
       a.AId, a.FNr, m.Tp, m.L_Nr as M_L_Nr, 
       a.L_Nr as A_L_Nr, o.L_Naam as Oorsprong, 
       h.L_Naam as Herkomst, b.LevCon,  
       a.MId, bd.SPrijs, DK.Koers, DK.KoersPer, 
       b.TranKost, a.PInvRecht, a.InvRecht, a.DStat, 
       a.StatNr, a.TNummer, a.InvDat, a.InslDat, a.UitslDat,
       b.Val, a.BNr, a.BRegelNr, a.VNr, a.VRegelNr,
       a.Lok, DK.KDatum as DKoersDat
  FROM dbo.Mod as m, dbo.Best as b, 
       dbo.Best_Det as bd, dbo.Art as a, 
       dbo.Lnd as o, dbo.Lnd as h, dbo.Rel as r,
       dbo.DoKoers as DK
 WHERE a.Mid = m.Mid 
   AND a.ArtInkStat <> 'Vervallen'
   AND a.BNr = b.BNr 
   AND b.BNr = bd.BNr 
   AND a.BRegelNr = bd.BRegelNr 
   AND m.L_Nr = o.L_Nr 
   AND a.L_Nr = h.L_Nr 
   AND bd.DoStat = 'T1' 
   AND b.RelId = r.RelId
   AND DK.Val = b.Val
   AND ((InvDat IS NOT NULL AND DatePart(mm, DK.KDatum) = Datepart(mm, InvDat) 
           AND DatePart(yy, DK.KDatum) = DatePart(yy, InvDat))
     OR (InvDat IS NULL AND DK.KDatum = (select max(KDatum) 
                               from dbo.DoKoers where Val = b.Val)))

Vraag me af of je hier iets mee kan...
Ennieweej als ik indexed view ervan wil maken, is die select max aan t einde de bottleneck volgens mij.

[whoami]

Heb je indexen liggen op de velden waarop je joined en zoekt in die tabellen?

Kan je die subquery niet herschrijven dmv een ORDER BY DESC en een TOP 1 clausule ?

[ Voor 37% gewijzigd door whoami op 03-03-2004 16:30 ]

[evol]

whoami schreef op 03 maart 2004 @ 16:29:
Heb je indexen liggen op de velden waarop je joined en zoekt in die tabellen?

Kan je die subquery niet herschrijven dmv een ORDER BY DESC en een TOP 1 clausule ?

Indexen liggen op de juiste velden. Herschrijven is een optie.

[P_de_B]

Je zegt dat de je de database aan het overzetten bent, kan het zijn dat de statistieken etc. nog niet zijn bijgewerkt?

Heb je de index tuning wizard geprobeerd?

[whoami]

Worden die indexen ook gebruikt als je het execution plan van die query in Query Analyzer bekijkt ?
Zijn je statistieken nog up to date ? Anders kan je eens proberen om die ook te updaten. Als ie nl. niet up to date zijn, dan wordt er een execution plan gemaakt / gebruikt op basis van verouderde statistieken.

Hoe lang duurt die select max(datum) als je 'm alleen uitvoert ?

[EfBe]

ai, een select max(field) zal een complete table scan doen. Je kunt ook SELECT TOP 1 field FROM table ORDER By Field DESC doen. scheelt vaak veel tijd