Skip to content

Instantly share code, notes, and snippets.

@pcdinh

pcdinh/relations.sql

Created October 15, 2014 14:55
Show Gist options
  • Save pcdinh/b9e832306b46ca3b396c to your computer and use it in GitHub Desktop.
Save pcdinh/b9e832306b46ca3b396c to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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
@pcdinh
Copy link
Author

pcdinh commented Oct 15, 2014 

Client Execution Time   22:18:02        22:18:01        22:17:58        22:17:53        22:17:51        22:17:48        22:17:46        22:17:43        21:57:34        21:53:58        
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       0       1       0.9000
  Rows returned by SELECT statements    8       8       8       8       8       8       8       8       0       8       7.2000
  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       1       4       3.7000
  Bytes sent from client    30810       30810       30810       30810       30810       30810       30810       30810       30052       30810       30734.2000
  Bytes received from server    13871       13871       13871       13871       13871       13871       13871       13871       762     13871       12560.1000
Time Statistics                                                                                 
  Client processing time    329     941     3383        352     1280        348     349     348     33      1657        902.0000
  Total execution time  891     1504        3946        1295        2165        1101        1089        1631        942     2567        1713.1000
  Wait time on server replies   562     563     563     943     885     753     740     1283        909     910     811.1000

@pcdinh
Copy link
Author

pcdinh commented Oct 15, 2014 

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment