3

I'm a part-time accidental DBA and I've run into problem. Unfortunately, do to some restrictions on how the software is running, I can't do the obvious and turn the sub-query into a join. Is there a way to index this thing to have it perform better?

I have select statement that on its own, takes about 2 seconds to return 28000 records. When I add a subquery to the SELECT, it explodes into over a minute and change.

The subquery, separately, also takes about 2 seconds to return data.

ADDENDUM: The XML for the execution plan.

The OpenIssuesTBL.AssignedBy and OpenIssuesTBL.Resp columns are not stored as wild cards. We pass variables in to them in code, the wildcards are just so I can grab all the records in a similar way as the 'show all records' button we have.

To explain the design a little bit, our code can add additional columns on the fly by using these sub-queries in the select statement. We pull them from another table and just insert them into the query we use to populate a search screen. Most of the time, this works without a problem. For some reason, this query is just dog slow.

SELECT OpenIssuesTBL.IssueID
   , OpenIssuesTBL.Category
   , qryCOCompanyDBQualUnion.CompanyName
   , CASE
        WHEN bitConfidential = 0
        THEN OpenIssuesTBL.Issue
        ELSE 'Confidential'
    END AS Issue
   , OpenIssuesTBL.OpenDate
   , OpenIssuesTBL.Priority
   , OpenIssuesTBL.Duration
   , OpenIssuesTBL.DueDate
   , OpenIssuesTBL.Resp
   , CASE
        WHEN bitConfidential = 0
        THEN OpenIssuesTBL.Progress
        ELSE 'Confidential'
    END                      AS Progress
   , OpenIssuesTBL.ClosedDate AS StatusDate
   , OpenIssuesTBL.AssignedBy
   , DBQualityUtilityTBL.CompanyName AS Site
   , tblIssueTypeList.strIssueType
   , OpenIssuesTBL.strIssueStatus
   , OpenIssuesTBL.strProductNum
   , OpenIssuesTBL.ynSystemGenerated
   , OpenIssuesTBL.strSuggestion
   , OpenIssuesTBL.strSource
   , OpenIssuesTBL.memIssueDialog
   , tblIssueTimeLookup.strTimeDesc
   , OpenIssuesTBL.strComponent
   , OpenIssuesTBL.intPercentComplete
   , tblUDFIssues.strUDF1
   , tblUDFIssues.strUDF2
   , tblUDFIssues.strUDF3
   , tblUDFIssues.strUDF4
   , tblUDFIssues.strUDF5
   , tblUDFIssues.strUDF6
   , tblUDFIssues.strUDF7
   , tblUDFIssues.strUDF8
   , tblUDFIssues.strUDF9
   , tblUDFIssues.strUDF10
   , tblUDFIssues.strUDF11
   , tblUDFIssues.strUDF12
   , CASE ISNULL(qryIssuesRYG.RYGColor, 32768)
        WHEN 255
        THEN 'Red'
        WHEN 32768
        THEN 'Green'
        WHEN 65535
        THEN 'Yellow'
    END AS RYG
   , qryWMIssueDurationHrs.DurationHours
   , CASE
        WHEN OpenIssuesTBL.bitConfidential = 0
        THEN ''
        ELSE 'X'
    END AS Confidential
   , CASE
        WHEN OpenIssuesTBL.bitRequiresVerify = 0
        THEN ''
        ELSE 'X'
    END AS ReqsVer
   , OpenIssuesTBL.strVerifiedBy
   , OpenIssuesTBL.dteVerified
   , CASE
        WHEN OpenIssuesTBL.bitRequiresVerify <> 0
        THEN 'Verified BY'
        ELSE ''
    END AS lblVerifiedBy
   , CASE
        WHEN OpenIssuesTBL.bitRequiresVerify <> 0
        THEN [strVerifiedBy]
        ELSE ''
    END AS VerifiedBy
   , CASE
        WHEN OpenIssuesTBL.bitRequiresVerify <> 0
        THEN 'Verified Date'
        ELSE ''
    END AS lblVerifiedDate
   , CASE
        WHEN OpenIssuesTBL.bitRequiresVerify <> 0
        THEN [dteVerified]
        ELSE NULL
    END AS VerifiedDate
   , qryWMWorkflowIssueNextStep.strStep
   , qryWMWorkflowIssueNextStep.strResp
   , qryWMWorkflowIssueNextStep.dteDue
   , tblWorkflowSetup.strWorkflowName + ' (Rev. ' + tblWorkflowSetupRevisions.strRevisionNo + ')' AS WorkflowName
   , OpenIssuesTBL.memWorkflowNotes
   , (
    SELECT TOP 1
        qryIssueRespSupervisor._Supvr
    FROM qryIssueRespSupervisor
    WHERE qryIssueRespSupervisor.lngFKIssueID = OpenIssuesTBL.IssueID
    )
