I need some help to optimize this query as it runs in many threads in an over night, and it pegs CPU. I can't change schema or field attributes as that is not in my control. The attvalue is a multipurpose field and believed to be some of my bottle neck.
I have 32gig of memory and 4 cpu environment and it's a dedicated SQL machine. running sql2016 sp3.
I have included query with ALL inner joins and the table it's hitting...
Any help fixing query would be much appriciated ..
select d.operation sequence,
max(d.attValue) Part,
max(d1.attValue) Op,
sum(cast(d2.attValue as float)) Quantity,
max(d3.attValue) machine,
sum(cast(d4.attValue as float)) setupQty,
max(d5.attValue) runTime,
max(d6.attValue) tasking,
max(d7.attValue) setupTime,
max(d8.attValue)
scrapRate
from design d inner join
design d1
on d.design=d1.design and d.designAlternative=d1.designAlternative and d.version=d1.version
inner join design d2 on d.design=d2.design and d.designAlternative=d2.designAlternative and d.version=d2.version
inner join design d3 on d.design=d3.design and d.designAlternative=d3.designAlternative and d.version=d3.version
inner join design d4 on d.design=d4.design and d.designAlternative=d4.designAlternative and d.version=d4.version
inner join design d5 on d.design=d5.design and d.designAlternative=d5.designAlternative and d.version=d5.version
inner join design d6 on d.design=d6.design and d.designAlternative=d6.designAlternative and d.version=d6.version
inner join design d7 on d.design=d7.design and d.designAlternative=d7.designAlternative and d.version=d7.version
inner join design d8 on d.design=d8.design and d.designAlternative=d8.designAlternative and d.version=d8.version
where d.design = N'xxxx' and d.designAlternative=0
and d.version = (select max(version) from version
where design=d.design and designAlternative=d.designAlternative)
and d.class = N'process' and d.attribute =N'BOMitem'
and d1.class = N'operation' and d1.operation=d.operation and d1.attribute=N'task'
and d2.class = N'process' and d2.operation = d.operation and d2.position=d.position and d2.layercode=d.layercode and d2.attribute=N'BOMqtyPerUnit'
and d3.class = N'resource' and d3.operation = d.operation and d3.position=10 and d3.attribute=N'machine'
and d4.class = N'process' and d4.operation = d.operation and d4.position=d.position and d4.layercode=d.layercode and d4.attribute=N'BOMSetupQty'
and d5.class = N'resource' and d5.operation = d.operation and d5.position=10 and d5.attribute=N'runTime'
and d6.class = N'resource' and d6.operation = d.operation and d6.position=10 and d6.attribute=N'tasking'
and d7.class = N'resource' and d7.operation = d.operation and d7.position=10 and d7.attribute=N'setupTime'
and d8.class = N'resource' and d8.operation = d.operation and d8.position=10 and d8.attribute=N'scrapRate'
group by d.operation, d.attValue order by d.operation
CREATE TABLE [dbo].[design](
[design] [nvarchar](40) NOT NULL,
[version] [int] NOT NULL,
[layergroup] [nvarchar](40) NULL,
[layercode] [int] NOT NULL,
[material] [nvarchar](40) NULL,
[operation] [int] NOT NULL,
[class] [nvarchar](24) NOT NULL,
[object] [nvarchar](60) NOT NULL,
[attribute] [nvarchar](60) NOT NULL,
[sequenc_] [int] NULL,
[attValue] [nvarchar](255) NULL,
[attCalc] [nvarchar](255) NULL,
[locked] [int] NULL,
[formulaUsed] [int] NULL,
[tableUsed] [int] NULL,
[uses] [nvarchar](20) NULL,
[position] [int] NOT NULL,
[outofdate] [nvarchar](3) NULL,
[ruleChange] [nvarchar](3) NULL,
[lookupOptions] [nvarchar](255) NULL,
[updateDate] [datetime] NULL,
[lookupTable] [int] NULL,
[designAlternative] [int] NOT NULL,
CONSTRAINT [PK_design] PRIMARY KEY CLUSTERED
(
[design] ASC,
[version] ASC,
[layercode] ASC,
[operation] ASC,
[class] ASC,
[object] ASC,
[attribute] ASC,
[position] ASC,
[designAlternative] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[design] ADD DEFAULT ((0)) FOR [version]
GO
ALTER TABLE [dbo].[design] ADD DEFAULT ((0)) FOR [layercode]
GO
ALTER TABLE [dbo].[design] ADD DEFAULT ((0)) FOR [operation]
GO
ALTER TABLE [dbo].[design] ADD DEFAULT ((0)) FOR [position]
GO
ALTER TABLE [dbo].[design] ADD DEFAULT ((0)) FOR [designAlternative]
GO
November 7, 2024 at 4:10 pm
Is there any way you could provide some sample data? Say full sets for a couple of designs?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 7, 2024 at 4:29 pm
I'll try to workup some dummy data... to load ...
any quick observations?
Thanks.
November 7, 2024 at 5:40 pm
I find it curious that you use so many self-joins only to do some sums. I would probably move the additional criteria from the WHERE section to CASE statements in the grouping functions. Like this (not tested of course):
select
d.operation sequence,
max(d.attValue) Part,
max(case when d1.class = N'operation' and d1.operation=d.operation and d1.attribute=N'task' then d1.attValue end) Op,
sum(case when d1.class = N'process' and d1.operation = d.operation and d1.position=d.position and d1.layercode=d.layercode and d1.attribute=N'BOMqtyPerUnit' then cast(d1.attValue as float) end) Quantity,
max(case when d1.class = N'resource' and d1.operation = d.operation and d1.position=10 and d1.attribute=N'machine' then d1.attValue end) machine,
sum(case when d1.class = N'process' and d1.operation = d.operation and d1.position=d.position and d1.layercode=d.layercode and d1.attribute=N'BOMSetupQty' then cast(d1.attValue as float) end) setupQty,
max(case when d1.class = N'resource' and d1.operation = d.operation and d1.position=10 and d1.attribute=N'runTime' then d1.attValue end) runTime,
max(case when d1.class = N'resource' and d1.operation = d.operation and d1.position=10 and d1.attribute=N'tasking' then d1.attValue end) tasking,
max(case when d1.class = N'resource' and d1.operation = d.operation and d1.position=10 and d1.attribute=N'setupTime' then d1.attValue end) setupTime,
max(case when d1.class = N'resource' and d1.operation = d.operation and d1.position=10 and d1.attribute=N'scrapRate' then d1.attValue end) scrapRate
from design d
inner join design d1 on d.design=d1.design and d.designAlternative=d1.designAlternative and d.version=d1.version
where
d.design = N'xxxx'
and d.designAlternative=0
and d.version = (
select max(version)
from version
where
design=d.design
and designAlternative=d.designAlternative
)
and d.class = N'process' and d.attribute =N'BOMitem'
group by d.operation, d.attValue
order by d.operation
November 7, 2024 at 5:56 pm
... also, why do you group by d.attValue when you have a MAX() on that column in the SELECT?
November 7, 2024 at 6:04 pm
Wow that seems very fast...
I'm not sure why some of this code exists and its logic wasn't up front when developed
Much appreciated for your reply.
November 7, 2024 at 6:20 pm
Wow that seems very fast...
I'm not sure why some of this code exists and its logic wasn't up front when developed
Much appreciated for your reply.
Thanks, but... on second thought it will probably not produce the same result as the original query. All those inner joins with their individual criteria would limit the number of rows affected overall. and thus also the sum/max values.
But... maybe, just maybe, I have given you something to think about and tinker with just the same.
I would try to push my predicates up as high as possible, then isolate my top-level values, before getting the rest of the data.
Something like this
;WITH cteVersion AS (
SELECT v.design, v.designAlternative, maxVersion = MAX(v.version)
FROM [version] AS v
WHERE v.design = N'xxxx' AND v.designAlternative = 0
GROUP BY v.design, v.designAlternative
)
, cteDesign AS (
SELECT TOP(9223372036854775807) /* Trick teh query optomiser into 1st getting this base list */
d.design, d.designAlternative, d.version
, d.operation, d.position, d.layercode
, d.class, d.attribute, d.attValue
FROM cteVersion AS dv
INNER JOIN design AS d ON d.design = dv.design AND d.designAlternative = dv.designAlternative AND d.maxVersion = dv.version
AND d.class = N'process' AND d.attribute = N'BOMitem'
GROUP BY d.design, d.version, d.layercode, d.operation, d.class, d.attribute, d.position, d.designAlternative, d.attValue
)
SELECT sequence = d0.operation
, Part = MAX( d0.attValue )
, Op = MAX( d1.attValue )
, Quantity = SUM( CAST(d2.attValue AS float))
, machine = MAX( d3.attValue )
, setupQty = SUM( CAST(d4.attValue AS float))
, runTime = MAX( d5.attValue )
, tasking = MAX( d6.attValue )
, setupTime = MAX( d7.attValue )
, scrapRate = MAX( d8.attValue )
FROM cteDesign AS d0
INNER JOIN design AS d1 ON d1.design = d0.design AND d1.designAlternative = d0.designAlternative AND d1.version = d0.version
AND d1.class = N'operation' AND d1.attribute = N'task' AND d1.operation = d0.operation
INNER JOIN design AS d2 ON d2.design = d0.design AND d2.designAlternative = d0.designAlternative AND d2.version = d0.version
AND d2.class = N'process' AND d2.attribute = N'BOMqtyPerUnit' AND d2.operation = d0.operation AND d2.position = d0.position AND d2.layercode = d0.layercode
INNER JOIN design AS d3 ON d3.design = d0.design AND d3.designAlternative = d0.designAlternative AND d3.version = d0.version
AND d3.class = N'resource' AND d3.attribute = N'machine' AND d3.operation = d0.operation AND d3.position = 10
INNER JOIN design AS d4 ON d4.design = d0.design AND d4.designAlternative = d0.designAlternative AND d4.version = d0.version
AND d4.class = N'process' AND d4.attribute = N'BOMSetupQty' AND d4.operation = d0.operation AND d4.position = d0.position AND d4.layercode = d0.layercode
INNER JOIN design AS d5 ON d5.design = d0.design AND d5.designAlternative = d0.designAlternative AND d5.version = d0.version
AND d5.class = N'resource' AND d5.attribute = N'runTime' AND d5.operation = d0.operation AND d5.position = 10
INNER JOIN design AS d6 ON d6.design = d0.design AND d6.designAlternative = d0.designAlternative AND d6.version = d0.version
AND d6.class = N'resource' AND d6.attribute = N'tasking' AND d6.operation = d0.operation AND d6.position = 10
INNER JOIN design AS d7 ON d7.design = d0.design AND d7.designAlternative = d0.designAlternative AND d7.version = d0.version
AND d7.class = N'resource' AND d7.attribute = N'setupTime' AND d7.operation = d0.operation AND d7.position = 10
INNER JOIN design AS d8 ON d8.design = d0.design AND d8.designAlternative = d0.designAlternative AND d8.version = d0.version
AND d8.class = N'resource' AND d8.attribute = N'scrapRate' AND d8.operation = d0.operation AND d8.position = 10
GROUP BY d0.operation, d0.attValue
ORDER BY d0.operation;
November 8, 2024 at 10:00 am
Thanks DesNorton ...
Works really nicely and quick.
kaj,
I did try your solution it did seem to produce same results, but your saying
" All those inner joins with their individual criteria would limit the number of rows affected overall. and thus also the sum/max values."
If I try both solutions provided with different designs results are the same in output???
Thanks again
November 8, 2024 at 10:33 am
If all of the self-joins are needed, some filtered indexes might also help.
As always .... TEST, TEST, TEST
CREATE NONCLUSTERED INDEX ix_Design_Parts_BOMitem
ON design (design, designAlternative, version, class, operation, position, layercode)
INCLUDE(attValue)
WHERE class = N'process' AND attribute = N'BOMitem'
CREATE NONCLUSTERED INDEX ix_Design_Parts_task
ON design (design, designAlternative, version, class, operation)
INCLUDE(attValue)
WHERE class = N'operation' AND attribute = N'task'
CREATE NONCLUSTERED INDEX ix_Design_Parts_BOMqtyPerUnit
ON design (design, designAlternative, version, class, operation)
INCLUDE(position, layercode, attValue)
WHERE class = N'process' AND attribute = N'BOMqtyPerUnit'
CREATE NONCLUSTERED INDEX ix_Design_Parts_machine
ON design (design, designAlternative, version, class, operation)
INCLUDE(attValue)
WHERE class = N'resource' AND attribute = N'machine'
CREATE NONCLUSTERED INDEX ix_Design_Parts_BOMSetupQty
ON design (design, designAlternative, version, class, operation)
INCLUDE(position, layercode, attValue)
WHERE class = N'process' AND attribute = N'BOMSetupQty'
CREATE NONCLUSTERED INDEX ix_Design_Parts_runTime
ON design (design, designAlternative, version, class, operation)
INCLUDE(attValue)
WHERE class = N'resource' AND attribute = N'runTime'
CREATE NONCLUSTERED INDEX ix_Design_Parts_tasking
ON design (design, designAlternative, version, class, operation)
INCLUDE(attValue)
WHERE class = N'resource' AND attribute = N'tasking'
CREATE NONCLUSTERED INDEX ix_Design_Parts_setupTime
ON design (design, designAlternative, version, class, operation)
INCLUDE(attValue)
WHERE class = N'resource' AND attribute = N'setupTime'
CREATE NONCLUSTERED INDEX ix_Design_Parts_scrapRate
ON design (design, designAlternative, version, class, operation)
INCLUDE(attValue)
WHERE class = N'resource' AND attribute = N'scrapRate'
November 8, 2024 at 12:29 pm
thanks for your suggestions.
November 11, 2024 at 12:51 pm
Would the cte version run into any contention if this was called multiple times concurrently?
The process that would use this logic runs 16 threads using this cte query and just substituting different designs.
Thanks for responding
November 11, 2024 at 1:14 pm
It doesnt matter what query you run. The 16 threads are hitting the same table, joining back to itself 8 times. There will always be contention.
Bottom line - The more efficient you can make the query, the better.
I would test all the options in a staging environment.
November 11, 2024 at 1:39 pm
Thanks just didn't know if using the CTE would cause any issue with multi process.. It seems fast .. doing some testing..
Thanks for quick responses.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply