relations.sql

WITH owner_to_direct_indirect_recipient AS (
                         SELECT f.FileID,
                                NULL AS SharedDate,
                                NULL AS SharingDataKey,
                                NULL AS Perms,
                                NULL AS ExpiredDate,
                                NULL AS IsPrinted,
                                NULL AS HasWatermark,
                                NULL AS SharingModifiedDate,
                                CONVERT(NVARCHAR(36), TODATETIMEOFFSET(fs.SharedDate, '+00:00'), 126) AS SharedDate2,
                                fs.SharingDataKey AS SharingDataKey2,
                                ss.[Right] AS Perms2,
                                CONVERT(NVARCHAR(36), TODATETIMEOFFSET(ss.ExpiredDate, '+00:00'), 126) AS ExpiredDate2,
                                ss.IsPrinted AS IsPrinted2,
                                ss.HasWatermark AS HasWatermark2,
                                CONVERT(NVARCHAR(36), TODATETIMEOFFSET(ss.ModifiedDate, '+00:00'), 126) AS SharingModifiedDate2,
                                fs.Status,
                                fs.SenderID
                         FROM dbo.[File] f, dbo.[FileSharing] fs, dbo.SharingSetting ss
                         WHERE f.FileID = fs.FileID
                         AND fs.SharingSettingID = ss.SharingSettingID
                         AND f.OwnerID = 'e3e8dd99-6b8b-4854-b050-7e44898eb1c2' AND fs.ReceiverID = '436821ed-c87c-3b21-83f2-975f92be7368'
                       ), recipient_to_owner_sharer AS (
                         SELECT f.FileID,
                                CONVERT(NVARCHAR(36), TODATETIMEOFFSET(fs.SharedDate, '+00:00'), 126) AS SharedDate,
                                fs.SharingDataKey,
                                ss.[Right] AS Perms,
                                CONVERT(NVARCHAR(36), TODATETIMEOFFSET(ss.ExpiredDate, '+00:00'), 126) AS ExpiredDate,
                                ss.IsPrinted,
                                ss.HasWatermark,
                                CONVERT(NVARCHAR(36), TODATETIMEOFFSET(ss.ModifiedDate, '+00:00'), 126) AS SharingModifiedDate,
                                NULL AS SharedDate2,
                                NULL AS SharingDataKey2,
                                NULL AS Perms2,
                                NULL AS ExpiredDate2,
                                NULL AS IsPrinted2,
                                NULL AS HasWatermark2,
                                NULL AS SharingModifiedDate2,
                                fs.Status,
                                fs.SenderID,
                                fs.FolderID
                         FROM dbo.[File] f, dbo.[FileSharing] fs, dbo.SharingSetting ss
                         WHERE f.FileID = fs.FileID
                         AND fs.SharingSettingID = ss.SharingSettingID
                         AND fs.ReceiverID = 'e3e8dd99-6b8b-4854-b050-7e44898eb1c2' AND f.OwnerID = '436821ed-c87c-3b21-83f2-975f92be7368'
                       ), recipient_to_recipient AS (
                         SELECT fs1.FileID,
                                fs1.FolderID,
                                fs1.SenderID AS SenderID1,
                                fs2.SenderID AS SenderID2,
                                fs1.SharedDate AS SharedDate1,
                                fs1.SharingDataKey AS SharingDataKey1,
                                fs1.Perms AS Perms1,
                                fs1.ExpiredDate AS ExpiredDate1,
                                fs1.IsPrinted AS IsPrinted1,
                                fs1.HasWatermark AS HasWatermark1,
                                fs1.SharingModifiedDate AS SharingModifiedDate1,
                                fs2.SharedDate AS SharedDate2,
                                fs2.SharingDataKey AS SharingDataKey2,
                                fs2.Perms AS Perms2,
                                fs2.ExpiredDate AS ExpiredDate2,
                                fs2.IsPrinted AS IsPrinted2,
                                fs2.HasWatermark AS HasWatermark2,
                                fs2.SharingModifiedDate AS SharingModifiedDate2,
                                fs1.Status AS Status1,
                                fs2.Status AS Status2
                         FROM (SELECT CONVERT(NVARCHAR(36), TODATETIMEOFFSET(fs.SharedDate, '+00:00'), 126) AS SharedDate,
                                      fs.SharingDataKey,
                                      ss.[Right] AS Perms,
                                      CONVERT(NVARCHAR(36), TODATETIMEOFFSET(ss.ExpiredDate, '+00:00'), 126) AS ExpiredDate,
                                      ss.IsPrinted,
                                      ss.HasWatermark,
                                      CONVERT(NVARCHAR(36), TODATETIMEOFFSET(ss.ModifiedDate, '+00:00'), 126) AS SharingModifiedDate,
                                      fs.FileID,
                                      fs.SenderID,
                                      fs.Status,
                                      fs.FolderID
                               FROM dbo.[FileSharing] fs, dbo.SharingSetting ss
                               WHERE fs.SharingSettingID = ss.SharingSettingID 
                               AND fs.ReceiverID = 'e3e8dd99-6b8b-4854-b050-7e44898eb1c2') fs1,
                              (SELECT CONVERT(NVARCHAR(36), TODATETIMEOFFSET(fs.SharedDate, '+00:00'), 126) AS SharedDate,
                                      fs.SharingDataKey,
                                      ss.[Right] AS Perms,
                                      CONVERT(NVARCHAR(36), TODATETIMEOFFSET(ss.ExpiredDate, '+00:00'), 126) AS ExpiredDate,
                                      ss.IsPrinted,
                                      ss.HasWatermark,
                                      CONVERT(NVARCHAR(36), TODATETIMEOFFSET(ss.ModifiedDate, '+00:00'), 126) AS SharingModifiedDate,
                                      fs.FileID,
                                      fs.SenderID,
                                      fs.Status
                               FROM dbo.[FileSharing] fs, dbo.SharingSetting ss
                               WHERE fs.SharingSettingID = ss.SharingSettingID 
                               AND fs.ReceiverID = '436821ed-c87c-3b21-83f2-975f92be7368') fs2, 
                               dbo.[File] f
                         WHERE fs1.FileID = fs2.FileID
                         AND fs1.FileID = f.FileID
                       )
                       SELECT f.FileID,
                              f.[Type],
                              f.IsFolder,
                              1 AS Ownership,
                             (CASE
                                WHEN m.NewRelativePath IS NULL THEN f.LocalRelativePath
                                ELSE m.NewRelativePath
                              END) AS LocalRelativePath,
                             (CASE
                                WHEN m.NewFileName IS NULL THEN f.[FileName]
                                ELSE m.NewFileName
                              END) AS [FileName],
                             (CASE
                                WHEN m.NewFileSize IS NULL THEN f.FileSize
                                ELSE m.NewFileSize
                              END) AS FileSize,
                             (CASE
                                WHEN m.NewCheckSum IS NULL THEN f.[CheckSum]
                                ELSE m.NewCheckSum
                              END) AS CheckSum,
                             (CASE
                                WHEN m.NewEncryptedCheckSum IS NULL THEN f.EncryptedCheckSum
                                ELSE m.NewEncryptedCheckSum
                              END) AS EncryptedCheckSum,
                             (CASE
                                WHEN m.NewEncryptedFileSize IS NULL THEN f.EncryptedFileSize
                                ELSE m.NewEncryptedFileSize
                              END) AS EncryptedFileSize,
                             f.EncryptedConvertedFileSize,
                             f.EncryptedConvertedCheckSum,
                             f.HasUpdatePending,
                             CONVERT(NVARCHAR(36), TODATETIMEOFFSET(f.LastPushUpdateDate, '+00:00'), 126) AS LastPushUpdateDate,
                             f.HasEncryptPending,
                             f.HasConvertPending,
                             CONVERT(NVARCHAR(36), TODATETIMEOFFSET(f.CreatedDate, '+00:00'), 126) AS CreatedDate,
                             (CASE
                                WHEN m.ModifiedDate IS NULL THEN CONVERT(NVARCHAR(36), TODATETIMEOFFSET(f.ModifiedDate, '+00:00'), 126)
                                ELSE CONVERT(NVARCHAR(36), TODATETIMEOFFSET(m.ModifiedDate, '+00:00'), 126)
                              END) AS ModifiedDate,
                             f.DataKey,
                             f.FileFormatVersion,
                             f.OwnerID,
                             NULL AS SenderID1,
                             s1.SenderID AS SenderID2,
                             NULL AS SharedDate1,
                             s1.SharedDate AS SharedDate2,
                             NULL AS SharingDataKey1,
                             s1.SharingDataKey AS SharingDataKey2,
                             NULL AS Perms1,
                             s1.Perms AS Perms2,
                             NULL AS ExpiredDate1,
                             s1.ExpiredDate AS ExpiredDate2,
                             NULL AS IsPrinted1,
                             s1.IsPrinted AS IsPrinted2,
                             NULL AS HasWatermark1,
                             s1.HasWatermark AS HasWatermark2,
                             NULL AS SharingModifiedDate1,
                             s1.SharingModifiedDate AS SharingModifiedDate2,
                             1 AS Status1,
                             s1.Status AS Status2,
                             1 AS SharingMode
                       FROM dbo.[File] f
                       LEFT JOIN dbo.EditedFile m ON f.FileID = m.OriginalFileID
                       INNER JOIN owner_to_direct_indirect_recipient s1
                       ON f.FileID = s1.FileID
                       UNION
                       SELECT f.FileID,
                              f.[Type],
                              f.IsFolder,
                              2 AS Ownership,
                              f2.LocalRelativePath,
                              f.[FileName],
                              f.FileSize,
                              f.[CheckSum],
                              f.EncryptedCheckSum,
                              f.EncryptedFileSize,
                              f.EncryptedConvertedFileSize,
                              f.EncryptedConvertedCheckSum,
                              f.HasUpdatePending,
                              CONVERT(NVARCHAR(36), TODATETIMEOFFSET(f.LastPushUpdateDate, '+00:00'), 126) AS LastPushUpdateDate,
                              f.HasEncryptPending,
                              f.HasConvertPending,
                              CONVERT(NVARCHAR(36), TODATETIMEOFFSET(f.CreatedDate, '+00:00'), 126) AS CreatedDate,
                              CONVERT(NVARCHAR(36), TODATETIMEOFFSET(f.ModifiedDate, '+00:00'), 126) AS ModifiedDate,
                              f.DataKey,
                              f.FileFormatVersion,
                              f.OwnerID,
                              s2.SenderID AS SenderID1,
                              NULL AS SenderID2,
                              s2.SharedDate AS SharedDate1,
                              NULL AS SharedDate2,
                              s2.SharingDataKey AS SharingDataKey1,
                              NULL AS SharingDataKey2,
                              s2.Perms AS Perms1,
                              NULL AS Perms2,
                              s2.ExpiredDate AS ExpiredDate1,
                              NULL AS ExpiredDate2,
                              s2.IsPrinted AS IsPrinted1,
                              NULL AS IsPrinted2,
                              s2.HasWatermark AS HasWatermark1,
                              NULL AS HasWatermark2,
                              s2.SharingModifiedDate AS SharingModifiedDate1,
                              NULL AS SharingModifiedDate2,
                              s2.Status AS Status1,
                              1 AS Status2,
                              2 AS SharingMode
                       FROM dbo.[File] f, recipient_to_owner_sharer s2, dbo.[File] f2
                       WHERE f.FileID = s2.FileID AND s2.FolderID = f2.FileID
                       UNION
                       SELECT f.FileID,
                              f.[Type],
                              f.IsFolder,
                              2 AS Ownership,
                              f2.LocalRelativePath,
                              f.[FileName],
                              f.FileSize,
                              f.[CheckSum],
                              f.EncryptedCheckSum,
                              f.EncryptedFileSize,
                              f.EncryptedConvertedFileSize,
                              f.EncryptedConvertedCheckSum,
                              f.HasUpdatePending,
                              CONVERT(NVARCHAR(36), TODATETIMEOFFSET(f.LastPushUpdateDate, '+00:00'), 126) AS LastPushUpdateDate,
                              f.HasEncryptPending,
                              f.HasConvertPending,
                              CONVERT(NVARCHAR(36), TODATETIMEOFFSET(f.CreatedDate, '+00:00'), 126) AS CreatedDate,
                              CONVERT(NVARCHAR(36), TODATETIMEOFFSET(f.ModifiedDate, '+00:00'), 126) AS ModifiedDate,
                              f.DataKey,
                              f.FileFormatVersion,
                              f.OwnerID,
                              s3.SenderID1,
                              s3.SenderID2,
                              s3.SharedDate1,
                              s3.SharedDate2,
                              s3.SharingDataKey1,
                              s3.SharingDataKey2,
                              s3.Perms1,
                              s3.Perms2,
                              s3.ExpiredDate1,
                              s3.ExpiredDate2,
                              s3.IsPrinted1,
                              s3.IsPrinted2,
                              s3.HasWatermark1,
                              s3.HasWatermark2,
                              s3.SharingModifiedDate1,
                              s3.SharingModifiedDate2,
                              s3.Status1,
                              s3.Status2,
                              3 AS SharingMode
                       FROM dbo.[File] f, recipient_to_recipient s3, dbo.[File] f2
                       WHERE f.FileID = s3.FileID AND s3.FolderID = f2.FileID

