Bug 330568

Comment 1 Jiri Peterka 2010-11-18 09:13:12 EST

Created attachment 183380 [details]
SQL Script for issue replication

Comment 2 Brian Payton 2011-02-19 03:24:12 EST

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?)

Comment 3 Jiri Peterka 2011-02-19 03:35:46 EST

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.

Comment 4 Brian Payton 2011-02-23 19:47:48 EST

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.

Comment 5 Leon Liefting 2011-03-20 19:21:59 EDT

(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.

Comment 6 Leon Liefting 2011-03-20 19:37:16 EDT

Created attachment 191590 [details]
Reproduce ide freeze bug with derby sql script.

Comment 7 Pierre Queinnec 2011-09-11 14:59:03 EDT

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.

Comment 8 Pierre Queinnec 2011-09-11 18:03:42 EDT

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?

Comment 9 Pierre Queinnec 2011-09-23 08:29:07 EDT

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.

Comment 10 Pierre Queinnec 2011-10-05 03:45:05 EDT

Created attachment 204567 [details]
Patch in EGit default format

Patch exported without checking Git format in EGit.

Comment 11 Pierre Queinnec 2012-05-18 13:37:54 EDT

Hi, could a committer from sqltools review this? This should give a nice performance boost. Many thanks.

Comment 12 Brian Payton 2012-05-18 17:56:08 EDT

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.