[MSSQL] Vanaf 18 joins doet de query niets meer

[wvansl]

Hallo,
Ik heb een query die vanaf een aantal nummer van joins niets meer doet.
Bv, 18 joins, query werkt perfect (output in minder dan 1s). Zet ik er 1 join bij doet die niets meer (output langer dan 30 minuten).

SQL zelf heeft een limiet van 256 joins ofzo, hoe komt het dat ik zo snel al in de problemen geraak?
Database zelf is helemaal niet zo groot (paar duizend rijen in totaal).

Voorbeeld query: (test query dus niet de query zelf)

SQL: testQuery.sql

SELECT  
  P.Name + ' ' + P.FirstName AS Employee
FROM
  HR_TimeSheetDetail TSD
  INNER JOIN VEH_CostCentrale CC ON CC.ID = TSD.ExecutingStructureID
  INNER JOIN HR_TimeSheet TS ON TS.ID = TSD.TimeSheetID
  INNER JOIN VEH_Transport TR ON TR.ID = TSD.VehicleTask
  INNER JOIN VEH_Vehicle VEH ON VEH.ID = TR.VehicleID
  LEFT JOIN VEH_Vehicle TRAILER ON TRAILER.ID = TR.TrailerID
  INNER JOIN VEH_VehicleType VTYPE ON VTYPE.ID = VEH.VehicleTypeID
  INNER JOIN HR_HEI_Employee EMP ON EMP.ID = TS.EmployeeID
  INNER JOIN COM_Person P ON P.ID = EMP.PersonID
  INNER JOIN COM_Site SITEMP ON SITEMP.ID = EMP.SiteID
  INNER JOIN COM_Site SIT01 ON SIT01.ID = EMP.SiteID
  INNER JOIN COM_Site SIT02 ON SIT02.ID = EMP.SiteID
  INNER JOIN COM_Site SIT03 ON SIT03.ID = EMP.SiteID
  INNER JOIN COM_Site SIT04 ON SIT04.ID = EMP.SiteID
  INNER JOIN COM_Site SIT05 ON SIT05.ID = EMP.SiteID
  INNER JOIN COM_Site SIT06 ON SIT06.ID = EMP.SiteID
  INNER JOIN COM_Site SIT07 ON SIT07.ID = EMP.SiteID
  INNER JOIN COM_Site SIT08 ON SIT08.ID = EMP.SiteID
  INNER JOIN COM_Site SIT09 ON SIT09.ID = EMP.SiteID
  INNER JOIN COM_Site SIT10 ON SIT10.ID = EMP.SiteID
  INNER JOIN COM_Site SIT11 ON SIT11.ID = EMP.SiteID
  INNER JOIN COM_Site SIT12 ON SIT12.ID = EMP.SiteID
  INNER JOIN COM_Site SIT13 ON SIT13.ID = EMP.SiteID
  INNER JOIN COM_Site SIT14 ON SIT14.ID = EMP.SiteID
  INNER JOIN COM_Site SIT15 ON SIT15.ID = EMP.SiteID
  INNER JOIN COM_Site SIT16 ON SIT16.ID = EMP.SiteID
  INNER JOIN COM_Site SIT17 ON SIT17.ID = EMP.SiteID
  INNER JOIN COM_Site SIT18 ON SIT18.ID = EMP.SiteID
  INNER JOIN COM_Site SIT19 ON SIT19.ID = EMP.SiteID
  INNER JOIN COM_Site SIT20 ON SIT20.ID = EMP.SiteID
  --INNER JOIN COM_Site SIT21 ON SIT21.ID = EMP.SiteID
  --INNER JOIN COM_Site SIT22 ON SIT22.ID = EMP.SiteID

Zo werkt die dus in minder dan 1s, uncomment ik een van de onderste 2 joins of voeg ik een andere join toe dan doet die niets meer.
Iemand ideeen hoe dit op te lossen?
Moet echt veel namen tonen van veel tabellen... (is voor een rapport te genereren)

[Woy]

kijk eerst eens naar het estimated execution plan. Zit daar veel verschil tussen tussen 18 en 19 joins?

Verder vraag ik me af of je data model of aanpak wel helemaal goed is als je zo vaak op dezelfde tabel moet joinen.

[pasz]

