I agree with Tim. Whe dont uwe views too becase this speed problem. Em 04/04/2014 08:51, "Tim Ward" <[email protected]> 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, [email protected] 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 > > >
