June 1, 2015 at 10:14 am
Tables :
CREATE TABLE [Production].[Products](
[productid] [int] IDENTITY(1,1) NOT NULL,
[productname] [nvarchar](40) NOT NULL,
[supplierid] [int] NOT NULL,
[categoryid] [int] NOT NULL,
[unitprice] [money] NOT NULL,
[discontinued] [bit] NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[productid] 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
CREATE TABLE [Production].[Categories](
[categoryid] [int] IDENTITY(1,1) NOT NULL,
[categoryname] [nvarchar](15) NOT NULL,
[description] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[categoryid] 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
I wrote this code using cross apply :
select c.categoryid,a.productid,a.productname, a.unitprice
from Production.Categories as c
cross apply
(
select productid, productname, unitprice from Production.Products as p
where c.categoryid=p.categoryid
order by categoryid, unitprice offset 0 rows fetch first 1 rows only
) as a
order by categoryid
go
My book suggested CTE like this :
with CTE_min as
(
select categoryid, min(unitprice) as mn
from Production.Products
group by categoryid
)
select p.categoryid, p.productid, p.productname, p.unitprice
from Production.Products as p
JOIN CTE_min as m on p.categoryid=m.categoryid
and p.unitprice=m.mn ;
This database is TSQL2012, I am learning for SQL Server MCSA. Exercise was to write a solution that uses a CTE to return the products with the lowest unit price per category. I done it but later I write this cross apply solution as well, i run Actual Execution plan and it was 88% for cross join and only 12% for CTE. My question is why is CTE so much better?
English isn't my native, thanks in advance.
June 1, 2015 at 11:01 am
To start, you're using 2 tables instead of 1.
Even if you weren't, the subquery used in the CROSS APPLY will be executed once per row (as shown by the nested loops operator in the plan).
Costs and percentages on an execution plan are just estimates and aren't the best way to define better performance. It works in this case but not always.
If you look at the execution plan, you'll note that the engine needs to do a lot more work then the CTE option.
Something else that you need to know is that the queries are not equivalent. They return different rows. For them to be the same, you should use TOP 1 WITH TIES instead of the OFFSET.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply