Slow Query
-
Hi,
This query is taking a long time to execute,is there any way I can improve the performance
SELECT TOP 100 PERCENT dbo.tbl_execParam.cycleName, dbo.tbl_execParam.framework,
'tbl_seedconfig_' + dbo.tbl_execParam.cycleName + '_' + dbo.tbl_execParam.framework AS SeedTableName,
'tbl_testExecution_' + dbo.tbl_execParam.cycleName + '_' + dbo.tbl_execParam.framework AS TestExecutionTableName,
dbo.tbl_tool.className,
dbo.tbl_execParam.taskName,
dbo.tbl_failedSeeds.targetName,
dbo.tbl_failedSeeds.status AS FailureStatus,
sd.dispositionStatus, dbo.tbl_execParam.toolName,
sd.seedName, CONVERT(varchar, DATEPART(YYYY,
dbo.tbl_failedSeeds.testendtime))
+ ' WW' + RIGHT('00' + CONVERT(varchar, DATEPART(WW,
dbo.tbl_failedSeeds.testendtime)), 2) AS FailureWW,
CONVERT(varchar, DATEPART(YYYY, sd.lastModifiedTime))
+ ' WW' + RIGHT('00' + CONVERT(varchar, DATEPART(WW,
sd.lastModifiedTime)), 2) AS lastModifiedWW,
sd.dispositionCategory, sd.dispositionSubcategory,
dbo.tbl_cycle.deleted
FROM dbo.tbl_seedDisposition sd INNER JOIN
dbo.tbl_failedSeeds ON sd.seedName = dbo.tbl_failedSeeds.seedName INNER JOIN
dbo.tbl_execParam ON dbo.tbl_failedSeeds.execParamID = dbo.tbl_execParam.execParamID INNER JOIN
dbo.tbl_tool ON dbo.tbl_execParam.toolName = dbo.tbl_tool.toolName AND dbo.tbl_execParam.framework = dbo.tbl_tool.framework INNER JOIN
dbo.tbl_cycle ON dbo.tbl_execParam.cycleName = dbo.tbl_cycle.cycleName
WHERE (dbo.tbl_cycle.deleted = 0) AND (sd.lastModifiedTime =
(SELECT TOP 1 lastmodifiedtime
FROM dbo.tbl_seedDisposition sd1
WHERE sd1.seedname = sd.seedname
ORDER BY lastModifiedTime DESC)) AND (dbo.tbl_execParam.cycleName LIKE '%sal77%')
ORDER BY dbo.tbl_execParam.cycleName, dbo.tbl_tool.className, dbo.tbl_execParam.framework, dbo.tbl_execParam.taskName, dbo.tbl_failedSeeds.status,
sd.dispositionStatus, dbo.tbl_execParam.toolName, sd.seedName, CONVERT(varchar, DATEPART(YYYY, dbo.tbl_failedSeeds.testendtime))
+ ' WW' + RIGHT('00' + CONVERT(varchar, DATEPART(WW, dbo.tbl_failedSeeds.testendtime)), 2), CONVERT(varchar, DATEPART(YYYY,
sd.lastModifiedTime)) + ' WW' + RIGHT('00' + CONVERT(varchar, DATEPART(WW, sd.lastModifiedTime)), 2)
|--Sort(ORDER BY
[tbl_execParam].[cycleName] ASC, [tbl_tool].[className] ASC, [tbl_execParam].[framework] ASC, [tbl_execParam].[taskName] ASC, [tbl_failedSeeds].[status] ASC, [sd].[dispositionStatus] ASC, [tbl_execParam].[toolName] ASC, [sd].[seedName]|--Compute Scalar(DEFINE
[Expr1011]='tbl_seedconfig_'+[tbl_execParam].[cycleName]+'_'+[tbl_execParam].[framework], [Expr1012]='tbl_testExecution_'+[tbl_execParam].[cycleName]+'_'+[tbl_execParam].[framework], [Expr1013]=Convert(datepart(year, [tbl_f|--Hash Match(Inner Join, HASH
[sd].[seedName])=([tbl_failedSeeds].[seedName]), RESIDUAL[sd].[seedName]=[tbl_failedSeeds].[seedName]))|--Filter(WHERE
[sd].[lastModifiedTime]=[sd1].[lastModifiedTime]))| |--Nested Loops(Inner Join, OUTER REFERENCES
[sd].[seedName]))| |--Clustered Index Scan(OBJECT
[Pandu].[dbo].[tbl_seedDisposition].[PK_tbl_seedDispositionTestCopy] AS [sd]), ORDERED FORWARD)| |--Hash Match(Cache, HASH
[sd].[seedName]), RESIDUAL[sd].[seedName]=[sd].[seedName]))| |--Compute Scalar(DEFINE
[sd1].[lastModifiedTime]=[sd1].[lastModifiedTime]))| |--Top(1)
| |--Clustered Index Seek(OBJECT
[Pandu].[dbo].[tbl_seedDisposition].[PK_tbl_seedDispositionTestCopy] AS [sd1]), SEEK[sd1].[seedName]=[sd].[seedName]) ORDERED BACKWARD)|--Hash Match(Inner Join, HASH
[tbl_execParam].[execParamID])=([tbl_failedSeeds].[execParamID]), RESIDUAL[tbl_failedSeeds].[execParamID]=[tbl_execParam].[execParamID]))|--Merge Join(Inner Join, MERGE
[tbl_tool].[toolName], [tbl_tool].[framework])=([tbl_execParam].[toolName], [tbl_execParam].[framework]), RESIDUAL[tbl_execParam].[toolName]=[tbl_tool].[toolName] AND [tbl_execParam].[framework]=[tbl| |--Clustered Index Scan(OBJECT
[Pandu].[dbo].[tbl_tool].[PK__tbl_tool__76619304]), ORDERED FORWARD)| |--Sort(ORDER BY
[tbl_execParam].[toolName] ASC, [tbl_execParam].[framework] ASC))| |--Nested Loops(Inner Join, OUTER REFERENCES
[tbl_cycle].[cycleName]) WITH PREFETCH)| |--Clustered Index Scan(OBJECT
[Pandu].[dbo].[tbl_cycle].[PK__tbl_cycle__59063A47]), WHEREConvert([tbl_cycle].[deleted])=0))| |--Index Seek(OBJECT
[Pandu].[dbo].[tbl_execParam].[unique_execParam]), SEEK[tbl_execParam].[cycleName]=[tbl_cycle].[cycleName]), WHERElike([tbl_execParam].[cycleName], '%sal77%', NULL)) ORDERED FORWARD)|--Clustered Index Scan(OBJECT
[Pandu].[dbo].[tbl_failedSeeds].[PK_tbl_failedSeeds]))Thanks,
ssm
thanx...,SSM
-
i am not sure this will help. your post is tooo long.
How if you change:
SELECT TOP 1 lastmodifiedtime
FROM dbo.tbl_seedDisposition sd1
WHERE sd1.seedname = sd.seedname
ORDER BY lastModifiedTime DESC
to
SELECT max(lastmodifiedtime)
FROM dbo.tbl_seedDisposition sd1
WHERE sd1.seedname = sd.seedname
how does the execution plan looks like?
Leo
-
Hi
Not sure whether it's a typo or intentional but why are you selecting the top 100 percent - surely that would just equate to everything (and will using a SELECT TOP x% add overhead to your execution)?
Martin
-
Order clause can be:
ORDER BY
dbo.tbl_execParam.cycleName,
dbo.tbl_tool.className,
dbo.tbl_execParam.framework,
dbo.tbl_execParam.taskName,
dbo.tbl_failedSeeds.status,
sd.dispositionStatus,
dbo.tbl_execParam.toolName,
sd.seedName,
dbo.tbl_failedSeeds.testendtime,
sd.lastModifiedTime
You can alose try this
FROM
(SELECT MAX(lastmodifiedtime) as lastmodifiedtime,seedName
FROM dbo.tbl_seedDisposition
GROUP BY lastModifiedTime)sd1 inner join dbo.tbl_seedDisposition sd
on sd.seedName=sd1.seedName and sd.lastmodifiedtime=sd1.lastmodifiedtime
INNER JOIN dbo.tbl_failedSeeds
ON sd.seedName = dbo.tbl_failedSeeds.seedName
INNER JOIN dbo.tbl_execParam
ON dbo.tbl_failedSeeds.execParamID = dbo.tbl_execParam.execParamID
INNER JOIN dbo.tbl_tool
ON dbo.tbl_execParam.toolName = dbo.tbl_tool.toolName AND dbo.tbl_execParam.framework = dbo.tbl_tool.framework
INNER JOIN dbo.tbl_cycle
ON dbo.tbl_execParam.cycleName = dbo.tbl_cycle.cycleName
WHERE
(dbo.tbl_cycle.deleted = 0) AND (dbo.tbl_execParam.cycleName LIKE '%sal77%')
Kindest Regards,Vasc
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply