Issue Details (XML | Word | Printable)

Key: CORE-2290
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Eugenk Konkov
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Firebird vs MysQL: in Firebird next query executes too slow

Created: 24/Jan/09 06:28 PM   Updated: 20/Jan/12 08:11 PM
Component/s: None
Affects Version/s: None
Fix Version/s: None


 Description  « Hide
In MySQL same query run about 2-3sec, but in FireBird it take over 1min.
It there something wrong with Firebird optimizer?

select
 pkt.ID, pkt.class, pkt.name, count(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 = pkt.ID
left join saldoanal sld on sld.AnalitID1 = 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 = usr.ID
left join (
  select
   sum(bytesIn) as bytesIn
   ,sum(bytesOut) as bytesOut
   ,sum(onlineTime) as onlineTime
   ,users.ID
  from radiusd_actions
  left join users on users.name = userName
  where startTime >= '2008-12-01' and startTime < '2009-01-01' and users.isDeleted='N'
  group by users.ID
  ) rda on rda.ID = usr.ID

group by pkt.ID, pkt.class, pkt.name
order by pkt.class, 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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 24/Jan/09 11:30 PM
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).

Dmitry Yemanov added a comment - 25/Jan/09 04:57 AM
It would be useful to show us the MySQL execution plan as well.

Eugenk Konkov added a comment - 27/Jan/09 03:39 PM
How can I view MySQL plan?


Eugenk Konkov added a comment - 27/Jan/09 04:20 PM
explain select
 pkt.ID, pkt.type, pkt.name, count(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 = pkt.ID
left join saldo sld on sld.userID = 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 = usr.ID
left join (
  select
   sum(bytesIn) as bytesIn
   ,sum(bytesOut) as bytesOut
   ,sum(onlineTime) as onlineTime
   ,users.ID
  from radiusd_actions
  left join users on users.name = userName
  where startTime >= '2008-12-01' and startTime < '2009-01-01' and users.isDeleted='N'
  group by users.ID
  ) rda on rda.ID = usr.ID

group by pkt.ID, pkt.type, pkt.name
order by pkt.type, 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,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 KES.users.name 730 Using where
2 DERIVED opr range idx_date idx_date 8 \N 1852 Using where; Using temporary; Using filesort

André Müller Pereira added a comment - 20/Jan/12 02:52 PM - edited
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

Dmitry Yemanov added a comment - 20/Jan/12 08:11 PM
Please provide the plan for the original (slow) query, as well as its complete (without omissions) text.