DA11, Looking for an SQL Query for all searches run with how long each search took

The Searches table does not lis the Search duration. This can be obtained by looking at the Search duration for each Index Volume. The query is a bit involved as it is designed to active as well as Accepted Searches. It is also designed to run against a singel SearchID but could be modified to loop against all Accepted Searches:

DECLARE @SearchID nvarchar(10) SET @SearchID = AA ---Change the SearchID here
IF ((SELECT StatusID FROM tblIntSearches WHERE SearchID = @SearchID) = 7)
BEGIN
PRINT ''
PRINT 'Begin output from tblSearchVaultsArchived for SearchID ' + @SearchID + ' in Review'
PRINT ''
SELECT tis.Name AS 'Search Name'
     , ts1.Name AS 'Search State'
     , tv.KVSVaultName AS 'Archive Name'
     , tsva.StatusID AS 'Archive Search StatusID'
     , ts2.Name AS 'Archive Search Status'
     , tv.KVSVaultEntryID
     , tsva.IndexVolumeSetID AS 'Accelerator Index Volume ID'
     , ISNULL(tivs.EVIndexVolumeSetID,-1) AS 'EV Index Volume ID'
     , ISNULL(tivs.FirstItemSeqNum, -1) AS 'First Item Sequence Number'
     , tsva.Info AS 'Information'
     , tsva.NumHits AS 'Number of Hits'
     , tsva.StartDate AS 'Index Volume Search StartDate', tsva.EndDate AS 'Index Volume Search EndDate'
     , CAST(CONVERT(nvarchar(10), DATEDIFF(DD, tsva.StartDate, tsva.EndDate)) AS nvarchar(10)) + ' days, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsva.StartDate, tsva.EndDate)))%86400000/3600000 AS nvarchar(10)) + ' hrs, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsva.StartDate, tsva.EndDate)))%86400000%3600000/60000 AS nvarchar(10)) + ' mins, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsva.StartDate, tsva.EndDate)))%86400000%3600000%60000/1000 AS nvarchar(10)) + '.' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsva.StartDate, tsva.EndDate)))%86400000%3600000%60000%1000 AS nvarchar(10)) + ' secs' AS 'Index Volume Search Time'
     , tv.VaultServerID, tvs1.VaultServer 'Vault Server Name'
     , tv.VaultStoreID, tvs2.Name 'Vault Store Name'
     , tv.IndexServerID, tis2.IndexServiceEntryID
FROM tblSearchVaultsArchived tsva
JOIN tblIntSearches tis ON tsva.SearchID = tis.SearchID
JOIN tblStatus ts1 ON tis.StatusID = ts1.StatusID
JOIN tblStatus ts2 ON tsva.StatusID = ts2.StatusID
FULL OUTER JOIN tblVaults tv ON tsva.VaultID = tv.VaultID
JOIN tblVaultServers tvs1 ON tv.VaultServerID = tvs1.VaultServerID
JOIN tblVaultStore tvs2 ON tv.VaultStoreID = tvs2.VaultStoreID
FULL OUTER JOIN tblIndexVolumeSet tivs ON tsva.IndexVolumeSetID = tivs.IndexVolumeSetID
FULL OUTER JOIN tblIndexService tis2 ON tv.IndexServerID = tis2.IndexServerID
WHERE tsva.SearchID = @SearchID
--AND tsva.StatusID <> 13 ---Un-comment this line to show only those Archives that have not Finished
ORDER BY ts2.Name, tv.KVSVaultName
DECLARE @tsvaMinStartDate datetime
DECLARE @tsvaMaxEndDate datetime
SET @tsvaMinStartDate = (SELECT MIN(StartDate) FROM tblSearchVaultsArchived WHERE SearchID = @SearchID)
SET @tsvaMaxEndDate = (SELECT MAX(EndDate) FROM tblSearchVaultsArchived WHERE SearchID = @SearchID)
SELECT DISTINCT @tsvaMinStartDate AS 'Index Search StartDate'
     , @tsvaMaxEndDate AS 'Index Search EndDate'
     , CAST(CONVERT(nvarchar(10), DATEDIFF(DD, @tsvaMinStartDate, @tsvaMaxEndDate)) AS nvarchar(10)) + ' days, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvaMinStartDate, @tsvaMaxEndDate)))%86400000/3600000 AS nvarchar(10)) + ' hrs, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvaMinStartDate, @tsvaMaxEndDate)))%86400000%3600000/60000 AS nvarchar(10)) + ' mins, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvaMinStartDate, @tsvaMaxEndDate)))%86400000%3600000%60000/1000 AS nvarchar(10)) + '.' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvaMinStartDate, @tsvaMaxEndDate)))%86400000%3600000%60000%1000 AS nvarchar(10)) + ' secs' AS 'Index Volume Search Time'
