December 9, 2016 at 1:39 am
Hi All,
COuld you please help me to tune the below query. I have attached the query plan of this query. Its going ont he nested loop and its cost more than 70%. Please help me out.
The epmdocument,epmdocumentmaster and wtprart is having more than 2 million rows
SELECT A0.classnamekeyteamId,A0.idA3teamId,A0.teamTemplateIdIsNull,A0.classnamekeyteamTemplateId,A0.idA3teamTemplateId,A0.enabledtemplate,A0.templatedtemplate,
CONVERT(varchar,A0.createStampA2,120),A0.markForDeleteA2,CONVERT(varchar,A0.modifyStampA2,120),A0.idA2A2,A0.updateCountA2,CONVERT(varchar,A0.updateStampA2,120),
CONVERT(varchar,A0B.createStampA2,120),A0B.markForDeleteA2,CONVERT(varchar,A0B.modifyStampA2,120),A0B.classnameA2A2,A0B.idA2A2,A0B.updateCountA2,
CONVERT(varchar,A0B.updateStampA2,120),A0B.branchIdA2typeDefinitionRefe,A0B.idA2typeDefinitionReference
FROM EPMDocument A0
INNER JOIN EPMDocumentMaster A0B ON (A0.idA3masterReference = A0B.idA2A2),
WTPart A1,EPMDocument A2,EPMBuildRule A3,EPMReferenceLink A4
December 9, 2016 at 2:22 am
Looking at your query plan, the join you have to PK_WTPart is your big cost, with just under an estimated 4 Million rows at around 9kb each. That's about 34 Gigs of data...
is there any reason why you're pulling data from a bunch of tables with no declare join clauses, or is your aim to create every single possible permutation? I mean, look at your final Nested Loops Estimated rows, it's... I actually don't know what that number is (1.68156E+31)... I think Google tells me it's something like 16 decillion, 815 nonillion, 600 octillion estimated rows... at 52Kb each, I think you might as well be killing your SQL Server at that stage.... I'm not even going to covert that into a storage cost, but I'm pretty sure no SQL Server has that amount of storage space or Memory resource..
We don;'t have any DDL here or Sample data (see my signature), but maybe you should considering doing some JOIN criteria.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 9, 2016 at 2:44 am
Start by removing the redundant tables which are cross joined in the from clause, not used in the query so why are they there?
😎
wtpart A1,
epmdocument A2,
epmbuildrule A3,
epmreferencelink A4
The query in a readable format
SELECT A0.classnamekeyteamid,
A0.ida3teamid,
A0.teamtemplateidisnull,
A0.classnamekeyteamtemplateid,
A0.ida3teamtemplateid,
A0.enabledtemplate,
A0.templatedtemplate,
CONVERT(VARCHAR, A0.createstampa2, 120),
A0.markfordeletea2,
CONVERT(VARCHAR, A0.modifystampa2, 120),
A0.ida2a2,
A0.updatecounta2,
CONVERT(VARCHAR, A0.updatestampa2, 120),
CONVERT(VARCHAR, A0B.createstampa2, 120),
A0B.markfordeletea2,
CONVERT(VARCHAR, A0B.modifystampa2, 120),
A0B.classnamea2a2,
A0B.ida2a2,
A0B.updatecounta2,
CONVERT(VARCHAR, A0B.updatestampa2, 120),
A0B.branchida2typedefinitionrefe,
A0B.ida2typedefinitionreference
FROM epmdocument A0
INNER JOIN epmdocumentmaster A0B
ON ( A0.ida3masterreference = A0B.ida2a2 ),
wtpart A1,
epmdocument A2,
epmbuildrule A3,
epmreferencelink A4
December 9, 2016 at 3:06 am
moosamca (12/9/2016)
Hi All,COuld you please help me to tune the below query. I have attached the query plan of this query. Its going ont he nested loop and its cost more than 70%. Please help me out.
The epmdocument,epmdocumentmaster and wtprart is having more than 2 million rows
SELECT A0.classnamekeyteamId,A0.idA3teamId,A0.teamTemplateIdIsNull,A0.classnamekeyteamTemplateId,A0.idA3teamTemplateId,A0.enabledtemplate,A0.templatedtemplate,
CONVERT(varchar,A0.createStampA2,120),A0.markForDeleteA2,CONVERT(varchar,A0.modifyStampA2,120),A0.idA2A2,A0.updateCountA2,CONVERT(varchar,A0.updateStampA2,120),
CONVERT(varchar,A0B.createStampA2,120),A0B.markForDeleteA2,CONVERT(varchar,A0B.modifyStampA2,120),A0B.classnameA2A2,A0B.idA2A2,A0B.updateCountA2,
CONVERT(varchar,A0B.updateStampA2,120),A0B.branchIdA2typeDefinitionRefe,A0B.idA2typeDefinitionReference
FROM EPMDocument A0
INNER JOIN EPMDocumentMaster A0B ON (A0.idA3masterReference = A0B.idA2A2),
WTPart A1,EPMDocument A2,EPMBuildRule A3,EPMReferenceLink A4
There is some mis-match here, the query you posted is not the same as the one in the execution plan.
😎
The query you posted
SELECT A0.classnamekeyteamid,
A0.ida3teamid,
A0.teamtemplateidisnull,
A0.classnamekeyteamtemplateid,
A0.ida3teamtemplateid,
A0.enabledtemplate,
A0.templatedtemplate,
CONVERT(VARCHAR, A0.createstampa2, 120),
A0.markfordeletea2,
CONVERT(VARCHAR, A0.modifystampa2, 120),
A0.ida2a2,
A0.updatecounta2,
CONVERT(VARCHAR, A0.updatestampa2, 120),
CONVERT(VARCHAR, A0B.createstampa2, 120),
A0B.markfordeletea2,
CONVERT(VARCHAR, A0B.modifystampa2, 120),
A0B.classnamea2a2,
A0B.ida2a2,
A0B.updatecounta2,
CONVERT(VARCHAR, A0B.updatestampa2, 120),
A0B.branchida2typedefinitionrefe,
A0B.ida2typedefinitionreference
FROM epmdocument A0
INNER JOIN epmdocumentmaster A0B
ON ( A0.ida3masterreference = A0B.ida2a2 ),
wtpart A1,
epmdocument A2,
epmbuildrule A3,
epmreferencelink A4
The actual query from the execution plan
SELECT A1.blob$entrysetadhocacl,
A1.classnamea2a2,
A1.branchida2typedefinitionrefe,
A1.ida3a2ownership,
A1.ida2a2,
A1.typeadministrativelock,
A1.classnamekeydomainref,
A1.securitylabels,
A1.entrysetadhocacl,
A1.statestate,
A1.ida3domainref,
A1.classnamekeya2ownership,
A2.blob$entrysetadhocacl,
A2.classnamea2a2,
A2.branchida2typedefinitionrefe,
A2.ida3a2ownership,
A2.ida2a2,
A2.typeadministrativelock,
A2.classnamekeydomainref,
A2.securitylabels,
A2.entrysetadhocacl,
A2.statestate,
A2.ida3domainref,
A2.classnamekeya2ownership,
A1.ida2a2,
'wt.epm.EPMDocument',
A0.administrativelockisnull,
A0.typeadministrativelock,
A0.classnamekeyc10,
A0.ida3c10,
A0.authoringappversion,
A0.classnamekeyb10,
A0.ida3b10,
A0.blob$entrysetadhocacl,
A0.boxextentsisnull,
A0.axd10,
A0.ayd10,
A0.azd10,
A0.bxd10,
A0.byd10,
A0.bzd10,
A0.checkoutinfoisnull,
A0.statecheckoutinfo,
A0.classnamekeycontainerreferen,
A0.ida3containerreference,
A0.dbkeysize,
A0.derived,
A0.description,
A0.classnamekeydomainref,
A0.ida3domainref,
A0.entrysetadhocacl,
A0.eventset,
A0.expression,
A0.extentsvalid,
A0.familytablestatus,
A0.classnamekeya2folderinginfo,
A0.ida3a2folderinginfo,
A0.classnamekeyb2folderinginfo,
A0.ida3b2folderinginfo,
A0.classnamekeyformat,
A0.ida3format,
A0.hashangingchange,
A0.haspendingchange,
A0.hasresultingchange,
A0.hasvariance,
A0.indexersindexerset,
A0.inheriteddomain,
A0.iopstateinteropinfo,
A0.stateinteropinfo,
A0.branchiditerationinfo,
A0.classnamekeyd2iterationinfo,
A0.ida3d2iterationinfo,
A0.classnamekeye2iterationinfo,
A0.ida3e2iterationinfo,
A0.iterationida2iterationinfo,
A0.latestiterationinfo,
A0.classnamekeyb2iterationinfo,
A0.ida3b2iterationinfo,
A0.noteiterationinfo,
A0.classnamekeyc2iterationinfo,
A0.ida3c2iterationinfo,
A0.stateiterationinfo,
A0.lengthscale,
CONVERT(VARCHAR, A0.datelock, 120),
A0.classnamekeya2lock,
A0.ida3a2lock,
A0.notelock,
A0.classnamekeymasterreference,
A0.ida3masterreference,
A0.maximumallowed,
A0.minimumrequired,
A0.missingdependents,
A0.oneoffversionida2oneoffversi,
A0.classnamekeya2ownership,
A0.ida3a2ownership,
A0.placeholder,
A0.referencecontrolisnull,
A0.geomrestre10,
A0.geomrestrrecursivee10,
A0.scopee10,
A0.violrestrictione10,
A0.revisionnumber,
A0.classnamekeyrootitemreferenc,
A0.ida3rootitemreference,
A0.securitylabels,
A0.atgatestate,
A0.classnamekeya2state,
A0.ida3a2state,
A0.statestate,
A0.teamidisnull,
A0.classnamekeyteamid,
A0.ida3teamid,
A0.teamtemplateidisnull,
A0.classnamekeyteamtemplateid,
A0.ida3teamtemplateid,
A0.enabledtemplate,
A0.templatedtemplate,
CONVERT(VARCHAR, A0.createstampa2, 120),
A0.markfordeletea2,
CONVERT(VARCHAR, A0.modifystampa2, 120),
A0.ida2a2,
A0.updatecounta2,
CONVERT(VARCHAR, A0.updatestampa2, 120),
A0.branchida2typedefinitionrefe,
A0.ida2typedefinitionreference,
A0.verified,
A0.versionida2versioninfo,
A0.versionlevela2versioninfo,
A0.versionsortida2versioninfo,
A0B.cadname,
A0B.authoringapplication,
A0B.collapsible,
A0B.classnamekeycontainerreferen,
A0B.ida3containerreference,
A0B.defaultunit,
A0B.docsubtype,
A0B.doctype,
A0B.generictype,
A0B.globalid,
A0B.NAME,
A0B.documentnumber,
A0B.classnamekeya6,
A0B.ida3a6,
A0B.classnamekeyorganizationrefe,
A0B.ida3organizationreference,
A0B.ownerapplication,
A0B.series,
CONVERT(VARCHAR, A0B.createstampa2, 120),
A0B.markfordeletea2,
CONVERT(VARCHAR, A0B.modifystampa2, 120),
A0B.classnamea2a2,
A0B.ida2a2,
A0B.updatecounta2,
CONVERT(VARCHAR, A0B.updatestampa2, 120),
A0B.branchida2typedefinitionrefe,
A0B.ida2typedefinitionreference
FROM wtprod.epmdocument (nolock) A0
INNER JOIN wtprod.epmdocumentmaster (nolock) A0B
ON ( A0.ida3masterreference = A0B.ida2a2 ),
wtprod.wtpart (nolock) A1,
wtprod.epmdocument (nolock) A2,
wtprod.epmbuildrule (nolock) A3,
wtprod.epmreferencelink (nolock) A4
December 9, 2016 at 3:21 am
Didn't even notice that. The query still doesn't have any fields returned from [font="System"]wtprod.epmbuildrule[/font] or [font="System"]wtprod.epmreferencelink[/font], so they can still be taken out of your CROSS JOIN.
I still can't see a purpose for joining all of your data to every single row in [font="System"]wtprod.wtpart[/font].
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 9, 2016 at 3:29 am
moosamca (12/9/2016)
Hi All,COuld you please help me to tune the below query. I have attached the query plan of this query. Its going ont he nested loop and its cost more than 70%. Please help me out.
The epmdocument,epmdocumentmaster and wtprart is having more than 2 million rows
SELECT A0.classnamekeyteamId,A0.idA3teamId,A0.teamTemplateIdIsNull,A0.classnamekeyteamTemplateId,A0.idA3teamTemplateId,A0.enabledtemplate,A0.templatedtemplate,
CONVERT(varchar,A0.createStampA2,120),A0.markForDeleteA2,CONVERT(varchar,A0.modifyStampA2,120),A0.idA2A2,A0.updateCountA2,CONVERT(varchar,A0.updateStampA2,120),
CONVERT(varchar,A0B.createStampA2,120),A0B.markForDeleteA2,CONVERT(varchar,A0B.modifyStampA2,120),A0B.classnameA2A2,A0B.idA2A2,A0B.updateCountA2,
CONVERT(varchar,A0B.updateStampA2,120),A0B.branchIdA2typeDefinitionRefe,A0B.idA2typeDefinitionReference
FROM EPMDocument A0
INNER JOIN EPMDocumentMaster A0B ON (A0.idA3masterReference = A0B.idA2A2),
WTPart A1,EPMDocument A2,EPMBuildRule A3,EPMReferenceLink A4
"Query tuning" is making an existing query run more efficiently. This query doesn't yet work at all - you might be waiting a while to see all estimated 16,815,600,000,000,000,000,000,000,000,000 rows returned.
You need to find out what the relationships are between the tables referenced by the query - it's not something which can be guessed. Then tell us - so folks can help you construct your query for you.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply