---

Hej jeg har følgende query.

Select DISTINCT
      a.Createby,
      a.AcceptByRev,
      a.AcceptByEmp,
      a.FormCycleId,
      a.CRId,
      a.CreateDate as today,
      a.EmpFormId,
      f.formid,
      h.Description AS cycledescription,
      i.Name,
      i.Description,
      b.CycleId AS REWCycleId,
      b.ConsultantId AS REWConsultantId,
      g.ConsultantName AS REWConsultantName,
      b.RolesConsultantId AS REWRolesConsultantId,
      b.RoleId AS REWRoleId,
      e.RoleText AS REWRoleText,
      e.Description AS REWDescription,
      c.CycleId AS MYCycleId,
      c.ConsultantId AS MYConsultantId,
      c.RolesConsultantId AS MYRolesConsultantId,
      j.ConsultantName AS MYConsultantName,
      c.RoleId AS MYRoleId,
      d.RoleText AS MYRoleText,
      d.Description AS MYDescription
   from
      PDRFormValue a
      LEFT JOIN PDRConsultantRoles b ON b.crid = a.crid AND
b.RolesConsultantId = 'mkruse'
      LEFT JOIN PDRConsultantRoles c ON c.crid = a.crid AND c.ConsultantId
= 'mkruse'
      LEFT JOIN PDRRoles d ON d.RoleId = c.RoleId
      LEFT JOIN PDRRoles e ON e.RoleId = b.RoleId
      INNER JOIN pdrformcycle f ON f.formcycleid = a.formcycleid
      LEFT JOIN Consultant g ON g.ConsultantId = b.ConsultantId OR
g.ConsultantId = c.RolesConsultantId
      LEFT JOIN Consultant j ON j.ConsultantId = c.ConsultantId
      LEFT JOIN PDRFormCycle h ON a.FormCycleId = h.FormCycleId AND
(h.CycleId = b.CycleId or h.CycleId = c.CycleId)
      LEFT JOIN PDRForm i ON i.FormId = f.FormId
   WHERE
      a.CreateDate IS NOT NULL
      AND (a.AcceptByRev IS NULL
      OR a.AcceptByEmp IS NULL)


Men den er meget langsom. Detter er sikkert pga. de mange LEFT JOIN's
på de samme rækker.

Er der nogen der har et foreslag til hvad der skal til for at gøre den
hurtigere?

Mvh,
Morten

---

On 12 Mar 2003 02:04:22 -0800, morten.kruse@privat.dk (Kruse) wrote:

>Hej jeg har følgende query.
>
>Select DISTINCT
>      a.Createby,
>      a.AcceptByRev,
>      a.AcceptByEmp,
>      a.FormCycleId,
>      a.CRId,
>      a.CreateDate as today,
>      a.EmpFormId,
>      f.formid,
>      h.Description AS cycledescription,
>      i.Name,
>      i.Description,
>      b.CycleId AS REWCycleId,
>      b.ConsultantId AS REWConsultantId,
>      g.ConsultantName AS REWConsultantName,
>      b.RolesConsultantId AS REWRolesConsultantId,
>      b.RoleId AS REWRoleId,
>      e.RoleText AS REWRoleText,
>      e.Description AS REWDescription,
>      c.CycleId AS MYCycleId,
>      c.ConsultantId AS MYConsultantId,
>      c.RolesConsultantId AS MYRolesConsultantId,
>      j.ConsultantName AS MYConsultantName,
>      c.RoleId AS MYRoleId,
>      d.RoleText AS MYRoleText,
>      d.Description AS MYDescription
>   from
>       PDRFormValue a
>      LEFT JOIN PDRConsultantRoles b ON b.crid = a.crid AND
>b.RolesConsultantId = 'mkruse'
>      LEFT JOIN PDRConsultantRoles c ON c.crid = a.crid AND c.ConsultantId
>= 'mkruse'
>      LEFT JOIN PDRRoles d ON d.RoleId = c.RoleId
>      LEFT JOIN PDRRoles e ON e.RoleId = b.RoleId
>      INNER JOIN pdrformcycle f ON f.formcycleid = a.formcycleid
>      LEFT JOIN Consultant g ON g.ConsultantId = b.ConsultantId OR
>g.ConsultantId = c.RolesConsultantId
>      LEFT JOIN Consultant j ON j.ConsultantId = c.ConsultantId
>      LEFT JOIN PDRFormCycle h ON a.FormCycleId = h.FormCycleId AND
>(h.CycleId = b.CycleId or h.CycleId = c.CycleId)
>      LEFT JOIN PDRForm i ON i.FormId = f.FormId
>   WHERE
>      a.CreateDate IS NOT NULL
>      AND (a.AcceptByRev IS NULL
>      OR a.AcceptByEmp IS NULL)
>
>
>Men den er meget langsom. Detter er sikkert pga. de mange LEFT JOIN's
>på de samme rækker.

Min erfaring er at ikke bare LEFT JOIN's, men også "alternative"
join-udtryk (specielt OR) har det med at være dårlige for performance.

>Er der nogen der har et foreslag til hvad der skal til for at gøre den
>hurtigere?

Det er lidt svært uden at se dine tabeldefinitioner - specielt primær-
og fremmednøgler. Samt eventuelt en tekstuel beskrivelse af hvad
tabellerne indeholder, og hvad du gerne vil have ud ...

Specielt er det interessant om det kan forekomme at både joinet til
tabel b og tabel c indeholder matchende rækker ...

--
Nis Jørgensen
Amsterdam

Please include only relevant quotes, and reply below the quoted text. Thanks