PRINT ''
PRINT 'End output from tblSearchVaultsArchived for SearchID ' + @SearchID + ' in Review'
PRINT ''
END
ELSE BEGIN
PRINT ''
PRINT 'Begin output from tblSearchVault for SearchID ' + @SearchID + ' in progress'
PRINT ''
SELECT tis.Name AS 'Search Name'
     , ts3.Name AS 'Search State'
     , tv.KVSVaultName AS 'Archive Name'
     , tsv.StatusID AS 'Archive Search StatusID'
     , ts4.Name AS 'Archive Search Status'
     , tv.KVSVaultEntryID
     , ISNULL(tsv.IndexVolumeSetID, -1) AS 'Accelerator Index Volume ID'
     , ISNULL(tivs.EVIndexVolumeSetID,-1) AS 'EV Index Volume ID'
     , ISNULL(tivs.FirstItemSeqNum, -1) AS 'First Item Sequence Number'
     , tsv.Info AS 'Information'
     , tsv.NumHits AS 'Number of Hits'
     , tsv.StartDate AS 'Index Volume Search StartDate', tsv.EndDate AS 'Index Volume Search EndDate'
     , CAST(CONVERT(nvarchar(10), DATEDIFF(DD, tsv.StartDate, tsv.EndDate)) AS nvarchar(10)) + ' days, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsv.StartDate, tsv.EndDate)))%86400000/3600000 AS nvarchar(10)) + ' hrs, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsv.StartDate, tsv.EndDate)))%86400000%3600000/60000 AS nvarchar(10)) + ' mins, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsv.StartDate, tsv.EndDate)))%86400000%3600000%60000/1000 AS nvarchar(10)) + '.' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsv.StartDate, tsv.EndDate)))%86400000%3600000%60000%1000 AS nvarchar(10)) + ' secs' AS 'Index Volume Search Time'
     , tv.VaultServerID, tvs1.VaultServer 'Vault Server Name'
     , tv.VaultStoreID, tvs2.Name 'Vault Store Name'
     , tv.IndexServerID, tis2.IndexServiceEntryID
FROM tblSearchVault tsv
JOIN tblIntSearches tis ON tsv.SearchID = tis.SearchID
JOIN tblStatus ts3 ON tis.StatusID = ts3.StatusID
JOIN tblStatus ts4 ON tsv.StatusID = ts4.StatusID
FULL OUTER JOIN tblVaults tv ON tsv.VaultID = tv.VaultID
JOIN tblVaultServers tvs1 ON tv.VaultServerID = tvs1.VaultServerID
JOIN tblVaultStore tvs2 ON tv.VaultStoreID = tvs2.VaultStoreID
FULL OUTER JOIN tblIndexVolumeSet tivs ON tsv.IndexVolumeSetID = tivs.IndexVolumeSetID
FULL OUTER JOIN tblIndexService tis2 ON tv.IndexServerID = tis2.IndexServerID
WHERE tsv.SearchID = @SearchID
--AND tsv.StatusID <> 13 ---Un-comment this line to show only those Archives that have not Finished
ORDER BY ts4.Name, tv.KVSVaultName
DECLARE @tsvMinStartDate datetime
DECLARE @tsvMaxEndDate datetime
SET @tsvMinStartDate = (SELECT MIN(StartDate) FROM tblSearchVault WHERE SearchID = @SearchID)
SET @tsvMaxEndDate = (SELECT MAX(EndDate) FROM tblSearchVault WHERE SearchID = @SearchID)
SELECT DISTINCT @tsvMinStartDate AS 'Index Search StartDate'
     , @tsvMaxEndDate AS 'Index Search EndDate'
     , CAST(CONVERT(nvarchar(10), DATEDIFF(DD, @tsvMinStartDate, @tsvMaxEndDate)) AS nvarchar(10)) + ' days, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvMinStartDate, @tsvMaxEndDate)))%86400000/3600000 AS nvarchar(10)) + ' hrs, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvMinStartDate, @tsvMaxEndDate)))%86400000%3600000/60000 AS nvarchar(10)) + ' mins, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvMinStartDate, @tsvMaxEndDate)))%86400000%3600000%60000/1000 AS nvarchar(10)) + '.' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvMinStartDate, @tsvMaxEndDate)))%86400000%3600000%60000%1000 AS nvarchar(10)) + ' secs' AS 'Index Volume Search Time'
FROM tblSearchVault tsv
JOIN tblIntSearches tis ON tsv.SearchID = tis.SearchID
PRINT ''
PRINT 'End output from tblSearchVault for SearchID ' + @SearchID + ' in progress'
PRINT ''
END

Hope this helps.

Kind Regards,

Mohamed Nemazie