Post reply

Query tuning - Help needed

  • December 14, 2016 at 6:04 pm

    #338277

    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

  • December 14, 2016 at 6:26 pm

    #1918053

    Is there any way you can split this part of the data into a separate column?

    convert(nvarchar,keyvalue) like '%11857548%'

    Ideally, you'd want the 11857548 value in a column by itself so you could index it. The problem is that you're forcing a table scan by filtering like that. It's a non-SARGable query... you can't optimize it as is because you're asking something like "Show me all the people in the phone book with the first name of 'John'". Because the phone book is indexed by last name and then first name, you have to read the whole thing to find all the "Johns".

    If you split that part to a separate column and indexed that, you could immediately go to the 118... section and search there without reading the whole table (You'd read the index instead).

  • December 14, 2016 at 6:37 pm

    #1918054

    pietlinden (12/14/2016)

    Is there any way you can split this part of the data into a separate column?

    convert(nvarchar,keyvalue) like '%11857548%'

    Ideally, you'd want the 11857548 value in a column by itself so you could index it. The problem is that you're forcing a table scan by filtering like that. It's a non-SARGable query... you can't optimize it as is because you're asking something like "Show me all the people in the phone book with the first name of 'John'". Because the phone book is indexed by last name and then first name, you have to read the whole thing to find all the "Johns".

    If you split that part to a separate column and indexed that, you could immediately go to the 118... section and search there without reading the whole table (You'd read the index instead).

    I appreciate your response. However, if you could be more specific on what do you mean by splitting the data into a separate column...keyvalue is a separate column by itself, but it seems the data type for that column is chosen to be sql_variant and I am not sure why, since it is a vendor specific database I don't have control over the data structures. I know it is non-SARGable and that is the reason why I posted this query in this forum so that someone would provide me a SARGable way of writing it or provide me tips on what could be wrong and how else I could bring it below the current execution time.

    Thanks

  • December 14, 2016 at 6:42 pm

    #1918055

    Oh, you neglected to mention that it was a vendor database. Key piece of information.

    having said that, the only way I can think of helping this situation out is to create a table in another database (that you control) and then indexing the columns... and then you'd have to modify the query... if you can't do that, I have no idea how to fix it.

  • December 14, 2016 at 6:54 pm

    #1918056

    pietlinden (12/14/2016)

    Oh, you neglected to mention that it was a vendor database. Key piece of information.

    having said that, the only way I can think of helping this situation out is to create a table in another database (that you control) and then indexing the columns... and then you'd have to modify the query... if you can't do that, I have no idea how to fix it.

    Thanks! once again. It is definitely part of a table that has indexes. However, I am not sure whether these indexes are really helping the cause. I've attached the script for that table and the indexes in question. Please review the same and let me know if possible what would be the best course of action for the same.

    Regards,

    Fievel

  • December 15, 2016 at 12:42 am

    #1918086

    For the table provided (DocumentBinder)

    • The clustered index in on a UNIQUEIDENTIFIER, which is going to cause major fragmentation
    • For IX_DocumentBinder_ID_BinderDefID and _dta_index_DocumentBinder_11_325576198__K1_K2_3_4, I would rather disable IX_DocumentBinder_ID_BinderDefID, and enable _dta_index_DocumentBinder_11_325576198__K1_K2_3_4
    • You can safely disable IX_BinderDefID, as it is covered by IX_DocumentBinder_BinderDefID_KeyValue

    For the query on TaskView

    • There is no reference to the tbale DocumentBinder
    • The view is based purely on other views - If you can, try to rewrite the query based on the base tables

    How to post data/code on a forum to get the best help.

  • December 15, 2016 at 2:36 am

    #1918096

    Your query is spending maximum time in Sorting the data. I checked your view and I see it uses "TOP 100 PERCENT " ... If you are getting 100 Percent data then you don't need to use top 100 Percent , even if you remove this it will fetch all records. At first place try to remove this TOP clause and see performance.

    Also remove the order by stoptimestamp clause unless you really need to order the result set in database. You can do the sorting at UI if required.

  • 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

  • December 15, 2016 at 6:23 am

    #1918155

    Shekhu (12/15/2016)

    Your query is spending maximum time in Sorting the data. I checked your view and I see it uses "TOP 100 PERCENT " ... If you are getting 100 Percent data then you don't need to use top 100 Percent , even if you remove this it will fetch all records. At first place try to remove this TOP clause and see performance.

    Also remove the order by stoptimestamp clause unless you really need to order the result set in database. You can do the sorting at UI if required.

    The cost of the sort at 86% is based upon an estimate of 1314550 rows, when in fact exactly 0 rows are sorted.

    “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

  • December 15, 2016 at 1:31 pm

    #1918280

    ChrisM@Work (12/15/2016)

    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.

    Thanks Chris. However, the output results from your query doesn't match the output from the query within the view. Is there a particular operator in the plan that could be a problem and what could be the easiest way to fix it.

  • January 12, 2017 at 1:48 pm

    #1922423

    ffarouqi - Thursday, December 15, 2016 1:31 PM

    Thanks Chris. However, the output results from your query doesn't match the output from the query within the view. Is there a particular operator in the plan that could be a problem and what could be the easiest way to fix it.

    Chris is pointing out that your TaskView definition references the same object twice (taskViewNoSubs) and may be returning duplicate data.

    The first query in your view definition is identical to the second query except for the join criteria.  If the second is only doing left joins, you will always return all taskViewNoSubs, then why do you need the first query?

    Since you are having performance issues, you may want to revisit your view definitions to make sure they aren't overly complicated.

    Wes
    (A solid design is always preferable to a creative workaround)

  • January 13, 2017 at 11:38 am

    #1922662

    Could you add a computed persistent field to this table, with a calculation that would be useful to index?  If so, index this computed persistent field and use it for your query.

Viewing 12 posts - 1 through 11 (of 11 total)

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