Client Execution Time   22:21:07        22:20:58        22:20:56        22:20:53        22:20:51        22:20:49        22:20:47        22:20:43        22:20:41        22:20:38        
Query Profile Statistics                                                                                    
  Number of INSERT, DELETE and UPDATE statements    0       0       0       0       0       0       0       0       0       0       0.0000
  Rows affected by INSERT, DELETE, or UPDATE statements 0       0       0       0       0       0       0       0       0       0       0.0000
  Number of SELECT statements   1       1       1       1       1       1       1       1       1       1       1.0000
  Rows returned by SELECT statements    8       8       8       8       8       8       8       8       8       8       8.0000
  Number of transactions    0       0       0       0       0       0       0       0       0       0       0.0000
Network Statistics                                                                                  
  Number of server roundtrips   1       1       1       1       1       1       1       1       1       1       1.0000
  TDS packets sent from client  8       8       8       8       8       8       8       8       8       8       8.0000
  TDS packets received from server  4       4       4       4       4       4       4       4       4       4       4.0000
  Bytes sent from client    30810       30810       30810       30810       30810       30810       30810       30810       30810       30810       30810.0000
  Bytes received from server    13871       13871       13871       13871       13871       13871       13871       13871       13871       13871       13871.0000
Time Statistics                                                                                 
  Client processing time    163     346     925     164     159     344     1320        529     328     978     525.6000
  Total execution time  729     1083        1844        1621        718     1092        3104        1212        1612        2098        1511.3000
  Wait time on server replies   566     737     919     1457        559     748     1784        683     1284        1120        985.7000