Slow Query

Question

Post reply

Slow Query

SSM-140546

Old Hand

Points: 365
More actions
September 22, 2005 at 4:47 pm

#110019

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Leo Leong SSCommitted Points: 1615 More actions · Answer 1

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

Martin Pritchard SSC Veteran Points: 225 More actions · Answer 2

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

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 3

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