Em 4/4/2014 09:31, fabianoas...@gmail.com escreveu:
I agree with Tim. Whe dont uwe views too becase this speed problem.
Em 04/04/2014 08:51, "Tim Ward" <t...@telensa.com
<mailto:t...@telensa.com>> escreveu:
We generate the queries on the fly rather than trying to use a
view, precisely because of these problems.
So if CityName is not required in the output, there's no JOIN to City.
And if there's something in the WHERE clause such as "HobbyCode >
27" then we know that HobbyID can't be null, which means we can
use a JOIN instead of a LEFT JOIN, and quite often that results in
a better plan.
But yes, it does involve hundreds of lines of quite complex code
to analyse what information is required in a particular case and
generate the appropriate query, and in many cases the query
optimiser could, theoretically, have worked this out for itself,
but it doesn't.
On 04/04/2014 12:36, kokok_ko...@yahoo.es
<mailto:kokok_ko...@yahoo.es> wrote:
I use the latest version of FB 2.5.
There is a view for called PERSON. Each row represents a person
and it shows information as address, name, hobbies, etc.
There are 20 joined codification tables using LEFT JOIN. For
example all cities are codified, hobbies, etc.
The structure of the view is something like
CREATE VIEW PersonView ..
SELECT *
FROM PersonTable P
LEFT JOIN City ON City.ID = P.CityID
LEFT JOIN Hobby ON Hobby.ID = P.HobbyID
...
and so on for the 20 codified tables. City.ID is a primary key,
like all IDs for remaining codifications.
How can I optimize this view? My problem is that Firebird uses a
really slow plan because it reads ALL codification records.
For example, supposing PersonTable has 10 records.
SELECT COUNT(*) FROM PersonView
I would expect that Firebird only read 10 record, however it
reads 10 + 10x20 = 210 records.
In theory the optimizer could know that City.ID and Hobby.ID are
both primary keys (or unique) and therefore only scan PersonTable.
Another example:
SELECT CityName FROM PersonView
I woud expect that Firebird read 10 records for PersonTable and
10 for City table, but it reads 210.
The real problem is that I have millions of records in the
database, and a simple consult can take minutes when it could
take few seconds with an optimized plan.
What options do I have?
Thank you
--
Tim Ward
I disagree...
The problem does not lies on the view per se, but in the query...
If you don't need a particular table information, having it on the view
is useless... So if you have the need of a distinct rescult set only one
view would not handle this. So the on the fly generated query is not
better than the view because it's better optimzed than a view would be,
but because it's a diferent query.
Getting back to the original question:
Do you really need OUTER JOINS ?
Then you use all LEFT JOIN's the optimizer could just choose the order
of the "first to scan" table between the tables that are on the left of
an OUTER JOIN, in this case, there is no option, since there is just on
table.
If the Columns of the Foreign Keys could be null and in fact you really
need the unrelated records, you really need the OUTER JOIN, and a query
like this:
select
*
from
Person LEFT JOIN
City on (City.CityID = Person.CityID)
where
City.Name = 'Paris'
will do a full scan on table person and then use the PK index on
City.CityID, where the ideal plan would be
Use an index on City.Name and then do an index scan on the Foreign Key
index of Person.CityID
This is not possible (in the current version) since the optimizer should
start with one of the tables on the left side of an OUTER JOIN
Perhaps one day the optimizer could analyze this query and get to the
conclusion that it could be better written as
select
*
from
Person JOIN
City on (City.CityID = Person.CityID)
where
City.Name = 'Paris'
So the problem is not on the view per se, but on the way the query is
written.
Give it a try, change your LEFT JOIN's to INNER JOIN's and se how it
performs, be it a directly query or using a VIEW
see you !
