SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query tuning - Help needed


Query tuning - Help needed

Author
Message
Feivel
Feivel
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5548 Visits: 1921
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
Attachments
SQL_QueryTuned.txt (46 views, 263 bytes)
SQL_Views.txt (47 views, 2.00 KB)
SQL_ActualExecPlan.sqlplan (32 views, 600.00 KB)
pietlinden
pietlinden
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49685 Visits: 16180
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).
Feivel
Feivel
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5548 Visits: 1921
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
pietlinden
pietlinden
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49685 Visits: 16180
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.
Feivel
Feivel
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5548 Visits: 1921
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
Attachments
Table_Indexes_DocumentBinder.txt (38 views, 2.00 KB)
DesNorton
DesNorton
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16753 Visits: 6990
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.
Make sure that you include code in the appropriate IFCode tags.
Attachments
IndexDetails.JPG (194 views, 36.00 KB)
Shekhu
Shekhu
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 130
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.
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)

Group: General Forum Members
Points: 157056 Visits: 21380
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
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)

Group: General Forum Members
Points: 157056 Visits: 21380
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
Exploring Recursive CTEs by Example Dwain Camps
Feivel
Feivel
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5548 Visits: 1921
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search