FROM qryWMIssueDurationHrs
    RIGHT OUTER JOIN qryWMWorkflowIssueNextStep
    RIGHT OUTER JOIN OpenIssuesTBL            ON qryWMWorkflowIssueNextStep.IssueID    = OpenIssuesTBL.IssueID
    LEFT OUTER JOIN tblWorkflowSetupRevisions ON OpenIssuesTBL.lngfkWorkflowRevisionID = tblWorkflowSetupRevisions.lngWorkflowSetupRevisionID
    LEFT OUTER JOIN tblWorkflowSetup          ON OpenIssuesTBL.lngfkWorkflowSetupID    = tblWorkflowSetup.lngWorkflowSetupID ON qryWMIssueDurationHrs.IssueID = OpenIssuesTBL.IssueID
    LEFT OUTER JOIN qryIssuesRYG              ON OpenIssuesTBL.IssueID                 = qryIssuesRYG.IssueID
    LEFT OUTER JOIN tblUDFIssues              ON OpenIssuesTBL.IssueID                 = tblUDFIssues.lngIssueID
    LEFT OUTER JOIN qryCOCompanyDBQualUnion   ON OpenIssuesTBL.CompanyID               = qryCOCompanyDBQualUnion.CompanyID
    LEFT OUTER JOIN tblIssueTypeList          ON OpenIssuesTBL.IssueTypeID             = tblIssueTypeList.lngIssueTypeID
    LEFT OUTER JOIN DBQualityUtilityTBL       ON OpenIssuesTBL.LocationID              = DBQualityUtilityTBL.CompanyID
    LEFT OUTER JOIN tblIssueTimeLookup        ON OpenIssuesTBL.bteDurUnits             = tblIssueTimeLookup.lngTimeID
WHERE (
        bitConfidential          = 0
    OR   OpenIssuesTBL.AssignedBy = '%'
    OR   OpenIssuesTBL.Resp       = '%'
    )

The subquery is a view--it looks like this.

CREATE VIEW qryIssueRespSupervisor AS
    (
    SELECT EmployeeTBL_1.FirstName + ' ' + EmployeeTBL_1.LastName AS _Supvr
       , i1.IssueID                                               AS lngFKIssueID
    FROM dbo.EmployeeTBL
        INNER JOIN dbo.PosDescriptTBL                     ON dbo.EmployeeTBL.PositionNo          = dbo.PosDescriptTBL.PositionNo
        INNER JOIN dbo.PosDescriptTBL AS PosDescriptTBL_1 ON dbo.PosDescriptTBL.lngfkReportsToID = PosDescriptTBL_1.PositionNo
        INNER JOIN dbo.EmployeeTBL    AS EmployeeTBL_1    ON PosDescriptTBL_1.PositionNo         = EmployeeTBL_1.PositionNo
        INNER JOIN dbo.StatusLookupTBL                    ON EmployeeTBL_1.Active                = dbo.StatusLookupTBL.StatusType
        INNER JOIN dbo.qryTREmployeeFullName              ON dbo.EmployeeTBL.EmployeeID          = dbo.qryTREmployeeFullName.EmployeeID
        INNER JOIN dbo.OpenIssuesTBL AS i1                ON dbo.qryTREmployeeFullName.EName     = i1.Resp
    WHERE (
            dbo.StatusLookupTBL.intHide = 0
        )
    )
3
  • 3
    What version and edition of sql server are you using ? Also, can you post the query execution plan ? Do you actually have OpenIssuesTBL.AssignedBy and OpenIssuesTBL.Resp with values stored as % ?
    – Kin Shah
    Sep 28, 2015 at 21:18
  • 1
    Execution plan would help as Kin said. And just for the sake of trying something you could pull that out of the subquery and try using an Apply operator. No guarantee that it will even change the query plan but it is possible and it will certainly give you the same result.
    – mskinner
    Sep 28, 2015 at 23:47
  • Does EmployeeTBL have indexes appropriate to the query? Can you provide the DDL for that table? The execution plan indicates the query planner estimated 300,000 rows would be returned from that table, however over 59 million rows were returned.
    – Hannah Vernon
    Sep 29, 2015 at 15:43

2 Answers 2

2

Your Select statement with TOP 1 in a sub query is a killer. When you are running your query, this statement will run for each row. depending on number of rows your main statement is returning, you can imagine the time it is going to take. In my opinion, what you can try is using your view's Select statement with GROUP BY as common table expression (or change the view to use group by, if possible) so it always returns a top record a particular Id. (I can not see your table design so I am not sure which aggregate you can use to GROUP BY your statement but May be group it with MAX(IssueId)) once you have that, all you need is simple INNER JOIN or LEFT OUTER JOIN in your main query.

Hope this is making sense.

1
  • It makes sense. Unfortunately the software design precludes being able to take my sub-query and making it part of the main query. I'm looking to hack around that.
    – Konraden
    Sep 29, 2015 at 15:14
0

Perhaps an index such as the following might help return rows from the subquery:

CREATE INDEX IX_EmployeeTBL_PositionNo
ON dbo.EmployeeTBL(PositionNo)
INCLUDE (Active, EmployeeID, FirstName, LastName);

Since you can't change the code itself, you're limited to ensuring indexes are appropriate, statistics are up-to-date, and your hardware is appropriate.

Perhaps giving the machine more ram might help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.