CODEWITHSUNDEEP
sqlsql-server
Volker
Volker

Reputation: 465

Simple SQL select overloads my server

Problem is that this Task, though looking easy challenges my SQL Server so I have to look for an easier one.

I have table items (with 300.000 rows) 

item  
-----
10  
11  
13  
14  
15

Then I have table parent-Child (with 900.000 rows)

Parent  Child
--------------
10      13
14      13
13      15

For every row in items I want to find out if

  1. item is at least one time in Parent and NOT in Child
  2. item is in Parent AND Child
  3. item is in Child AND NOT in Parent
  4. is neither (ok - the rest...)

I want to write the related case a/b/c/d into a dedicated column in item table.

The approaches Select -> worked fine as long as there were few rows -> kill my server with growing log and extreme CPU load

-- Select with CTE
With G1 (P) as 
(
    select PARENT  
    from parent_child
    where < ... condition> 
), G2 (C) as 
(
    select CHILD 
    from parent_child
    where  < ... condition>
)
update Item_Master 
set Item_Assembly_Level = '1' 
where Item_Number in (select * from G1)  
  and Item_Number not in  (select * from G2) ;

Upvotes: 1

Views: 75

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13393

you can test this.

UPDATE I SET Item_Assembly_Level = 
            CASE 
                WHEN P.PARENT  IS NOT NULL AND C.CHILD  IS NULL THEN 1      --a) item is at least one time in Parent and NOT in Child
                WHEN P.PARENT  IS NOT NULL AND C.CHILD  IS NOT NULL THEN 2  --b) item is in Parent AND Child
                WHEN P.PARENT  IS NULL AND C.CHILD  IS NOT NULL THEN 3      --c) item is in Child AND NOT in Parent
                ELSE 4                                                  --d) is neither (ok - the rest...)
            END
FROM Item_Master I
    LEFT JOIN parent_child P ON I.Item_Number = P.PARENT 
    LEFT JOIN parent_child C ON I.Item_Number = C.CHILD

Upvotes: 1

MatBailie
MatBailie

Reputation: 86765

With G1 (P)
as (
select DISTINCT PARENT from parent_child
where < ... condition> 

EXCEPT

select DISTINCT CHILD from parent_child
where  < ... condition>
)
update Item_Master set Item_Assembly_Level = '1' where 
Item_Number in (select P from G1)
;

Upvotes: 0

Related Questions