Community
Participate
Working Groups
Build Identifier: 1.7.2.v20100914-1028 IDE is Frozen after SQL Query execution form SQL Scrapbook for many minutes. After it it executes queries one by one but very slow (cca 1 query per 1 second) Reproducible: Always Steps to Reproduce: 1. Define database Connections (postgres or mysql, possibly any other) 2. Open SQL Scrapbook 3. Insert SQL Script (attached) 4. Run execute all
Created attachment 183380 [details] SQL Script for issue replication
I don't have PostgresSQL, but I was able to run the script successfully on DB2 for LUW v9.7. (I tweaked the script slightly to replace "serial" with "integer not null generated always as identity".) Can you run the script from a command line? (That is, outside of DTP?)
I can run script from other SQL clients very fast as well. On Linux Eclipse is incredibly slow, on Windows little bit faster, but still terribly slow. Similar scripts to HSQL were performed quite fast. Next indicator is that CPU is highly overloaded during script processing.
OK, I installed a PostgreSQL server and I can reproduce the problem. It looks like the Execute SQL action in the SQL editor "thinks real hard" about the script before trying to execute it. On my machine it took about three minutes of almost 100% CPU before the first statement in the script was sent to the database. After that, the statements were executed quickly. I don't know what's going on here. I think it might be because the SQL editor or script execution engine doesn't have specific support for PostgreSQL, and it thinks the script is Sybase SQL Server. However even that doesn't explain the long delay. Some database connections have this problem, and others don't. The problem also aflicts MySQL (see bug 330626), but it doesn't occur with DB2 connections. Unfortunately all the Sybase developers who originally contributed this code are no longer active with Eclipse. So this will take some time to figure out and fix.
(In reply to comment #0) > Build Identifier: 1.7.2.v20100914-1028 > > IDE is Frozen after SQL Query execution form SQL Scrapbook for many minutes. > After it it executes queries one by one but very slow (cca 1 query per 1 > second) > > > Reproducible: Always > > Steps to Reproduce: > 1. Define database Connections (postgres or mysql, possibly any other) > 2. Open SQL Scrapbook > 3. Insert SQL Script (attached) > 4. Run execute all I have this problem with an embedded derby database on my Windows 7 64 bit machine. This is my script: http://leejjon.webs.com/create.sql If I execute the drop constraint part first, and then the rest of the script, it works. It crashes when I run the entire script together.
Created attachment 191590 [details] Reproduce ide freeze bug with derby sql script.
I get the same behavior on my machine. While jstack'ing the process, it's stuck for a great deal of time in regex pattern matching at o.e.d.sqltools.core.services.SQLService.isQuoted(SQLService.java:227). I'll investigate further.
It seems that this is a problem with the backtracking in the first regex. For example, if you modify the first regex from : Pattern pSingle = Pattern.compile("'[^']*('')*[^']*;+[^']*('')*[^']*'"); to using possessive quantifiers : Pattern pSingle = Pattern.compile("'[^']*+('')*+[^']*+;+[^']*+('')*+[^']*+'"); On my machine, it cuts down from 10 seconds per SQL line, to some milliseconds. I need to think a bit more about the regex, in the meantime, is the original author still around so we can know for sure which regex subparts we can change without altering the original behavior?
Created attachment 203892 [details] Patch A patch that fixes the behavior on my machine. Yet as this is a regex, this would be better reviewed by the original author, to guarantee idempotence. I believe so as the groups are mutually exclusive.
Created attachment 204567 [details] Patch in EGit default format Patch exported without checking Git format in EGit.
Hi, could a committer from sqltools review this? This should give a nice performance boost. Many thanks.
OK, I've gone ahead and checked in the fix. Here's the Git commit record: http://git.eclipse.org/c/datatools/org.eclipse.datatools.sqltools.git/commit/?id=7db8ba7f67b67913f01c44cc1977907318cbfa79 Please keep testing to see if any problems appear.
