1
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
1
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.