SQL Server – Reducing Cost of View’s Group By

group bysql serverview

I have a view which joins couple tables and returns a list of available book copies. My view creation code is as follows:

CREATE VIEW AvailableCopies AS
SELECT Product.title, CONCAT(Author.firstName, ' ', Author.lastName) as Author_name, ProductType.pType as Product_type, Product.lendable, count(BookCopy.bisbn) as Copy_count
FROM Product
INNER JOIN BookCopy on BookCopy.bisbn = Product.isbn
INNER JOIN Author on Product.authorId = Author.id
LEFT JOIN ProductType on ProductType.id = Product.typeId
WHERE BookCopy.Available = 'true'
GROUP BY Product.title, Author.firstName, Author.lastName, Product.lendable, ProductType.pType;

The problem that I cannot get rid of, is GROUP BY line. I cannot omit it, if I do, I get the following error:

Error: Column 'Product.title' is invalid in the select list 
because it is not contained in either an aggregate function 
or the GROUP BY clause.

I spent quite a lot of time online, trying to solve this issue in some cheaper way than adding all my selected columns into GROUP BY, but sadly no luck. Execution plan says that my group by statement costs 40-41% of whole process! Is there a way to solve this? This view returns 37,5K results, at the moment for education purposes, I am only returning TOP 20 in my nodeJs application, but it is really frustrating to wait 8-10 seconds for a simple page to load, with a size of 20 in a recordset. I am aware of caching, but I would like to keep this solution as a last option for now.

pastetheplan is not accepting my execution plan xml so I cannot provide it.

After view creation I call Select TOP 20 from AvailableCopies . My database is hosted on Azure, I was trying to find version, but I couldn't. But as it is Azure, I assume SQL server 2017.

Best Answer

I can't see your indexes, but something worth trying is to grab the copy count in a subquery or CROSS APPLY: 

SELECT Product.title, CONCAT(Author.firstName, ' ', Author.lastName) as Author_name, ProductType.pType as Product_type, Product.lendable, BC.Copy_count
FROM Product
CROSS APPLY (
  SELECT COUNT(*) 
  FROM BookCopy 
  WHERE BookCopy.bisbn = Product.isbn
  AND BookCopy.Available = 'true'
  ) AS BC(Copy_count)
INNER JOIN Author on Product.authorId = Author.id
LEFT JOIN ProductType on ProductType.id = Product.typeId
WHERE BC.Copy_count>0;
Related Question