Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
Hi one of our customer is facing slowness when performing some particular queries on ColumnStore 1.5 .
Here are these queries :
Query 1:
=======
DROP TABLE if EXISTS `fait_facturation` ;
1 queries executed, 1 success, 0 errors, 0 warnings
Query: DROP TABLE if EXISTS `fait_facturation`
0 row(s) affected
Execution Time : 0.578 sec
Transfer Time : 1.024 sec
Total Time : 1.603 sec
Query 2:
=======
CREATE TABLE `fait_facturation` (
`FACTURATION_ID` varchar(20) DEFAULT NULL,
`PERIODE_ETP` varchar(7) DEFAULT NULL,
`CODE_ETT_FACTURATION` varchar(50) DEFAULT NULL) engine=columnstore;
1 queries executed, 1 success, 0 errors, 0 warnings
Query: CREATE TABLE `fait_facturation` ( `FACTURATION_ID` varchar(20) DEFAULT NULL, `PERIODE_ETP` varchar(7) DEFAULT NULL, `CODE_ETT_FA...
0 row(s) affected
Execution Time : 0.659 sec
Transfer Time : 1.024 sec
Total Time : 1.683 sec
Query 3:
=======
INSERT INTO `fait_facturation`(
with recursive series as (
select 1 as FACTURATION_ID union all
select FACTURATION_ID +1 as id from series
where FACTURATION_ID < 500000 )
select FACTURATION_ID,concat(CHAR(65+MOD(ROUND(RAND()*100),26)),CHAR(65+MOD(ROUND(RAND()*100),26)),CHAR(65+MOD(ROUND(RAND()*100),26)))as PERIODE_ETP ,
SUBSTRING(REPLACE(REPLACE(REPLACE( TO_BASE64(MD5(RAND())), '=',''),'+',''),'/',''), 2, 40) AS CODE_ETT_FACTURATION from series);
1 queries executed, 1 success, 0 errors, 0 warnings
Query: INSERT INTO `fait_facturation`( with recursive series as ( select 1 as FACTURATION_ID union all select FACTURATION_ID +1 as id f...
500000 row(s) affected
Execution Time : 3.421 sec
Transfer Time : 0 sec
Total Time : 3.422 sec
Query 4:
========
SELECT PERIODE_ETP, CODE_ETT_FACTURATION, COUNT(1), COUNT(FACTURATION_ID)
FROM fait_facturation
GROUP BY PERIODE_ETP,CODE_ETT_FACTURATION,FACTURATION_ID
ORDER BY PERIODE_ETP DESC ,CODE_ETT_FACTURATION,FACTURATION_ID;
Exec: 2,504s Total: 4,132s 500000 rows
Query 4 bis (very similar to the previous one)
=========
SELECT PERIODE_ETP, CODE_ETT_FACTURATION, COUNT(1), COUNT(DISTINCT FACTURATION_ID)
FROM fait_facturation
GROUP BY 1,2
ORDER BY 1 DESC ,2;
On CS 1.2 the query is executed in 47 secondes and on CS 10.5 the query is executed in 98s