Reputation: 101
We have a view table and selecting from view is normally taking too much time.
for example: select x,y,z from view1
is taking too much time to load. This one is ok.
If you query: select x,y,z from view1 where x in ('abc')
queries in seconds.
If you query: select x,y,z from view1 where x in (select 'abc' from table1 where y='1234')
queries in seconds.
But if you query:
select x,y,z from view1 where x in (select x from table1 where y='1234')
is taking too much time to query and this is the problem we want to solve.
By the way you can think that : select x from table1 where y='1234'
returns 'abc'
with one row.
The scenario is decsribed above , what do you think could be the reason to take so much time to query on the third query. We have tried joining but it didn't work.
Upvotes: 1
Views: 6534
Reputation: 432742
So if your view has 5 tables and 4 JOINs, these will be evaluated every time.
So, my question should be:
Is there suitable indexing on the underlying column in some base table that generates column x in the view?
As for your last SQL, you are adding an extra IN clause and subquery to the view contents.
Note that you may know only one row is returned but the optimiser may not because or poor or out of date statistics, and/or bad indexing
My next question
Does table1 have good indexes to satisfy the subquery efficiently?
Either way, looking at the query plan will help you to identify what is going wrong
Upvotes: 4