Re: [firebird-support] Reading unnecessary records
On 04/04/2014 23:36, Alexandre Benson Smith wrote: 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. All of which is true, but it is also true that the optimiser doesn't always work out that some of the stuff returned by the view is never used by a particular query that calls the view, and that therefore it doesn't need to be calculated. (To put it very very crudely.) -- Tim Ward
Re: [firebird-support] Reading unnecessary records
Please, let me explain it better: I cannot use INNER JOIN since many codification may be NULL. For example, a person could not have a hobby, or the the job codification could be NULL because he/she is a child. So, I need to use LEFT JOINs. This view, PersonView, is exposed to database users for their own consults. So, users using an assistant or SQL, they can create consults like: SELECT PersonName FROM PersonView WHERE BirthDate='2011/1/1' As I said, in theory the optimizer could know codification tables linked to PersonTable (City, Job, Hobby, ...) use primary keys (or unique) and therefore only scan PersonTable because in the above query, codification tables do not need to be involved. So, if I have 10 records in PersonTable, it would be 10 records read. The reality is that now Firebird reads ALL codification tables, event it would not be necessary, taking minutes when a optimized SQL query like SELECT PersonName FROM PersonTable WHERE BirthDate='2011/1/1' takes seconds For example, in a table with 10 millions of people, 20 codification tables, it means 210 millions of reads instead of 10 millions. The solution that some users of this forum are using, as they say, is to create on-the-fly the SQL statement instead of using views. It is not easy in many cases because it implies to analyze the SQL syntax and convert it, but for now is the only solution that I have found.
[firebird-support] Reading unnecessary records
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
Re: [firebird-support] Reading unnecessary records
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 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
Re: [firebird-support] Reading unnecessary records
I agree with Tim. Whe dont uwe views too becase this speed problem. Em 04/04/2014 08:51, Tim Ward 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 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
RE: [firebird-support] Reading unnecessary records
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. Where are you getting those details? Firebird reports every row that is being read, including internal index rows/data. Also the query PLAN details makes the distinction between fetches reads which were resolved by engine page cache and reads (actual reads from disk/or OS disk cache) So, without seeing the fetch details, it is not possible to say how your problem can be solved. 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. With that size of database, it is likely that the database cache size setting is low and should be increased. To answer that question, you will need to specific which version of the engine you are running? Classic, SuperClassic or SuperServer. Sean
RE: [firebird-support] Reading unnecessary records
I get these data from an old app called Interbase PLAN Analizer, that works pretty well. It shows me reads and also the plan. I can see how Firebird includes in the plan all codification tables, when it is not strictly necessary. Something changed in FB 2.5 version. I used 1.0 for a long time and the new one is slower in some cases. I use ClassicServer because of resilience offered by the multiple processes. I know that it is less efficient, but the number of concurrent users are really low, between 2-10. In any case, when I do tests, I am in my own dev PC and I am the only user. Probably I could improve tuning the database, but the bottle neck is the plan, that it is not optimized. As other users say in this forum about this thread, it is a huge problem. They, as probably me, cannot use views because they are slow. The solution is to built on the fly the SQL statement, but it is complex and we are doing the Firebird job. I would like to find a better way. Thank you
RE: [firebird-support] Reading unnecessary records
I get these data from an old app called Interbase PLAN Analizer, that works pretty well. It shows me reads and also the plan. I can see how Firebird includes in the plan all codification tables, when it is not strictly necessary. Something changed in FB 2.5 version. I used 1.0 for a long time and the new one is slower in some cases. True, but v1.0 would generate invalid results in some corner cases. Result accuracy must always trump pure performance. Probably I could improve tuning the database, but the bottle neck is the plan, that it is not optimized. But you are assuming that because the PLAN shows that a non-referenced table is actually being read during execution. That may not be the case. The real test to confirm is running the queries and looking at the total of READs and FETCHes. Given the small size of the page cache for typical Classic deployments, and the size of the database, it is possible that the reads are to load the index data alone. Separately: - Please provide the schema for the tables and views, as well as the PLAN which is generated - what is the database page size? - In your v1.0 vs. v2.5 performance tests, did you use the exact same database for the comparison As other users say in this forum about this thread, it is a huge problem. They, as probably me, cannot use views because they are slow. Views are no slower than the same as standalone SQL, this has been tested/benchmarked and confirmed. I am not convinced that your numbers prove that non-referenced JOIN tables actually impact performance (whether in View or standalone query) Sean
RE: [firebird-support] Reading unnecessary records
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. The engine/optimizer does know that. The engine, however, must check whether the matching key does exist, which requires reading the index structures. -- which raises a READ or FETCH operation! So, a count of 210 is not unreasonable. Sean
Odp: [firebird-support] Reading unnecessary records
Hi, Did you use order by or group by in this view? Regards, Karol Bieniaszewski - Reply message - Od: kokok_ko...@yahoo.es Do: firebird-support@yahoogroups.com Temat: [firebird-support] Reading unnecessary records Data: pt., kwi 4, 2014 13:36 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 nbsp; 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
Re: [firebird-support] Reading unnecessary records
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 !
