SQL Queries MUCH slower in 2019.1.2, serious performance regression

已回答

创建于 2019年05月28日 16:20

Hi,

I just upgraded pycharm from 2018.3.4 to 2019.1.2, I do both python and SQL extensively. Suddenly, "SELECT * FROM ..." queries on a large table take minutes to return the first 500 rows, while on 2018.3.4 it only took a few seconds. This is consistent across several different databases and tables. I'm using both Azure DB and SQL Server.

Doing "SELECT TOP 10 * FROM..." is still fast. But as I increase the 10 to thousands or millions it gets really slow, even though it's only returning the first 500 rows as usual.

Reverting to 2018.3.4 made the queries fast again.

12 条评论

Andrey Dernov

创建于 2019年05月29日 13:16

What is your OS? If you have antivirus/firewall please try with it disabled or make sure that IDE settings directories, IDE installation home, IDE process are excluded from the scan.

Make also sure to check with the same jdbc drivers (see how to change if needed: https://www.jetbrains.com/help/datagrip/connectivity-problems.html#step-3-check-the-driver-version).

If issue remains, please attach IDE log and sql log (Help | Show SQL Log in ... action) directories zipped after reproducing and output from Test Connection button for the Data Source.

8forty

创建于 2019年05月29日 14:16

Thanks. OS is Windows. I added exclusions for the settings/installation dirs and the process, made no difference. JDBC driver is the same version (7.2.1 - Azure DB) as I'm using on 2018.3.4.

I don't see any way to attach zip file of logs?

Note that the first 500 rows ARE returned pretty fast (just like 2018.3.4), but the query keeps running for minutes after that (unlike 2018.3.4).

Andrey Dernov

创建于 2019年05月29日 14:35

For uploading you can use ftp://ftp.intellij.net/.uploads / https://uploads.services.jetbrains.com or any file sharing service.

8forty

创建于 2019年05月29日 15:06

Ok, uploaded the file:

pycharm-2019.1.2-logs-20190529.zip (33 kb)

Andrey Dernov

创建于 2019年05月29日 17:27

Looks like fetching the results takes most time:

-- 2019-05-29 08:03:53 [ftmlprod] [ftmlprod-ft] [console: ftmlprod-ft [console]] [session id: 385019918] [statement id: 1130173156] 
SELECT *
FROM staged.UsageData
-- ] --------------------------------------------------
-- 2019-05-29 08:03:53 [ftmlprod] [ftmlprod-ft] [console: ftmlprod-ft [console]] [session id: 385019918] [statement id: 1130173156] results received
-- 2019-05-29 08:04:51 [ftmlprod] [ftmlprod-ft] [session id: 385019918] disconnected
-- 2019-05-29 08:04:51 [ftmlprod] [ftmlprod-ft] [console: ftmlprod-ft [console]] [session id: 385019918] [statement id: 1130173156] finished - execution time: 47 ms, fetching time: 58 s 164 ms, total result sets count: 1
-- 2019-05-29 08:04:51 [ftmlprod] [ftmlprod-ft] [console: ftmlprod-ft [console]] error - [08S01] Socket closed\njava.net.SocketException: Socket closed
-- 2019-05-29 08:04:59 [ftmlprod] [ftmlprod-ft] [session id: 385019919] connected
-- [ --------------------------------------------------
-- 2019-05-29 08:04:59 [ftmlprod] [ftmlprod-ft] [unknown] [session id: 385019919] [statement id: 1130173157] 
SELECT 'keep alive'

notice the fetching time: 58 s 164 ms. It also looks like the connection has been disconnected at some point then re-connected. Do you have stable connection? Any VPNs you use?

try also switching PyCharm to use 1.8 java runtime: you can point IDE to the bundled 1.8 runtime in previous PyCharm distribution.

8forty

创建于 2019年05月29日 18:37

Thanks for taking a look.

The connection is stable and yes there's a VPN but I've been using it for over a year with 2018.3.4 with no issues. The closed connection you see in the log is because I cancelled the query rather than let it run -- it takes over 20 minutes to finish with 2019.1.2, but if I exit and immediately run 2018.3.4 the exact same query finishes (showing the first 500 rows) in a couple of seconds.

I tried changing the runtime, same results.

It's a big table (29M rows) so it appears that the real issue here is that 2019.1.2 reads the ENTIRE table before finishing, while 2018.3.4 only reads only the first 500 rows for this query then finishes. Is there maybe a new configuration setting that's causing 2019.1.2 to do that?

Andrey Dernov

创建于 2019年05月30日 09:04

What options do you have under Settings (Preferences on macOS) | Database | 'Data Views'? Could you check that you have the same options in both versions.