New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Firebird vs MysQL: in Firebird next query executes too slow [CORE2290] #2715
Comments
Commented by: Sean Leyne (seanleyne) The LEFT JOINs are being re-evaluated with each row. You should should really replace them with "common table expressions" syntax (CTEs), support for which was added in v2.1. Look at the "README.common_table_expressions" file in the DOC\SQL.Extensions folder for implementation details. This is also really a subject that appears to be a support issue, not for the tracker (right now). |
Commented by: @dyemanov It would be useful to show us the MySQL execution plan as well. |
Commented by: Eugenk Konkov (kes) How can I view MySQL plan? |
Commented by: Eugenk Konkov (kes) explain select group by http://pkt.ID, pkt.type, http://pkt.name 1 PRIMARY pkt ALL \N \N \N \N 27 Using temporary; Using filesort |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Commented by: André Müller Pereira (andremp) I am converting MS Access to Firebird and have performance problem with Left join that use a SubSelect like this: SELECT ... I think that Firebird redo the subselect for each register joinned by TB1 If I put the subselect (SELECT ... FROM TB2 INNER JOIN TB3 ...) SELECT ... The performance increase much, the time to create table, insert and select is much more fast. I am using v2.1.3 |
Commented by: @dyemanov Please provide the plan for the original (slow) query, as well as its complete (without omissions) text. |
Submitted by: Eugenk Konkov (kes)
In MySQL same query run about 2-3sec, but in FireBird it take over 1min.
It there something wrong with Firebird optimizer?
select
http://pkt.ID, pkt.class, http://pkt.name, count(http://usr.ID) as usersInPacket
,round( sum( sld.sumdeb ), 2 ) as "balance+"
,round( sum( sld.sumkred ), 2 ) as "balance-"
,round( sum( COALESCE(opr.debet,0) ), 2 ) as debet
,round( sum( COALESCE(opr.kredit,0) ),2) as kredit
,round( sum( COALESCE( bytesIn, 0) )/1024/1024, 1) as bytesIn
,round( sum( COALESCE( bytesOut, 0) )/1024/1024, 1) as bytesOut
,round( sum( COALESCE( onlineTime, 0) )/3600, 1) as onlineTime
from packet pkt
left join users usr on usr.gid = http://pkt.ID
left join saldoanal sld on sld.AnalitID1 = http://usr.ID and nYear = 2008 and nMonth = 12
left join (
select
user_ID
,sum( IIF( bill>0, coalesce( bill, 0 ), 0 ) ) as debet
,sum( IIF( bill<0, coalesce( bill, 0 ), 0 ) ) as kredit
from opers opr
where opr.at_date >= '2008-12-01' and opr.at_date < '2009-01-01'
group by user_ID
) opr on opr.user_ID = http://usr.ID
left join (
select
sum(bytesIn) as bytesIn
,sum(bytesOut) as bytesOut
,sum(onlineTime) as onlineTime
,http://users.ID
from radiusd_actions
left join users on http://users.name = userName
where startTime >= '2008-12-01' and startTime < '2009-01-01' and users.isDeleted='N'
group by http://users.ID
) rda on http://rda.ID = http://usr.ID
group by http://pkt.ID, pkt.class, http://pkt.name
order by pkt.class, http://pkt.name
Plan
PLAN SORT (JOIN (JOIN (JOIN (SORT (JOIN (PKT NATURAL, USR INDEX (USERS_FK_GID))), SLD INDEX (SALDOANAL_IDX_ANALIT_SCHET_DATE, SALDOANAL_PK)), SORT (OPR OPR INDEX (OPERS_IDX_AT_DATE))), SORT (JOIN (RDA RADIUSD_ACTIONS INDEX (RADIUSD_ACTIONS_IDX_STARTTIME), RDA USERS INDEX (USERS_PK)))))
Adapted Plan
PLAN SORT (JOIN (JOIN (JOIN (SORT (JOIN (PKT NATURAL, USR INDEX (USERS_FK_GID))), SLD INDEX (SALDOANAL_IDX_ANALIT_SCHET_DATE, SALDOANAL_PK)), SORT (OPR OPR INDEX (OPERS_IDX_AT_DATE))), SORT (JOIN (RDA RADIUSD_ACTIONS INDEX (RADIUSD_ACTIONS_IDX_STARTTIME), RDA USERS INDEX (USERS_PK)))))
------ Performance info ------
Prepare time = 62ms
Execute time = 1m 13s 734ms
Avg fetch time = 4 096,33 ms
Current memory = 1 530 656
Max memory = 2 047 568
Memory buffers = 90
Reads from disk to cache = 20 761
Writes from cache to disk = 0
Fetches from cache = 3 998 121
All reads are indexed.
The text was updated successfully, but these errors were encountered: