[SQL SERVER 2005] Indexed view levert query op base tables

[Verwijderd]

Al enige tijd ben ik me aan het verdiepen in optimalisatie in Sql Server 2005. Nu lees ik dat je een view moet gebruiken als je veel joins gebruikt. Gedaan, maar leverde eigelijk geen verschil. Daarna kwam ik iets tegen dat je views kan indexeren. Ook hier op Got wordt geroepen dat het kan, maar kan niet echt vinden hoe iemand het werkend gekregen heeft. Dus gegoogled, diverse links gevonden waaronder: http://www.akadia.com/services/sqlsrv_matviews.html

Dit heb ik geprobeerd met een simpele (ansi) inner join tussen 2 tabbelen met een gezamelijke resultset van zo'n 800.000 records. Groot genoeg lijkt me dat er op de view gequeried dient te worden en niet op de achterliggende base tables.Ergens heb ik gelezen dat de resultset wel enige grootte moet hebbel wil deze echt 1 op 1 gebruikt wordt voor de query op de view.

Als voorbeeld heb ik deze code uitgevoerd in Sql Server Management Studio Express

SQL:

use EenDummyDatabase
go

SET SHOWPLAN_ALL OFF
GO
 SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ON
GO
SET ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO

-- Create the Index View
DROP VIEW VW_summary
go
CREATE VIEW VW_summary
WITH SCHEMABINDING
AS
   SELECT tabelA.id AS a_id, tabelB.id as b_id, tabelB.amount
     from dbo.tabelA inner join dbo.tabelB on tabelA.id = tabelB.tabelA_id
GO

-- Create UNIQUE CLUSTERED index on the view
CREATE UNIQUE CLUSTERED INDEX IVW_summary
  ON VW_summary (b_id)
GO

Om te testen of er op de view of op de base tables gequeried wordt gebruik ik deze code:

SQL:

SET SHOWPLAN_ALL ON
go

select top 100 * from VW_summary

Dit levert dit op:

code:

  select top 100 * from VW_summary
  |--Top(TOP EXPRESSION:((100)))
       |--Hash Match(Inner Join, HASH:([EenDummyDatabase].[dbo].[tabelA].[id])=([EenDummyDatabase].[dbo].[tabelB].[tabelA_id]), RESIDUAL:([EenDummyDatabase].[dbo].[tabelB].[tabelA_id]=[EenDummyDatabase].[dbo].[tabelA].[id]))
            |--Index Scan(OBJECT:([EenDummyDatabase].[dbo].[tabelA].[deleted]))
            |--Clustered Index Scan(OBJECT:([EenDummyDatabase].[dbo].[tabelB].[PK_tabelB]))

Ook een join op 5 tabellen, een gecombineerde unique clusterd key geprobeerd, maar krijg het niet voor elkaar. Wat zie ik over het hoofd?

[Verwijderd]

Iemand een suggestie?

[DigiK-oz]

Ik ken SQL server niet echt, maar ik zie nergens een WHERE clause. Waarom zou je dan verwachten dat er via de index op de view wordt gelezen?

Verder wil het gebruik van TOP ook nog wel eens een access-path beïnvloeden, omdat de database dan "weet" dat er maar 100 rijen opgehaald hoeven worden.

[MrBucket]

Je moet je index leggen op alle velden van de view die je uit wil vragen, niet op 1 veld

Het idee is namelijk dat je de optimalisatie haalt uit het feit dat je de onderliggende tabellen niet meer hoeft te raadplegen omdat alle informatie al in de index van de view is opgeslagen.

[P_de_B]

Je weet dat Indexed Views alleen in de - zeer dure - Enterprise Versie werken?

[Verwijderd]

Sloompie schreef op zondag 29 april 2007 @ 18:27:
Ik ken SQL server niet echt, maar ik zie nergens een WHERE clause. Waarom zou je dan verwachten dat er via de index op de view wordt gelezen?

Verder wil het gebruik van TOP ook nog wel eens een access-path beïnvloeden, omdat de database dan "weet" dat er maar 100 rijen opgehaald hoeven worden.

Aangezien ik een ANSI join gebruik zie je de join voorwaarde niet expliciet in de where staan. Let erop dat dit hetzelfde is als:

SQL:

select a.id, b.id from a, b where a.id = b.a_id

MrBucket schreef op zondag 29 april 2007 @ 20:50:
Je moet je index leggen op alle velden van de view die je uit wil vragen, niet op 1 veld

