Skip to content
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

Closed
firebird-automations opened this issue Jan 24, 2009 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

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).

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It would be useful to show us the MySQL execution plan as well.

@firebird-automations
Copy link
Collaborator Author

Commented by: Eugenk Konkov (kes)

How can I view MySQL plan?

@firebird-automations
Copy link
Collaborator Author

@firebird-automations
Copy link
Collaborator Author

Commented by: Eugenk Konkov (kes)

explain select
http://pkt.ID, pkt.type, http://pkt.name, count(http://usr.ID) as usersInPacket
,round( sum( sld.saldo ), 2 ) as "balance+"
,round( sum( sld.saldo ), 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 saldo sld on sld.userID = http://usr.ID and nYear = 2008 and nMonth = 12
left join (
select
userID
,sum( IF( bill>0, coalesce( bill, 0 ), 0 ) ) as debet
,sum( IF( bill<0, coalesce( bill, 0 ), 0 ) ) as kredit
from operations opr
where opr.date >= '2008-12-01' and opr.date < '2009-01-01'
group by userID
) opr on opr.userID = 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.type, http://pkt.name
order by pkt.type, http://pkt.name

1 PRIMARY pkt ALL \N \N \N \N 27 Using temporary; Using filesort
1 PRIMARY usr ALL \N \N \N \N 151
1 PRIMARY sld eq_ref idx_nYear_nMonth_userID idx_nYear_nMonth_userID 12 const,const,http://KES.usr.ID 1
1 PRIMARY <derived2> ALL \N \N \N \N 80
1 PRIMARY <derived3> ALL \N \N \N \N 67
3 DERIVED users ref udx_name,is_Deleted is_Deleted 1 106 Using where; Using temporary; Using filesort
3 DERIVED radiusd_actions ref idx_userName_startTime idx_userName_startTime 19 http://KES.users.name 730 Using where
2 DERIVED opr range idx_date idx_date 8 \N 1852 Using where; Using temporary; Using filesort

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

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 ...
FROM TB1
LEFT JOIN (SELECT ... FROM TB2 INNER JOIN TB3 ...) AS LJ1 ON TB1.X = LJ1.X

I think that Firebird redo the subselect for each register joinned by TB1
in the ibExpert Performance tab show the number of registers accessed by table, and the tables in subselect have more reads

If I put the subselect (SELECT ... FROM TB2 INNER JOIN TB3 ...)
to a table XX1 and use like this:

SELECT ...
FROM TB1
LEFT JOIN XX1 AS LJ1 ON TB1.X = LJ1.X

The performance increase much, the time to create table, insert and select is much more fast.
The tables in subselect have much less read access.

I am using v2.1.3

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Please provide the plan for the original (slow) query, as well as its complete (without omissions) text.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants