Can we Optimise this sql query?

Question

This is the schema:

  Product(maker, model, type)
  PC(code, model, speed, ram, hd, cd, price)

The Product table contains data on the maker, model number, and type of product ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all makers and product types. Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed field, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, '4x') - cd, and its price.

Now, the requirement is to find the printer makers also producing PCs with the lowest RAM capacity and the highest processor speed of all PCs having the lowest RAM capacity.

I wrote this query:

WITH outp AS
(
     SELECT 
         P.Maker, MAX(PC.Speed) AS Speed
     FROM 
         PC
     JOIN 
         Product P ON P.model = PC.Model
     WHERE
         P.maker IN (SELECT maker FROM Product WHERE type = 'Printer')
         AND PC.Ram = (SELECT MIN(ram) FROM PC)
     GROUP BY 
         Maker
)
SELECT maker 
FROM outp
WHERE speed = (SELECT MAX(speed) FROM outp) 

This is working but I want to optimise this query or find another query which is much less bulkier than this. Any help?

Thanks guys...

Answer 1

Using analytics might be faster:

SELECT * FROM 
(
  SELECT 
    P.Maker, 
    pc.speed, 
    MAX(PC.Speed) over() AS maxSpeed
  FROM 
    (SELECT pc.speed, pc.ram, p.maker, min(pc.ram) over() as minram FROM pc INNER JOIN Product P ON P.model = PC.Model) pc 
    INNER JOIN
    Product pr 
    ON 
      pc .maker = pr.maker AND
      pr.type = 'printer' 
  WHERE
    pc.Ram = pc.minram
  ) a
WHERE 
  a.speed = a.maxspeed

I believe you first want to filter down to only pc manufacturers that make printers, where those pcs have the minimum of all PCs ram

And then from that subset, find the fastest pc

But if you want to find the PC whose ram is minimum and whose speed is maximum of all pcs (not just the fastest of the low ram spec ones) then you can move the max speed over... clause alongside the min ram clause, ditch the outermost query and put the maxspeed condition in the same where as the min ram condition