1. Watch out for all the spelling mistakes with your table
core_datadefinition_Details
or
core_datadefinition_Detailes
or
core_data_definition_Details
- many members would not bother persisting through that to get your code to work.
2. As I mentioned in my comments, the addition of appropriate indexes will help remove the table scans in your plan e.g.
create index gen_ZfeatureKey on [dbo].[gen] ([ZfeatureKey]);
CREATE INDEX PA_PartID ON [dbo].[PartAttributes] ([PartID]);
CREATE INDEX PA_Feature ON [dbo].[PartAttributes] ([ZfeatureKey]);
3. When I was trying to pull out bits of your code I noticed that you have correlated sub-queries - see
SQL server performance - Death by correlated subqueries - SQL Service[
^]
4. I think you may have over-done the normalization in your table schemas - personally I would combine
core_datadefinition_Details
and
PartAttributes
into a single table
5. I suggested breaking out the code into CTEs - this was originally to make it easier for me to read, but as in point 3 above it helped me track down potential issues. In the code below cte1 addresses my point 4 and gets key information into a simpler format.
cte2 takes that information to do the STUFF and capture ConCount by PartID
The final part of the query was just going to take what I had and join it to the [gen] table to get the remaining details. but I can't determine what to join to [gen] ON - you have ZFeatureKey as the common column - but are combining the features themselves into the $ separated list
6. So that leads me to question your schema again, however this code now gets the expected results
;with cte1 as
(
select ColumnName, FeatureValue, p.PartID, p.ZfeatureKey, Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID
from core_datadefinition_Details d
inner join PartAttributes p on d.ColumnNumber = p.ZfeatureKey
INNER JOIN gen Co ON Co.ZfeatureKey = p.ZfeatureKey
), cte2 as
(
select PartID, Code,CodeTypeID,RevisionID,ZPLID, count(*) as ConCount,
stuff(( SELECT '$' + CAST( ColumnName AS VARCHAR(300)) AS [text()]
FROM cte1
ORDER BY cte1.ZfeatureKey
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
, 1, 1, '') as FeatureName,
stuff(( SELECT '$' + CAST( FeatureValue AS VARCHAR(300)) AS [text()]
FROM cte1
ORDER BY cte1.ZfeatureKey
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
, 1, 1, '') as FeatureValue
from cte1
Group By PartID,Code,CodeTypeID,RevisionID,ZPLID
)
select PartID, Code,CodeTypeID,RevisionID,ZPLID,ConCount, FeatureName, FeatureValue
from cte2
Doesn't have any correlated sub-queries and (courtesy of the indexes in point 2) also doesn't have any table scans in the Execution plan.
Caveat: I don't know if this will work fully in your case as one PartID is not enough data for proper testing
One final point - use the
Reply
link next to comments so that the member posting the comment is notified of your reply.