[SQL Server 2005] Doorzoeken op een selectie

[Verwijderd]

Stel we hebben een huis waarvan we een aantal dingen opslaan (zie http://www.funda.nl/wonin...ult.aspx?tab=PlaatsOfLand)

Voorzieningen
Tuin
Balkon/dakterras
C.V.-ketel
Lift
Garage
Schuur/berging
Bedrijfsruimte
Open haard
Zwembad

Ligging
Aan bosrand
Aan drukke weg
Aan park
Aan rustige weg
Aan water
Beschutte ligging
Buiten bebouwde kom
In centrum
In woonwijk
Open ligging
Vrij uitzicht

isolatie
dubbelglas
spouwmuurisolatie
gevelisolatie


We onderscheiden de volgende tabellen:
huis
huis_voorziening
voorziening
huis_ligging
ligging
huis_isolatie
isolatie
Een huis heeft dus een veel op veel relatie met voorzienig en met ligging.

Voorbeeld
Huis A heeft als voorzieningen een zwembad, tuin, garage (3 stuks), heeft de volgende liggingen: aan bosrand, Vrij uitzicht (2 stuk) en is volledig geïsoleerd: dubbelglas, spouwmuurisolatie en gevelisolatie

Scenario
Er dient gezocht te worden naar alle huizen met ligging Aan water en isolatie Dubbelglas.

Simpelweg alle tabellen joinen levert een vrij groot cartesiaans product op: 3 * 2 * 3 = 18 waarop vervolgens de filters worden toegepast. Dit lijkt mij niet de oplossing.

Oftewel: je wilt doorzoeken op een selectie dus (uit de losse pols) heb ik dit sql statement gemaakt:

code:

WITH derived1 AS
(
    SELECT
        huis_id
    FROM
        liggingen
        INNER JOIN liggingen_huis ON
            liggingen_huis.huis_id = liggingen.id
    WHERE
        liggingen.name = 'Aan water'
),
derived2 AS (
    SELECT
        huis_id
    FROM
        derived1
        INNER JOIN isolatie_huis ON
            isolatie_huis.huis_id = derived1.huis_id
        INNER JOIN isolatie ON
                isolatie_huis.huis_id = isolatie.id     
            AND isolatie.name = 'dubbelglas'
) 
SELECT
*
FROM
    huis
WHERE
    derived2.id IN(SELECT huis_id FROM derived2)

Wat is jullie visie hierop?

[ Voor 8% gewijzigd door Verwijderd op 16-03-2010 16:46 ]

[Hydra]

Verwijderd schreef op dinsdag 16 maart 2010 @ 16:28:
Wat is jullie visie hierop?

Als je de juiste indices zet zal SQL server de query zo optimaliseren dat die join helemaal geen grote performanceimpact op gaat leveren. Volgens mij ben je een oplossing aan 't zoeken voor een probleem dat niet bestaat.

[Verwijderd]

Hydra schreef op dinsdag 16 maart 2010 @ 16:43:
[...]


Als je de juiste indices zet zal SQL server de query zo optimaliseren dat die join helemaal geen grote performanceimpact op gaat leveren. Volgens mij ben je een oplossing aan 't zoeken voor een probleem dat niet bestaat.

Nu geef ik maar 3 tabellen waar informatie over een huis wordt opgeslagen. Dit zijn er in werkelijkheid veel meer. Reken op een stuk of 20. Mochten al die 20 tabellen 2 kenmerken bevatten krijg je 2²⁰ = 1048576 combinaties. Dan wordt het wel erg gortig

[Janoz]

Je hebt niet een volledig cartesiaans product nodig. Wanneer je je joins begint bij de opties wordt de afmeting van de koppeltabel natuurlijk al enorm terug gebracht omdat je enkel geinterreseerd bent in de huis id's die aan de gekozen opties gekoppeld zijn.

[Hydra]

Janoz schreef op dinsdag 16 maart 2010 @ 17:01:
Je hebt niet een volledig cartesiaans product nodig. Wanneer je je joins begint bij de opties wordt de afmeting van de koppeltabel natuurlijk al enorm terug gebracht omdat je enkel geinterreseerd bent in de huis id's die aan de gekozen opties gekoppeld zijn.

Inderdaad. Als je zoekt naar huizen aan het water zullen huizen die niet gekoppeld zijn aan deze ligging uitgesloten worden als je je indices goed voor elkaar hebt. Een query explain zou dat ook moeten tonen.

[Verwijderd]

Janoz schreef op dinsdag 16 maart 2010 @ 17:01:
Je hebt niet een volledig cartesiaans product nodig. Wanneer je je joins begint bij de opties wordt de afmeting van de koppeltabel natuurlijk al enorm terug gebracht omdat je enkel geinterreseerd bent in de huis id's die aan de gekozen opties gekoppeld zijn.

Klopt maar ik ga even uit van een een worst case scenario. Ik verwacht zeker 2⁵ = 32 records. En er wordt niet naar 1 pand gezocht maar naar meerdere, dus je zult een hele grote resultset krijgen...

[Afvalzak]

Als dit soort query's echt vaak gaan voorkomen is het altijd een idee om te gaan de-normaliseren waardoor je minder tabellen in je join hoeft aan te roepen en het selecteren aanzienlijk sneller zal gaan.

Bijvoorbeeld isolatie toevoegen aan de tabel huis.

[ Voor 11% gewijzigd door Afvalzak op 16-03-2010 17:10 ]

[Verwijderd]

afvalzak schreef op dinsdag 16 maart 2010 @ 17:07:
Als dit soort query's echt vaak gaan voorkomen is het altijd een idee om te gaan de-normaliseren waardoor je minder tabellen in je join hoeft aan te roepen en het selecteren aanzienlijk sneller zal gaan.

Bijvoorbeeld isolatie toevoegen aan de tabel huis.

Gelukkig zeg je dit. Daar had ik ook aan gedacht. Punt is alleen dat een huis meerdere voorzieningen (kunnen) hebben. Ik kan deze dan comma-separated opnemen, maar daar zijn legio voorbeelden van dat dat problemen oplevert. Bitwise zit je met de 32-bit integer max. Dus ik dacht aan het datatype xml en dan met een mijnXmlKolom.exit() voorwaardes te checken.

Stel we hebben:

code:

<root>
<voorziening>tuin</voorziening>
<voorziening>open haard</voorziening>
<voorziening>zwembad</voorziening>
</root>

en we willen kijken welke huizen een open haard & tuin hebben; dus:

code:

<root>
<voorziening>tuin</voorziening>
<voorziening>open haard</voorziening>
</root>

Ik moet in staat zijn te checken of de onderste xml een complete (alle elementen in de onderste moeten in de bovenste zitten) of gedeeltelijke subset (een, of meerdere, elementen van de onderste moeten in de bovenste zitten).

Zijn er nog meer opties die ik dien te bekijken om te denormaliseren?

[ Voor 10% gewijzigd door Verwijderd op 17-03-2010 09:19 ]