Sql-server – Queries are fast separtely, but slow when joined via subquery

sql server

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
        )
    )

Best Answer

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.

Related Solutions

Sql-server – Slow deletion of records when a trigger is enabled

The row-versioning framework introduced in SQL Server 2005 is used to support a number of features, including the new transaction isolation levels READ_COMMITTED_SNAPSHOT and SNAPSHOT. Even when neither of these isolation levels are enabled, row-versioning is still used for AFTER triggers (to facilitate generation of the inserted and deleted pseudo-tables), MARS, and (in a separate version store) online indexing.

As documented, the engine may add a 14-byte postfix to each row of a table that is versioned for any of these purposes. This behaviour is relatively well-known, as is the addition of the 14-byte data to every row of an index that is rebuilt online with a row-versioning isolation level enabled. Even where the isolation levels are not enabled, one extra byte is added to non-clustered indexes only when rebuilt ONLINE.

Where an AFTER trigger is present, and versioning would otherwise add 14 bytes per row, an optimization exists within the engine to avoid this, but where a ROW_OVERFLOW or LOB allocation cannot occur. In practice, this means the maximum possible size of a row must be less than 8060 bytes. In calculating maximum possible row sizes, the engine assumes for example that a VARCHAR(460) column could contain 460 characters.

The behaviour is easiest to see with an AFTER UPDATE trigger, though the same principle applies to AFTER DELETE. The following script creates a table with a maximum in-row length of 8060 bytes. The data fits on a single page, with 13 bytes of free space on that page. A no-op trigger exists, so the page is split and versioning information added:

USE Sandpit;
GO
CREATE TABLE dbo.Example
(
    ID          integer NOT NULL IDENTITY(1,1),
    Value       integer NOT NULL,
    Padding1    char(42) NULL,
    Padding2    varchar(8000) NULL,

    CONSTRAINT PK_Example_ID
    PRIMARY KEY CLUSTERED (ID)
);
GO
WITH
    N1 AS (SELECT 1 AS n UNION ALL SELECT 1),
    N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R),
    N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R),
    N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R)
INSERT TOP (137) dbo.Example
    (Value)
SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM N4;
GO
ALTER INDEX PK_Example_ID 
ON dbo.Example 
REBUILD WITH (FILLFACTOR = 100);
GO
SELECT
    ddips.index_type_desc,
    ddips.alloc_unit_type_desc,
    ddips.index_level,
    ddips.page_count,
    ddips.record_count,
    ddips.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Example', N'U'), 1, 1, 'DETAILED') AS ddips
WHERE
    ddips.index_level = 0;
GO
CREATE TRIGGER ExampleTrigger
ON dbo.Example
AFTER DELETE, UPDATE
AS RETURN;
GO
UPDATE dbo.Example
SET Value = -Value
WHERE ID = 1;
GO
SELECT
    ddips.index_type_desc,
    ddips.alloc_unit_type_desc,
    ddips.index_level,
    ddips.page_count,
    ddips.record_count,
    ddips.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Example', N'U'), 1, 1, 'DETAILED') AS ddips
WHERE
    ddips.index_level = 0;
GO
DROP TABLE dbo.Example;

The script produces the output shown below. The single-page table is split into two pages, and the maximum physical row length has increased from 57 to 71 bytes (= +14 bytes for the row-versioning information).

Update example

DBCC PAGE shows that the single updated row has Record Attributes = NULL_BITMAP VERSIONING_INFO Record Size = 71, whereas all other rows in the table have Record Attributes = NULL_BITMAP; record Size = 57.

The same script, with the UPDATE replaced by a single row DELETE produces the output shown:

DELETE dbo.Example
WHERE ID = 1;

Delete example

There is one fewer row in total (of course!), but the maximum physical row size has not increased. Row versioning information is only added to rows needed for the trigger pseudo-tables, and that row was ultimately deleted. The page split remains, however. This page-splitting activity is responsible for the slow performance observed when the trigger was present. If the definition of the Padding2 column is changed from varchar(8000) to varchar(7999), the page no longer splits.

Also see this blog post by SQL Server MVP Dmitri Korotkevitch, which also discusses the impact on fragmentation.

Sql-server – Converting outer apply with Correlated subquery in SQL Server to SQLite

SQLite does not support lateral or correlated joins. You can work around that using a join condition. For example:

create table t1 (id int, name text);
create table t2 (id int, t1id int references t1(id), name text);
insert into t1 values (1, 'a'), (2, 'b'), (3, 'c');
insert into t2 values (1, 1, 'a1'), (2, 1, 'a2'), (3, 2, 'b1');

Now we'd like to look up at most one row from t2, sorted by name. Here's one approach that will work in SQLite:

select  *
from    t1
left join
        t2
on      t2.id =
        (
        select  id
        from    t2
        where   t1.id = t2.t1id
        order by
                name desc
        limit   1
        )

Example at SQL Fiddle.

Best Answer

Related Solutions

Sql-server – Slow deletion of records when a trigger is enabled

Sql-server – Converting outer apply with Correlated subquery in SQL Server to SQLite

Related Question