Slow SQL execution for correlated scalar subquery

[monetdb-team]

Date: 2014-06-06 14:38:34 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.17.17 (Jan2014-SP2)
CC: @njnes

Last updated: 2014-10-31 14:14:57 +0100

Comment 19830

Date: 2014-06-06 14:38:34 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:29.0) Gecko/20100101 Firefox/29.0
Build Identifier:

SQL query:
SELECT DISTINCT mod, (SELECT COUNT(*) FROM SYS.FUNCTIONS M WHERE M.mod = F.mod) as count
FROM SYS.FUNCTIONS F
ORDER BY mod

takes a long time (3 secs) to complete.
It appears to execute the scalar subquery for each row in table SYS.FUNCTIONS, so some 1256 times, which probably clarifies the 3 secs needed to complete.

Reproducible: Always

Steps to Reproduce:

1. Start mserver5 (with SQL module loaded)
2. Start SQL frontend program (I used SQuirreL together with the MonetDB JDBC driver)
3. execute SQL query:
   SELECT DISTINCT mod, (SELECT COUNT(*) FROM SYS.FUNCTIONS M WHERE M.mod = F.mod) as count
   FROM SYS.FUNCTIONS F
   ORDER BY mod

It returns 17 rows but takes about 3 secs te complete.
That is very long.

Actual Results:

mod count
aggr 97
algebra 2
calc 752
dictionary 1
geom 40
gsl 1
inet 9
mmath 41
mtime 54
pcre 3
sql 86
str 96
txtsim 24
udf 4
url 16
user 27
zorder 3

Expected Results:

Same result but delivered in 0.04 secs

If the SQL parser would rewrite the correlated scalar subquery into:
SELECT DISTINCT F.mod, M.count
FROM SYS.FUNCTIONS F LEFT OUTER JOIN (SELECT mod, COUNT(*) as count FROM SYS.FUNCTIONS GROUP BY mod) M ON F.mod = M.mod
ORDER BY F.mod

it takes only 0.04 secs

SQL parser should be detect these correlated scalar subqueries (in SELECT-clause and/or WHERE-clause and/or GROUP BY-clause) and rewrite them.

Comment 19996

Date: 2014-08-07 09:31:47 +0200
From: @njnes

performance issue is fixed

Comment 20399

Date: 2014-10-31 14:14:57 +0100
From: @sjoerdmullender

Oct2014 has been released.