Zonder tabel definities kunnen we hier niet zoveel mee. Ik denk dat je iets als een PIVOT wil maken ?
Welke versie gebruik je ? PIVOT wordt vanaf 2005 ondersteund.

[wvansl]

Woy schreef op dinsdag 23 maart 2010 @ 13:54:
kijk eerst eens naar het estimated execution plan. Zit daar veel verschil tussen tussen 18 en 19 joins?

Verder vraag ik me af of je data model of aanpak wel helemaal goed is als je zo vaak op dezelfde tabel moet joinen.

Bij estimated execution plan voert die de query wel succesvol uit. Dat is het rare

En het is niet steeds op dezelfde tabel joinen hoor maar is gewoon bij wijze van test gedaan.
Bij de echte query stopt die na 18 joins in SQL 2008, bij deze test is het na een stuk of 24 joins in 2008 en 16 joins in 2005.

[wvansl]

pasz schreef op dinsdag 23 maart 2010 @ 13:55:
Zonder tabel definities kunnen we hier niet zoveel mee. Ik denk dat je iets als een PIVOT wil maken ?
Welke versie gebruik je ? PIVOT wordt vanaf 2005 ondersteund.

Tabel definities maakt weinig uit want eender welke tabel ik op join gaat het de mist in vanaf dat ik boven de 18 kom.
De tabel waar ik in het voorbeeld op join (COM_Site) bestaat uit twee velden (ID, Name) met 30 records in...

PIVOT wil ik niet gebruiken, doel van de query is iets totaal anders.

[HMS]

Als je zoveel joins moet doen lijkt het mij dat er iets mis is met je datamodel of dat je teveel in 1 query wil stoppen.

[wvansl]

HMS schreef op dinsdag 23 maart 2010 @ 14:01:
Als je zoveel joins moet doen lijkt het mij dat er iets mis is met je datamodel of dat je teveel in 1 query wil stoppen.

Daar gaat het niet zo om, het feit is gewoon dat die vanaf 1 join meer volldig niets meer doet en dat het zonder een extra join binnen de seconde gedaan is.

[pedorus]

Dat 'probleem' lijkt me duidelijk, maar wat is het praktisch nut van zo'n query? Werkt het trouwens wel met VIEWs? Daarnaast staat die LEFT JOIN er een beetje vreemd tussen al die INNER JOINs.

[HMS]

Als je toch programmatisch een rapport wil genereren dan zou ik die query op splitsen in meerdere SELECTs ipv zo'n JOIN constructie.

