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

[djdowney]

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

We can get the searches - no issue - can only seem to pull creation and modifed dates - but looking for time stamp for duration of each search.

use [Database]
SELECT SearchID,

CaseID,

NumHits,

PrincipalName AS ModifiedBy,

tblIntSearches.Name AS SearchName,

tblIntSearches.ModifiedDate AS DateModified,

CreateDate,

tblStatus.[Name] AS SearchType,

CreationType.[Name] AS CreationType,

SampleResultSize,

NativeQuery,

NativeLegacyQuery,

XMLText

FROM tblIntSearches

LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[ModifiedByID] = tblPrincipal.[PrincipalID]

INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]

INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]

WHERE tblIntSearches.[StatusID] <> 858 AND PrincipalName IS NOT NULL

UNION ALL

SELECT TypeID AS SearchID,

tblAudit.CaseID,

NumHits,

PrincipalName AS ModifiedBy,

tblIntSearches.Name AS SearchName,

AuditDate AS DateModified,

CreateDate,

tblStatus.[Name] AS SearchType,

CreationType.[Name] AS CreationType,

SampleResultSize,

NativeQuery,

NativeLegacyQuery,

XMLText

Name

FROM tblIntSearches

INNER JOIN tblAudit ON tblAudit.TypeID = tblintSearches.SearchID

AND tblIntSearches.StatusID <> 858

AND tblAudit.AuditTypeID = 1052

LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[CreatedByID] = tblPrincipal.[PrincipalID]

INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]

INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]

ORDER BY CreateDate DESC

[MohamedNemazie]

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