Home Forums SQL Server 2019 SQL Server 2019 - Development Query tuning - Help needed RE: Query tuning - Help needed

  • December 15, 2016 at 6:20 am

    #1918154

    ffarouqi (12/14/2016)

    Hi all,

    I've been looking into this query and trying to see if there is any better way to write or enhance performance on these queries. Client states that these queries are run frequently by him and he has been facing issues since the application upgrade for the progression database. I need some help in understanding what I could possibly do to tune the queries or make it go faster. Attached query is taking somewhere around 20 secs which I think is bad.

    Please do let me know if I need to provide more information.

    Regards,

    Fievel

    Regarding [dbo].[TaskView]; the result of the first query is a subset of the second query. At a guess, it's incorrect and should look like this:

    SELECT --DISTINCT

    --TOP 100 PERCENT

    tq.TaskName, tq.TypeID, tq.QueueID, tq.TaskID, tq.BinderID, tq.AssignedID, tq.AssignedType, tq.TaskAssignmentID, tq.ProcessHistoryID,

    tq.PrecedingWorkItemID, tq.PrecedingTaskID, tq.PrecedingInstanceID, tq.PrecedingStateID, tq.PrecedingSubscriberType, tq.PrecedingSubscriberID,

    tq.InstanceID, tq.Available, tq.LockedBy, tq.BinderDefID, tq.DocumentDefID, tq.ProcessID, tq.ProcessName, tq.BinderName, tq.KeyName, tq.KeyValue,

    tq.InsertTime, tq.StartTimestamp, tq.stopTimeStamp, tq.Priority, tq.Deadline, ts.SubscriberID, ts.SubscriberType, ts.AssignedDuration,

    ISNULL(sp.PriorityOrder, 999) AS priorityorder, tq.Suspended, tq.SuspendMode, tq.SuspendStartTime, tq.SuspendEndTime

    FROM dbo.taskViewNoSubs AS tq

    left outer JOIN dbo.SubscriptionView AS ts

    ON ts.queueid = tq.QueueID

    LEFT OUTER JOIN dbo.SubscriberPriority AS sp

    ON tq.TaskID = sp.TaskID

    AND sp.SubscriberID = ts.SubscriberID

    AND sp.SubscriberType = ts.SubscriberType

    WHERE ts.queueid IS NULL OR (ts.queueid = tq.QueueID AND NOT EXISTS (SELECT 1 FROM dbo.SubscriptionView tsi WHERE tsi.Queueid = tq.QueueID))

    Confirm whether or not this is the case. If it is, then you've halved the table access.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden