CODEWITHSUNDEEP
sqlview
kubilay bayraktar
kubilay bayraktar

Reputation: 101

querying view is taking too much time

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

Answers (1)

gbn
gbn

Reputation: 432742

  • A view is not some magic piece of time saving code
  • A view is a macro that is expanded: no more, no less

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

Related Questions