[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

Optimize view

Hello,

Is it possible to optimize this view ?
CREATE VIEW [livelink].[WebNodes] AS SELECT a.OwnerID,a.DataID,a.ParentID, a.UserID,a.GroupID,
a.UPermissions,a.GPermissions,a.WPermissions,a.SPermissions,a.PermID, a.Name,a.DataType,a.SubType,
a.DComment,a.DCategory,a.CreateDate,a.ModifyDate,a.ExAtt1, a.Reserved,a.ReservedBy,a.ReservedDate,a.Ordering,
a.ChildCount,a.VersionNum, a.AssignedTo,a.Status,a.Priority,a.GIF,a.Catalog, b.FileName,b.FileType,b.DataSize,
b.ResSize,b.MimeType, c.Name OwnerName,a.OriginDataID, a.Major, a.Position FROM DTree
a LEFT OUTER JOIN DVersData b ON (a.DataID=b.DocID and (a.Major = b.Version
or (a.Major IS NULL and a.VersionNum=b.Version))), KUAF c WHERE a.CreatedBy=c.ID and b.VerType is NULL
GO


Thanks
bibi
0
bibi92
Asked:
bibi92
  • 4
1 Solution
 
Anthony PerkinsCommented:
It looks like intentionally or not you have a CROSS JOIN:
KUAF is not joined to anything.

If that is the case than use the correct syntax by losing the , and changing it to CROSS JOIN KUAF c
0
 
Anthony PerkinsCommented:
See below for the same VIEW in a readable format and using the correct syntax:

Make sure the CROSS JOIN is required.
CREATE VIEW [livelink].[WebNodes]
AS
SELECT  a.OwnerID,
        a.DataID,
        a.ParentID,
        a.UserID,
        a.GroupID,
        a.UPermissions,
        a.GPermissions,
        a.WPermissions,
        a.SPermissions,
        a.PermID,
        a.Name,
        a.DataType,
        a.SubType,
        a.DComment,
        a.DCategory,
        a.CreateDate,
        a.ModifyDate,
        a.ExAtt1,
        a.Reserved,
        a.ReservedBy,
        a.ReservedDate,
        a.Ordering,
        a.ChildCount,
        a.VersionNum,
        a.AssignedTo,
        a.Status,
        a.Priority,
        a.GIF,
        a.Catalog,
        b.FileName,
        b.FileType,
        b.DataSize,
        b.ResSize,
        b.MimeType,
        c.Name OwnerName,
        a.OriginDataID,
        a.Major,
        a.Position
FROM    DTree a
        LEFT OUTER JOIN DVersData b ON a.DataID = b.DocID 
			    AND (a.Major = b.Version OR (a.Major IS NULL AND a.VersionNum = b.Version))
        CROSS JOIN KUAF c
WHERE   a.CreatedBy = c.ID
        AND b.VerType IS NULL

Open in new window

0
 
Anthony PerkinsCommented:
Oops my mistake I see where you are relating KUAF, but do it this way:
CREATE VIEW [livelink].[WebNodes]
AS
SELECT  a.OwnerID,
        a.DataID,
        a.ParentID,
        a.UserID,
        a.GroupID,
        a.UPermissions,
        a.GPermissions,
        a.WPermissions,
        a.SPermissions,
        a.PermID,
        a.Name,
        a.DataType,
        a.SubType,
        a.DComment,
        a.DCategory,
        a.CreateDate,
        a.ModifyDate,
        a.ExAtt1,
        a.Reserved,
        a.ReservedBy,
        a.ReservedDate,
        a.Ordering,
        a.ChildCount,
        a.VersionNum,
        a.AssignedTo,
        a.Status,
        a.Priority,
        a.GIF,
        a.Catalog,
        b.FileName,
        b.FileType,
        b.DataSize,
        b.ResSize,
        b.MimeType,
        c.Name OwnerName,
        a.OriginDataID,
        a.Major,
        a.Position
FROM    DTree a
        LEFT OUTER JOIN DVersData b ON a.DataID = b.DocID 
			    AND (a.Major = b.Version OR (a.Major IS NULL AND a.VersionNum = b.Version))
        INNER JOIN KUAF c ON a.CreatedBy = c.ID
WHERE   b.VerType IS NULL

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
lcohanDatabase AnalystCommented:
Why not the INNER JOIN first?

CREATE VIEW [livelink].[WebNodes] AS
SELECT a.OwnerID,a.DataID,a.ParentID, a.UserID,a.GroupID, a.UPermissions,a.GPermissions,a.WPermissions,
            a.SPermissions,a.PermID, a.Name,a.DataType,a.SubType,a.DComment,a.DCategory,a.CreateDate,a.ModifyDate,
            a.ExAtt1, a.Reserved,a.ReservedBy,a.ReservedDate,a.Ordering, a.ChildCount,a.VersionNum, a.AssignedTo,
            a.[Status],a.Priority,a.GIF,a.[Catalog], b.[FileName],b.FileType,b.DataSize,b.ResSize,b.MimeType,
            c.Name OwnerName,a.OriginDataID, a.Major, a.Position
FROM DTree a
      INNER JOIN KUAF c ON a.CreatedBy=c.ID
      LEFT OUTER JOIN DVersData b ON (a.DataID=b.DocID and (a.Major = b.Version or (a.Major IS NULL and a.VersionNum=b.Version))) and b.VerType is NULL
GO
0
 
Anthony PerkinsCommented:
>>Why not the INNER JOIN first?<<
Because it does not make any difference in performance?
0
 
bibi92Author Commented:
Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now