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.