Het idee is namelijk dat je de optimalisatie haalt uit het feit dat je de onderliggende tabellen niet meer hoeft te raadplegen omdat alle informatie al in de index van de view is opgeslagen.

Nee toch...toch alleen op de gebruikte kolommen in de join voorwaardes + de eventuele where clause kolommen?

P_de_B schreef op zondag 29 april 2007 @ 20:55:
Je weet dat Indexed Views alleen in de - zeer dure - Enterprise Versie werken?

We got a winner, hier gebruiken we de standaard editie denk ik.

[MrBucket]

Ik snapte de 'where-clause' opmerking eerst niet helemaal, maar volgens mij begint het me te dagen: Stel dat je FK-veld b.a_id een NOT NULL is, dan heeft elke b-rij een verwijzing naar een a-rij. Door nu een select zonder where uit te voeren, moet er hoe dan ook een table-scan worden gedaan - er worden nu immers geen rijen gefilterd. Dus waarom zou SQL server dan een index gebruiken als hij toch van tevoren al weet dat hij door de hele tabel heen moet ploegen?

Verwijderd schreef op dinsdag 01 mei 2007 @ 13:59:
[...]
Nee toch...toch alleen op de gebruikte kolommen in de join voorwaardes + de eventuele where clause kolommen?

Dan moet SQL Server nog steeds de onderliggende tabellen raadplegen om de uiteindelijke resultset samen te stellen. Nogmaals: niet alle informatie die benodigd is voor de resultset is aanwezig in de index op je view. En dat is nu net wat je niet wil, omdat er dan alsnog lookups gedaan moeten worden - die maken de zaak zo traag.
Als je alle kolommen wel meeneemt in de index op je view heb je geen lookups nodig: de joins zijn dan al gedaan tijdens het samenstellen van de index op je view.

Mijn advies is: probeer eerst een index op de view te maken die alle kolommen uit de resultset bevat:

SQL:

CREATE UNIQUE CLUSTERED INDEX IVW_summary 
  ON VW_summary (a_id, b_id, amount) 
GO

Doe vervolgens een select * where [filter op het eerste veld uit je index], bijv:

SQL:

select *
from IVW_summary
where a_id between 1000 and 5000

Als er indexed views ondersteund worden, dan is dit de ideale situatie om hem te gebruiken. Mocht dit inderdaad werken, dan kun je experimenteren met kolommen weglaten enz.

[Verwijderd]

MrBucket schreef op dinsdag 01 mei 2007 @ 20:09:

Mijn advies is: probeer eerst een index op de view te maken die alle kolommen uit de resultset bevat:

SQL:

CREATE UNIQUE CLUSTERED INDEX IVW_summary 
  ON VW_summary (a_id, b_id, amount) 
GO

Doe vervolgens een select * where [filter op het eerste veld uit je index], bijv:

SQL:

select *
from IVW_summary
where a_id between 1000 and 5000

Als er indexed views ondersteund worden, dan is dit de ideale situatie om hem te gebruiken. Mocht dit inderdaad werken, dan kun je experimenteren met kolommen weglaten enz.

Het execution plan op een dergelijke view (met een clusterd unique index) en op achterliggende sql code met de additionele where voorwaarde zijn exact hetzelfde. Dit lijkt dus niet te werken.

Nu lees ik ook dingen op statistieken in Sql Server. Wordt het execution plan bij een view soms bepaald door statistieken? Ik krijg hier in ieder geval geen hoogte van, aangezien een herhalende query op een view vaak veel sneller is dan losse 'ad hoc' queries.

[mulder]

Ik zie een hash match voorbij komen, dat kan beteken dat je een join doet op 2 velden van een verschillend datatype. Dit is een dure operatie, zorg dat de sleutels van het zelfde datatype zijn.

[Verwijderd]

Don Facundo schreef op woensdag 02 mei 2007 @ 11:45:
Ik zie een hash match voorbij komen, dat kan beteken dat je een join doet op 2 velden van een verschillend datatype. Dit is een dure operatie, zorg dat de sleutels van het zelfde datatype zijn.

Beide datatypes zijn bigint, dus deze vlieger gaat niet op.

Is het trouwens niet slimmer op kolommen waar je niet opgenomen zijn in WHERE, GROUP BY of ORDER op te nemen als INCLUDE ipv in de index?

[ Voor 16% gewijzigd door Verwijderd op 02-05-2007 13:33 ]