Daarbij vind ik een query die 1 seconde duurt vrij lang (zeker voor zo'n kleine database).

Maar misschien is dat iet wat offtopic.

[wvansl]

Nogmaals het is een test query. De echte query is veel ingewikkelder daarom deze versimpelde query die hetzelfde probleem heeft als de echte query

[HMS]

Tja, het kan zijn dat de SQL server out of memory gaat met zoveel joins? Wat is de memory / CPU usage als de query zo lang duurt?

Of misschien dat de server ergens intern tegen een bug aanloopt die zich alleen voordoet bij zoveel joins?

Al getest op een kleinere dataset met dezelfde query?

Anders zit er niks anders op dan de query doormidden hakken (oid, in ieder geval opsplitsen).

[leuk_he]

Hier nog zoiets.

http://social.answers.mic...78-4844-a33b-3cc982ee6d8a

worarround zoeken lijkt me.

[wvansl]

Heb uiteindelijk de volledige query in een stored procedure gestoken en daarin werkt die dus wel perfect en snel...

Raar gedoe if you ask me...

[pedorus]

Het is inderdaad raar om een query te hebben met meer dan 10 joins... Overigens kan ik het probleem niet reproduceren hier in 2008 (100 joins duurt wel even en met 1 left join en geen where conditie gaat er toch iets mis ('There is insufficient system memory in resource pool 'internal' to run this query.'), maar geen eindeloze tijdsduur).

[RobIII]

leuk_he schreef op dinsdag 23 maart 2010 @ 14:57:
Hier nog zoiets.

http://social.answers.mic...78-4844-a33b-3cc982ee6d8a

worarround zoeken lijkt me.

Want Access == MSSQL

[wvansl]

Vandaag weer problemen...
Nieuwe database backup dus create script van stored procedure (welke perfect & snel werkte) in nieuwe database. Run de procedure, blijft weer eeuwigheid duren...
Begin er een beetje moe van te worden

[pasz]

Zoals eerder geopperd in dit topic is een join zoals deze een alarm dat het datamodel niet spoort.

Je probleem ruikt een beetje naar een oud Sybase (moeder van SQL Server) probleem. Daar moest je af en toe een veelvoud van een aantal joins doen. Dat was pas raar.

Heb je misschien meer dan 128 verwijzingen van tabellen in je query ?

[wvansl]

Update:
Nu de query terug uit de stored procedure gehaald, gewoon uitgevoerd en kreeg wel resultaten. En vanaf dan werkt mijn stored procedure ook weer.
Ik dacht, misschien dat er wat zaken in het geheugen blijven zitten ofzo dus ben opnieuw opgestart en opnieuw de stored procedure uitgevoerd en hij werkt nog steeds nu...

[leuk_he]

RobIII schreef op dinsdag 23 maart 2010 @ 17:58:
[...]

Want Access == MSSQL

ze hebben het toch Server 2003 to Server 2008 dan neem ik aan dat dat over SQL server gaat.

[wvansl]

Voor de mensen die nog steeds denken dat het een raar datamodel/query is:

SQL: testQuery.sql

DECLARE @SITE UNIQUEIDENTIFIER
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @SITE = '10000000-0000-0000-0000-000000000036' -- Mechelen
SET @StartDate = '01/01/2009' -- mm/dd/yy
SET @EndDate = '01/31/2010' -- mm/dd/yy

  SELECT DISTINCT 
--  P.Name + ' ' + P.FirstName AS Employee,
--  SITEMP.Name AS [Site-Employee],
--  VEHOWNER,
--  VEHUSER,
--  TROWNER,
--  TRUSER,
  CC.Name AS ExecutingStructure,
  VTYPE.NameNL As VehicleTypeNL,
  VTYPE.NameFR AS VehicleTypeFR,
  RIGHT('0000' + CONVERT(VARCHAR, VEH.Number), 4) AS VehicleNumber,
  RIGHT('0000' + CONVERT(VARCHAR, TRAILER.Number), 4) AS TrailerNumber,
  CONVERT (VARCHAR,TS.StartDate, 105) AS Date,
  LEFT(CONVERT(NVARCHAR, TSD.StartHour, 8), 5) AS StartHour,
  LEFT(CONVERT(NVARCHAR, TSD.EndHour, 8), 5) AS EndHour,
  (TSD.Minutes/15) AS NumberOfQuarters,
  PIF.Price AS PricePerQuarter,
  ((TSD.Minutes/15)*PIF.Price) As TotalPrice,
  TS.StartDate
  
FROM
  HR_TimeSheetDetail TSD
  INNER JOIN VEH_CostCentrale CC ON CC.ID = TSD.ExecutingStructureID
  INNER JOIN HR_TimeSheet TS ON TS.ID = TSD.TimeSheetID
  INNER JOIN VEH_Transport TR ON TR.ID = TSD.VehicleTask
  INNER JOIN VEH_Vehicle VEH ON VEH.ID = TR.VehicleID
  LEFT JOIN VEH_Vehicle TRAILER ON TRAILER.ID = TR.TrailerID
  INNER JOIN VEH_VehicleType VTYPE ON VTYPE.ID = VEH.VehicleTypeID
--  LEFT JOIN HR_HEI_Employee EMP ON EMP.ID = TS.EmployeeID
--  LEFT JOIN HR_INT_Contractor CON ON CON.ID = TS.EmployeeID
--  INNER JOIN COM_Person P ON P.ID = EMP.PersonID OR P.ID = CON.ID
--  INNER JOIN COM_Site SITEMP ON SITEMP.ID = EMP.SiteID
  INNER JOIN VEH_CostCentrale SITEXECUTING ON SITEXECUTING.ID = TSD.ExecutingStructureID
  LEFT OUTER JOIN VEH_ParamsInternalFacturation AS PIF ON (PIF.VehicleTypeID = VTYPE.ID AND PIF.Volume = VEH.MaxCapacityMixer)
--  INNER JOIN COM_Company AS COMP ON COMP.ID = SITEMP.CompanyID  
  -- Vehicle Assignment
  LEFT JOIN (
    SELECT VA.ValidFrom, VA.ValidTill, VA.VehicleID, VA.OwnerID AS VEHOWNERID, VA.UserID AS VEHUSERID--, VAOWNER.Name AS VEHOWNER, VAUSER.Name AS VEHUSER
    FROM VEH_VehicleAssignment VA
    --INNER JOIN COM_Site VAOWNER ON VAOWNER.ID = VA.OwnerID
    --INNER JOIN COM_Site VAUSER ON VAUSER.ID = VA.UserID
  ) VEHASS ON VEHASS.VehicleID = VEH.ID
  -- Trailer Assignment
  LEFT JOIN (
    SELECT TR.ValidFrom, TR.ValidTill, TR.VehicleID, TR.OwnerID AS TROWNERID, TR.UserID AS TRUSERID--, TROWNER.Name AS TROWNER, TRUSER.Name AS TRUSER
    FROM VEH_VehicleAssignment TR
    --INNER JOIN COM_Site TROWNER ON TROWNER.ID = TR.OwnerID
    --INNER JOIN COM_Site TRUSER ON TRUSER.ID = TR.UserID
  ) TRAILERASS ON TRAILERASS.VehicleID = TRAILER.ID

WHERE 
  (VEHOWNERID = @SITE 
  OR TROWNERID = @SITE)
  AND (TSD.ExecutingStructureID <> @Site)
  AND TS.StartDate >= @StartDate
  AND (TS.StartDate < DATEADD(dd, 1, @EndDate))
  AND (VEHASS.ValidFrom <= TS.StartDate AND (VEHASS.ValidTill IS NULL OR VEHASS.ValidTill >= TS.StartDate) 
  OR TRAILERASS.ValidFrom <= TS.StartDate AND (TRAILERASS.ValidTill IS NULL OR TRAILERASS.ValidTill >= TS.StartDate))
  
ORDER BY 
  TS.StartDate

Dit werkt dus op dit moment weer niet meer op sql 20005, wel op sql 2008...

[Guillome]

wvansl schreef op woensdag 24 maart 2010 @ 08:44:

Begin er een beetje moe van te worden

Nu al? Ik zou toch echt even lezen wat mensen hier opperen, of uitleggen dat jouw database WEL goed in elkaar zit.. Daar is ie al

[ Voor 4% gewijzigd door Guillome op 24-03-2010 10:49 ]

[pedorus]

wvansl schreef op woensdag 24 maart 2010 @ 10:43:
Voor de mensen die nog steeds denken dat het een raar datamodel/query is:
[...]
Dit werkt dus op dit moment weer niet meer op sql 20005, wel op sql 2008...

Dat blijf ik inderdaad denken. Die convert-zaken zijn typisch iets voor de representatie, en moeten niet door de database worden afgehandeld. Verder dat stukje met die 2 left joins en die 2 subqueries op VEH_VehicleAssignment. Hier is iets geks: is die of absoluut (dus geen en/of), of zit er een bugje in de query waarbij je de periode van het voertuig kan pakken, en de assignment van de trailer of omgekeerd? Daarnaast: waarom hebben we niet gewoon een view met voertuigen, trailers, en eigenenaren/periode tot onze beschikking? Die beide subqueries wil je niet zo in deze query krijgen. Het is ook inconsistent om daar met een subquery te hernoemen, en daarvoor alleen met een tabelalias trouwens (alleen alias is beter). Eigenlijk wil je alle left joins overhevelen naar VIEWs. Als je dat niet wil, zet dan in ieder geval alle inner joins vooraan. Gebeurd het eigenlijk dat de trailer een andere eigenaar heeft dan het voertuig, en waarom is er geen aparte tabel voor trailers?

Al met al is deze query veel te lang, waardoor hij zeer lastig is te doorgronden. Met een paar VIEWs moet dat wel verbeterd kunnen worden, en dan zijn de queries ook nog makkelijker te doorgronden voor de database.

[wvansl]

pedorus schreef op woensdag 24 maart 2010 @ 12:32:
[...]

Dat blijf ik inderdaad denken. Die convert-zaken zijn typisch iets voor de representatie, en moeten niet door de database worden afgehandeld. Verder dat stukje met die 2 left joins en die 2 subqueries op VEH_VehicleAssignment. Hier is iets geks: is die of absoluut (dus geen en/of), of zit er een bugje in de query waarbij je de periode van het voertuig kan pakken, en de assignment van de trailer of omgekeerd? Daarnaast: waarom hebben we niet gewoon een view met voertuigen, trailers, en eigenenaren/periode tot onze beschikking? Die beide subqueries wil je niet zo in deze query krijgen. Het is ook inconsistent om daar met een subquery te hernoemen, en daarvoor alleen met een tabelalias trouwens (alleen alias is beter). Eigenlijk wil je alle left joins overhevelen naar VIEWs. Als je dat niet wil, zet dan in ieder geval alle inner joins vooraan. Gebeurd het eigenlijk dat de trailer een andere eigenaar heeft dan het voertuig, en waarom is er geen aparte tabel voor trailers?

Al met al is deze query veel te lang, waardoor hij zeer lastig is te doorgronden. Met een paar VIEWs moet dat wel verbeterd kunnen worden, en dan zijn de queries ook nog makkelijker te doorgronden voor de database.

Views kan ik naar mijn weten niet echt gebruiken omdat ik die parameters zou moeten meegeven.

Uitleg query:
Je hebt Timesheets voor Employees.
Ik wil dan die timesheets eruit hebben welke te maken hebben met transporten (met een vehicle en/of trailer)
Voor die transporten wil ik de vehicle assignments en trailer assignments (om te zien bij welk bedrijf de vehicle/trailer toehoeren) hebben op de datum van de timesheet. (dit omdat die assignments nogal vaak wisselen).

Dus heb ik in mijn subqueries 2 parameters nodig:
- vehicleID/trailerID
- datum timesheet

Dit kan ik naar mijn weten dus niet oplossen met een enkele view oid.
Het rare is gewoon dat het de ene keer heel snel gaat en de andere keer heel traag.

De query wordt gebruikt in SQL Reporting Services (2005, niet 2008). Omdat er nogal veel gegevens in het rapport moeten komen heb je dus zoveel joins wel nodig.

[Coltrui]

Gebruik je nergens locking? Als je deze query met zoveel joins zoder lockspecificaties loslaat op een multi-useromgeving gaat het geheid een keer fout...

[pedorus]

wvansl schreef op woensdag 24 maart 2010 @ 13:50:
Views kan ik naar mijn weten niet echt gebruiken omdat ik die parameters zou moeten meegeven.

Je kunt toch prima een where op een view doen zodat je niet zo'n ellenlange query ziet? Ik bedoel dus dingen als:

SQL:

create view VehicleWithAssignee as
    select * from VEH_Vehicle v left join VEH_VehicleAssignment a on v.ID=a.VehicleId;
go
select distinct v.Number from VehicleWithAssignee v where v.OwnerId=@SITE;

Als ik het goed snap zou je regel 44-56 eruit kunnen gooien als je deze view 2x gebruikt.

Voor die transporten wil ik de vehicle assignments en trailer assignments (om te zien bij welk bedrijf de vehicle/trailer toehoeren) hebben op de datum van de timesheet. (dit omdat die assignments nogal vaak wisselen).

Je gaat niet in op dat geclaimde bugje. Toch lijkt me dat nogal verwarrend voor de DB. Ik snap zelf eigenlijk ook niet precies hoe het met die 2 left joins en 2 or's in de where-clause nu zit. Kan een vehicle aan X toegewezen zijn en de bijbehorende trailer tegelijkertijd aan Y waarbij X is not Y? Waarom staat dit niet altijd bij vehicle aangegeven in plaats van soms ook bij de trailer? Kunnen een aantal zaken die in de where staan, verhuisd worden naar een on-clause van een left-join (selecteren op datum bijvoorbeeld)?

(En verder neem ik aan dat er niet tegelijkertijd nog tal van schrijfacties op deze db zijn. Dat zou inderdaad wel eens wat locking-problemen kunnen geven.)

[leuk_he]

Coltrui schreef op woensdag 24 maart 2010 @ 14:38:
Gebruik je nergens locking? Als je deze query met zoveel joins zoder lockspecificaties loslaat op een multi-useromgeving gaat het geheid een keer fout...

Sinds wanneer heeft een query last van locks? (echt, dat zou nieuw zijn voor me, maar ik blijf dingen leren)