Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Optimization not enough Expand / Collapse
Author
Message
Posted Tuesday, April 22, 2014 12:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:38 PM
Points: 132, Visits: 816
Hi,

In my execution plan i can see some warning like:
1. "Type conversion in expression (CONVERT_IMPLICIT(varchar(40),[PAIntel].[ActivationDateTime],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(varchar(24),[Machine].[MachineKey],0)) may affect "CardinalityEstimate" in query plan choice "

2. I am not using any order by clause in my query but still its having an sort with warning as

Operator used tempdb to spill data during execution with spill level 2

3. I have a Key look up (clustred) with high cost how do we remove this.

I am doing join with 2 views (there is no indexed view)

and one table is having 84 million records

Any help much appreciated.


  Post Attachments 
LoadExcepMissOlsAndCp.sqlplan (7 views, 718.58 KB)
Post #1563985
Posted Tuesday, April 22, 2014 12:53 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
Query, table definitions, index definitions please.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1563995
Posted Tuesday, April 22, 2014 1:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:38 PM
Points: 132, Visits: 816
1 st tabel is having 48 Lacks recoprds

the 5 th table is having 84 million records


  Post Attachments 
tabel defination1.txt.txt (7 views, 3.96 KB)
table defination2.txt.txt (4 views, 899 bytes)
tabel defination3.txt.txt (3 views, 861 bytes)
table defination 4.txt (3 views, 863 bytes)
tabel defination 5.txt (2 views, 3.07 KB)
main Query .txt.txt (7 views, 3.43 KB)
Post #1564016
Posted Tuesday, April 22, 2014 2:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
Wow!
The script name have nothing to identify the table in question.
You really like to provide puzzles, do you?
Also, please provide the code of DML.GetRetailOrOnlineIndicator(), which seems to be a function you're calling in your insert script.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1564020
Posted Tuesday, April 22, 2014 2:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:38 PM
Points: 132, Visits: 816
CREATE FUNCTION [DML].[GetRetailOrOnlineIndicator] (
@GrpID INT
,@ChanID INT
,@SeqNo INT

)
RETURNS NVARCHAR(10)
AS
BEGIN
DECLARE @RetailOrOnlineIndicator NVARCHAR(10)

IF EXISTS (SELECT 1
FROM DML.ExtKeyWestRetailOnlineInfo
WHERE GrpID = @GrpID AND SeqNo = CONVERT( BIGINT, CAST(@ChanID AS VARCHAR(3)) + RIGHT('000000'+ CAST(@SeqNo AS VARCHAR(6)),6) ))
SET @RetailOrOnlineIndicator = 'Online'
ELSE
SET @RetailOrOnlineIndicator = 'Retail'

RETURN @RetailOrOnlineIndicator
END

GO
Post #1564025
Posted Tuesday, April 22, 2014 2:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
May I ask for the table definition involved?
You provided a view definition. But we'd need the table def. Especially for [ExtOLSMachine].




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1564030
Posted Tuesday, April 22, 2014 2:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:38 PM
Points: 132, Visits: 816
i dodn't have any table with name as ExtOLSMachine
Post #1564034
Posted Tuesday, April 22, 2014 2:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
The View [DML].[ExtOLSMachineEntitlement] refers to a table DML.ExtOLSMachine.
You might want to take the effort to have a look at your execution plan... -> The table is indeed referenced.

I guess I'm out of this challenge...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1564043
Posted Tuesday, April 22, 2014 3:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:38 PM
Points: 132, Visits: 816
Hi Can you pls help me to study and understand the execution plan properly, i have not done this work earlier but not sure where to look,what to look so posted all query.

Post #1564047
Posted Tuesday, April 22, 2014 5:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 1,787, Visits: 5,726
First thing I see in the plan is Query 6 which shows up as 99% of the cost.

Within Query 6, there is a section that accounts for 96% of the plan cost

I suspect (from the estimated and actual rowcounts and the predicates present in the Index Seek on ExtPAIntelActivationEvent.NCIX_ISOCountrycode which includes a condition; ISOCountryCode <> N'' which does not appear anywhere in the query text) that you have another view, perhaps within the Scalar function, perhaps this "DML.ExtKeyWestRetailOnlineInfo"

I would inspect that view to see if it is optimised for the purpose of this query.

I would also rewrite the Scalar function GetRetailOrOnlineIndicator as a Table Valued Function (see here : http://www.sqlservercentral.com/articles/T-SQL/91724/ )

Once that is done, test Query 6 from the plan without the INSERT to see if the row estimates are any better and to see if performance has improved.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1564062
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse