[Mondrian] Woefully slow query

Tom Barber(Alabs) tom at analytical-labs.com
Fri Jan 31 14:27:58 EST 2014


Ok here is the issue:

SELECT NON EMPTY {Hierarchize({{[Invoice Date].[Invoice 
Date].[Year].Members}, {[Invoice Date].[Invoice 
Date].[Month].Members}})} ON COLUMNS,
NON EMPTY {Hierarchize({[Customer].[Customer].[Customer].Members})} ON ROWS
FROM [Aging]

Using the following schema and ddl and ~ 35k rows on Postgres.

On  mondrian Trunk it does this for literally hours:

2014-01-31 16:54:16,950 DEBUG [mondrian.sql] 228: Segment.load: 
executing sql [select "aging_link"."Country Code" as "c0", 
"aging_link"."Customer Number" as "c1", "datelookup_1"."calendaryear" as 
"c2", sum("aging_link"."Invoice Amount") as "m0" from 
"public"."aging_link" as "aging_link", "public"."datelookup" as 
"datelookup_1" where "aging_link"."Country Code" = 'Somewhere' and 
"aging_link"."Customer Number" in ('272483545', '272483941', 
'272484027', '272484605', '272485439', '272485822', '272485950', 
'272486276', '272486317', '272486544', '272486867', '272487956', 
'272488313', '272488876', '272488959', '272489022', '272490318', 
'272490404', '272490420', '272491126', '272491270', '272491449', 
'272491452', '272491861', '272492033', '272492541', '272493292', 
'272493672', '272493870', '272494071', '272494899', '272496598', 
'272497054', '272497249', '272497492', '272497588', '272497997', 
'272498169', '272498718', '272499261', '272499571', '272500138', 
'272501706', '272501889', '272502530', '272503207', '272503278', 
'272504776', '272504789', '272505571', '272506037', '272506107', 
'272507126', '272507407', '272509487', '272510830', '272512951', 
'272513194', '272513219', '272513222', '272513251', '272513404', 
'272513590', '272514423', '272514915', '272515554', '272516825', 
'272516979', '272517000', '272517422', '272519192', '272519332', 
'272520123', '272521296', '272521308', '272521692', '272522187') and 
"aging_link"."Due Date" = "datelookup_1"."daydate" group by 
"aging_link"."Country Code", "aging_link"."Customer Number", 
"datelookup_1"."calendaryear"]

Running 100's of queries just iterating through the whole fact table.

If I run the identical schema on Mondrian 4 it runs a grand total of 10 
queries and the whole thing finishes in 12 seconds (including rendering 
12500 rows in Saiku)

2014-01-31 19:26:24,327 DEBUG [mondrian.sql] 125: 
SqlTupleReader.readTuples [[Invoice Date].[Invoice Date].[Year]]: 
executing sql [select "datelookup"."calendaryear" as "c0" from 
"aging_link" as "aging_link", "datelookup" as "datelookup" where 
"aging_link"."Invoice Date" = "datelookup"."daydate" group by 
"datelookup"."calendaryear" order by "datelookup"."calendaryear" ASC 
NULLS LAST]
2014-01-31 19:26:24,434 DEBUG [mondrian.sql] 125: , exec 106 ms
2014-01-31 19:26:24,435 DEBUG [mondrian.sql] 125: , exec+fetch 108 ms, 3 
rows
2014-01-31 19:26:24,435 DEBUG [mondrian.sql] 126: 
SqlTupleReader.readTuples [[Invoice Date].[Invoice Date].[Month]]: 
executing sql [select "datelookup"."calendaryear" as "c0", 
"datelookup"."calendarmonth" as "c1", "datelookup"."monthname" as "c2" 
from "aging_link" as "aging_link", "datelookup" as "datelookup" where 
"aging_link"."Invoice Date" = "datelookup"."daydate" group by 
"datelookup"."calendaryear", "datelookup"."calendarmonth", 
"datelookup"."monthname" order by "datelookup"."calendaryear" ASC NULLS 
LAST, "datelookup"."calendarmonth" ASC NULLS LAST]
2014-01-31 19:26:24,520 DEBUG [mondrian.sql] 126: , exec 85 ms
2014-01-31 19:26:24,521 DEBUG [mondrian.sql] 126: , exec+fetch 86 ms, 31 
rows
2014-01-31 19:26:24,522 DEBUG [mondrian.sql] 127: 
SqlTupleReader.readTuples [[Customer].[Customer].[Customer]]: executing 
sql [select "aging_link"."Country Code" as "c0", "aging_link"."Customer 
Number" as "c1", "aging_link"."Customer Name" as "c2" from "aging_link" 
as "aging_link" group by "aging_link"."Country Code", 
"aging_link"."Customer Number", "aging_link"."Customer Name" order by 
"aging_link"."Country Code" ASC NULLS LAST, "aging_link"."Customer 
Number" ASC NULLS LAST]
2014-01-31 19:26:25,254 DEBUG [mondrian.sql] 127: , exec 732 ms
2014-01-31 19:26:25,374 DEBUG [mondrian.sql] 127: , exec+fetch 852 ms, 
12382 rows
2014-01-31 19:26:25,669 DEBUG [mondrian.sql] 128: 
SqlStatisticsProvider.getColumnCardinality: executing sql [select 
count(distinct "Country Code") from "aging_link"]
2014-01-31 19:26:25,944 DEBUG [mondrian.sql] 128: , exec 274 ms
2014-01-31 19:26:25,944 DEBUG [mondrian.sql] 128: , exec+fetch 275 ms, 1 
rows
2014-01-31 19:26:25,944 DEBUG [mondrian.sql] 129: 
SqlStatisticsProvider.getColumnCardinality: executing sql [select 
count(distinct "calendaryear") from "datelookup"]
2014-01-31 19:26:25,966 DEBUG [mondrian.sql] 129: , exec 21 ms
2014-01-31 19:26:25,966 DEBUG [mondrian.sql] 129: , exec+fetch 22 ms, 1 rows
2014-01-31 19:26:25,966 DEBUG [mondrian.sql] 130: 
SqlStatisticsProvider.getColumnCardinality: executing sql [select 
count(distinct "Customer Number") from "aging_link"]
2014-01-31 19:26:26,177 DEBUG [mondrian.sql] 130: , exec 210 ms
2014-01-31 19:26:26,177 DEBUG [mondrian.sql] 130: , exec+fetch 211 ms, 1 
rows
2014-01-31 19:26:26,178 DEBUG [mondrian.sql] 131: Segment.load: 
executing sql [select "aging_link"."Country Code" as "c0", 
"aging_link"."Customer Number" as "c1", "datelookup"."calendaryear" as 
"c2", sum("aging_link"."Invoice Amount") as "m0" from "aging_link" as 
"aging_link", "datelookup" as "datelookup" where "aging_link"."Country 
Code" in ('BELGIUM', 'Belgium') and "datelookup"."calendaryear" in 
(2011, 2012, 2013) and "aging_link"."Invoice Date" = 
"datelookup"."daydate" group by "aging_link"."Country Code", 
"aging_link"."Customer Number", "datelookup"."calendaryear"]
2014-01-31 19:26:26,186 DEBUG [mondrian.sql] 132: 
SqlStatisticsProvider.getColumnCardinality: executing sql [select 
count(distinct "calendarmonth") from "datelookup"]
2014-01-31 19:26:26,221 DEBUG [mondrian.sql] 132: , exec 34 ms
2014-01-31 19:26:26,221 DEBUG [mondrian.sql] 132: , exec+fetch 35 ms, 1 rows
2014-01-31 19:26:26,222 DEBUG [mondrian.sql] 133: Segment.load: 
executing sql [select "aging_link"."Country Code" as "c0", 
"aging_link"."Customer Number" as "c1", "datelookup"."calendaryear" as 
"c2", "datelookup"."calendarmonth" as "c3", sum("aging_link"."Invoice 
Amount") as "m0" from "aging_link" as "aging_link", "datelookup" as 
"datelookup" where "aging_link"."Country Code" in ('BELGIUM', 'Belgium') 
and "datelookup"."calendaryear" in (2011, 2012, 2013) and 
"aging_link"."Invoice Date" = "datelookup"."daydate" group by 
"aging_link"."Country Code", "aging_link"."Customer Number", 
"datelookup"."calendaryear", "datelookup"."calendarmonth"]
2014-01-31 19:26:26,389 DEBUG [mondrian.sql] 131: , exec 210 ms
2014-01-31 19:26:26,434 DEBUG [mondrian.sql] 131: , exec+fetch 256 ms, 
12447 rows
2014-01-31 19:26:26,485 DEBUG [mondrian.sql] 133: , exec 261 ms
2014-01-31 19:26:26,542 DEBUG [mondrian.sql] 133: , exec+fetch 319 ms, 
18685 rows
2014-01-31 19:26:27,261 DEBUG [mondrian.sql] 134: Segment.load: 
executing sql [select "aging_link"."Country Code" as "c0", 
"aging_link"."Customer Number" as "c1", "datelookup"."calendaryear" as 
"c2", sum("aging_link"."Invoice Amount") as "m0" from "aging_link" as 
"aging_link", "datelookup" as "datelookup" where "aging_link"."Country 
Code" in ('Ireland', 'Sweden') and "aging_link"."Customer Number" in 
('953724073', '953724440', '960814035') and "datelookup"."calendaryear" 
in (2011, 2012, 2013) and "aging_link"."Invoice Date" = 
"datelookup"."daydate" group by "aging_link"."Country Code", 
"aging_link"."Customer Number", "datelookup"."calendaryear"]
2014-01-31 19:26:27,261 DEBUG [mondrian.sql] 135: Segment.load: 
executing sql [select "aging_link"."Country Code" as "c0", 
"aging_link"."Customer Number" as "c1", "datelookup"."calendaryear" as 
"c2", "datelookup"."calendarmonth" as "c3", sum("aging_link"."Invoice 
Amount") as "m0" from "aging_link" as "aging_link", "datelookup" as 
"datelookup" where "aging_link"."Country Code" in ('Ireland', 'Sweden') 
and "aging_link"."Customer Number" in ('953724073', '953724440', 
'960814035') and "datelookup"."calendaryear" in (2011, 2012, 2013) and 
"aging_link"."Invoice Date" = "datelookup"."daydate" group by 
"aging_link"."Country Code", "aging_link"."Customer Number", 
"datelookup"."calendaryear", "datelookup"."calendarmonth"]
2014-01-31 19:26:27,265 DEBUG [mondrian.sql] 135: , exec 3 ms
2014-01-31 19:26:27,266 DEBUG [mondrian.sql] 135: , exec+fetch 5 ms, 5 rows
2014-01-31 19:26:27,266 DEBUG [mondrian.sql] 134: , exec 4 ms
2014-01-31 19:26:27,267 DEBUG [mondrian.sql] 134: , exec+fetch 6 ms, 3 rows


The above is the entire query that is pushed to the database, as you can 
see, much quicker and cleaner.

Anyway, there you go.

Tom
-------------- next part --------------
A non-text attachment was scrubbed...
Name: schema.xml
Type: text/xml
Size: 8716 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20140131/a67a22bc/attachment.xml 
-------------- next part --------------
-- Table: aging_link

-- DROP TABLE aging_link;

CREATE TABLE aging_link
(
  recuid double precision,
  "Invoice Number" text,
  "Customer Number" text,
  "Customer Name" text,
  "Invoice Type" text,
  "Invoice Date" timestamp without time zone,
  "Due Date" timestamp without time zone,
  currency text,
  salesrep text,
  "Invoice Amount" double precision,
  "Amount Due Remaining" double precision,
  "Payment Terms" text,
  "Major Account Code" text,
  "Collector Code" text,
  "Credit Stop" character(1),
  "Customer Profile Class" text,
  "INV/CM" text,
  "Owner Station" text,
  "Payment Number" text,
  "EMPTY COLUMN1" timestamp without time zone,
  "EMPTY COLUMN2" text,
  "Country Code" text,
  "Load Date" timestamp without time zone,
  "Days Late" double precision,
  "LOC CUR Invoice Amount" double precision,
  "LOC CUR Amount Due Remaining" double precision,
  current double precision,
  "0-30" double precision,
  "31-60" double precision,
  "61-90" double precision,
  "91-120" double precision,
  "121-150" double precision,
  "151-180" double precision,
  "181-210" double precision,
  "211-240" double precision,
  "241-270" double precision,
  "271-300" double precision,
  "301-330" double precision,
  "331-360" double precision,
  "360+" double precision,
  "Short MAC" text,
  "Month Difference" double precision,
  "Fixed Bucket Date" timestamp without time zone,
  "EUR Invoice Amount" double precision,
  "EUR Amount Due Remaining" double precision,
  "Technical ID of Transaction" text,
  "Outsourcer Selection Value" text,
  "Amount Paid" text,
  "Amount Credited" text,
  "Amount Adjusted / Otherwise" text,
  "Paying Location Number" text,
  custbaltype text
)
WITH (
  OIDS=FALSE
);
ALTER TABLE aging_link
  OWNER TO superuser;

CREATE TABLE datelookup
(
  daydate date NOT NULL,
  yeardate date,
  monthdate date,
  weekdate date,
  quarterdate date,
  monthday integer,
  monthdays integer,
  monthname character varying(20),
  monthshortname character varying(20),
  calendaryear integer,
  calendarmonth integer,
  calendarweek integer,
  calendarquarter integer,
  leapyear character varying(20),
  monthweek integer,
  yearday integer,
  weekday integer,
  dayname character varying(20),
  dayshortname character varying(20),
  northseason character varying(20),
  southseason character varying(20),
  last_day_of_month boolean,
  last_working_day boolean,
  CONSTRAINT datelookup_pkey PRIMARY KEY (daydate)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE datelookup
  OWNER TO superuser;



More information about the Mondrian mailing list