Getting data from two tables

Question

This query gets data, but for 116819 rows I need to wait 2:19 minutes. Getting the same data from the table takes 2-3 seconds, but I need some rows from the view (that are matching my data from table) in my query. How can I make it faster? The database that I work on is created properly.

select aa.*, b.quantity, b.price, case when b.itemNr is null then 'status' else 'status1' end as Status
 from(
 Select a.*, b.quantity, b.price, b.itemNr
 From table a
 left join view b
 on a.itemNR = b.itemNr and a.itemLine=b.itemLine) aa
 where aa.itemType = 'a type'
 group by ....
 order by ...

If I remove group by and order by, the query takes 2:10 minutes, so I reduce it only by 9 seconds.

Answer 1

Actually I have resolved my own problem. My problem was that the query was taking too long to display data and this problem came from my join to a view! After I found the tables that were creating the view I extracted the one were I found my needed columns and used it in my join. Probably the problem in joining views is with indexes. I need to read more about indexing a view but I suspect that this was my problem.

Answer 2

Try replacing the SELECT * with the columns and running it again. It's generally agreed that `Select* is considered harmful and can have some fairly major impacts of query performance.