May 13, 2014 at 11:21 am
Hi Experts,
I was Tuning a query which is running for a long time of more than 30 min.When i look at the query i could see its not using the proper indexes.
Attached the query in problem, Index Script and execution plan,please help me to change Index scan to Index seek.
DML.ExtPAIntelSubscriptionActivationEvents is a view and the script is attached
Please help me with your suggestions.
May 13, 2014 at 12:35 pm
Gangadhara MS (5/13/2014)
Hi Experts,I was Tuning a query which is running for a long time of more than 30 min.When i look at the query i could see its not using the proper indexes.
Attached the query in problem, Index Script and execution plan,please help me to change Index scan to Index seek.
DML.ExtPAIntelSubscriptionActivationEvents is a view and the script is attached
Please help me with your suggestions.
Nearly impossible to help much without the actual table definitions too. All we have is a view with a lot of nonSARGable predicates.
You might try looking at changing your where clause in that view to something like this.
WHERE PAIntel.ReferralTrackingCode > ''
AND PAIntel.ProductEdition > ''
AND PAIntel.PKPNDescription > ''
AND PAIntel.ActivationDateTime > '1900-01-01'
AND PAIntel.ISOCountryCode > ''
AND CalcReferralTrackingCode <> '0'
There is no need to check for both not and <> ''. Just change it to > ''. This will remove any null AND any rows where the value = ''. This moves all of your columns outside of functions so that indexes on those columns can be used.
Do the other objects in this view represent other views? If so, you may want to reconsider what you have going on here because nested views can create performance nightmares that are very difficult to unravel.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2014 at 1:08 pm
Hi Lange,
It Really helps me, I just changed the where clause as mentioned in your suggestion the query reduced to 1 minute from morethan 20 minute, you are awesome .!!
How do we learn this was the problem in query execution, any reading suggestion helps me to retain my job.
Great help thanks
May 13, 2014 at 1:22 pm
Gangadhara MS (5/13/2014)
Hi Lange,It Really helps me, I just changed the where clause as mentioned in your suggestion the query reduced to 1 minute from morethan 20 minute, you are awesome .!!
How do we learn this was the problem in query execution, any reading suggestion helps me to retain my job.
Great help thanks
Glad that worked for you. I didn't gain anything from the execution plan. I looked at your query and you have columns inside a function in the where clause. This is a big time no no because it renders your indexes useless. You might look up SARGable in your favorite search engine. There are thousands of articles on the topic.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2014 at 2:19 pm
Thanks for your suggestions.
One more help on the same line but here i don't have any SARGble function here its direct select and insert. As i am doing a join i am getting required records to staging before i do actually join.
But the table contains 125 Millions of record.
Query,Index with table definition attached
May 13, 2014 at 2:43 pm
Gangadhara MS (5/13/2014)
Thanks for your suggestions.One more help on the same line but here i don't have any SARGble function here its direct select and insert. As i am doing a join i am getting required records to staging before i do actually join.
But the table contains 125 Millions of record.
Query,Index with table definition attached
Again don't need to look much deeper than your query.
SELECT PartnerEntitlementID
FROM DML.ExceptionsTrackingTable
WHERE ExceptionType <> N'No OLS Machine Data'
AND PartnerEntitlementID IS NOT NULL
AND ActivationModel = 'Subscription'
You have a nonSARGable predicate in there. Not Equal is going to kill your performance here.
Think of this like a phone book. If you were to find all LastNames where <> 'No OLS Machine Data' you would have to look at every single row and determine if the value is different (scan). If however you found all names > 'No OLS Machine Data' AND all names < 'No OLS Machine Data' you can do your lookup a lot differently. Anything after that can be grouped right? Just like pages in the phone book.
Try this.
SELECT PartnerEntitlementID
FROM DML.ExceptionsTrackingTable
WHERE (ExceptionType > N'No OLS Machine Data' OR ExceptionType < N'No OLS Machine Data' )
AND PartnerEntitlementID IS NOT NULL
AND ActivationModel = 'Subscription'
You might find that you have even better luck with something like this.
SELECT PartnerEntitlementID
FROM DML.ExceptionsTrackingTable
WHERE ExceptionType > N'No OLS Machine Data'
AND PartnerEntitlementID IS NOT NULL
AND ActivationModel = 'Subscription'
UNION ALL
SELECT PartnerEntitlementID
FROM DML.ExceptionsTrackingTable
WHERE ExceptionType < N'No OLS Machine Data'
AND PartnerEntitlementID IS NOT NULL
AND ActivationModel = 'Subscription'
I don't have time to cobble together 125 million rows of sample data to test this out but see what the results are like with either of these.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2014 at 5:45 pm
It really didn't helped me out, seems something is wrong here does it due to 125 rows ??
May 14, 2014 at 7:43 am
Gangadhara MS (5/13/2014)
It really didn't helped me out, seems something is wrong here does it due to 125 rows ??
Well the query you posted is nothing like the query in the execution plan you posted. In your execution plan you first insert into ExceptMissingOLSMachine from ExceptionsTrackingTable. Then you insert into ExceptMissingOLSMachine from ExceptMissingOLSMachine. This is creating duplicate data for the entire table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2014 at 9:00 am
No But in My Query I am truncating it before i insert into ExceptMissingOLSMachine from ExceptionsTrackingTable by disabling the index and rebuild it later.
I have forgotten to give the fully qualified name,let me do this and test it. Thanks for your suggestions.
TRUNCATE TABLE DML.ExceptMissingOLSMachine
ALTER INDEX NCIX_ExceptMissingOLSMachine_PartnerEntitlementID ON ExceptMissingOLSMachine disable
INSERT INTO ExceptMissingOLSMachine
SELECT PartnerEntitlementID
FROM DML.ExceptionsTrackingTable
WHERE (ExceptionType > N'No OLS Machine Data' OR ExceptionType < N'No OLS Machine Data' )
AND PartnerEntitlementID IS NOT NULL
AND ActivationModel = 'Subscription'
ALTER INDEX NCIX_ExceptMissingOLSMachine_PartnerEntitlementID ON ExceptMissingOLSMachine REBUILD
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply