You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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:
Start mserver5 (with SQL module loaded)
Start SQL frontend program (I used SQuirreL together with the MonetDB JDBC driver)
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.
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.
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:
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.
The text was updated successfully, but these errors were encountered: