The thing is it did not fail before in version 1.0.2005 when used against the same database. It also did not allow selecting any of the public synonyms. Was the exception previously handled?

No, nothing in that code has changed in v2.0 actually... that's the strange part.

I tried the same thing on a freshly created Oracle database and it failed after 10 minutes with the same exception. The database had a few public synonyms (<100).

You only had that schema in the project? The synonyms aren't synonyms on tables in linked servers?

I had a default installtion of Oracle. It had only schemas installed by default and I did not use any linked servers.

Kazak1 wrote:

I had a default installtion of Oracle. It had only schemas installed by default and I did not use any linked servers.

Hmm. Well, I can't reproduce it here. So if you could mail us (support AT llblgen.com) a test DDL SQL script for oracle which creates the tables and synonyms to reproduce this it would be helpful. You're sure it hangs on the synonyms and not on the proc retrieval code for example?

It hangs on "Retrieving schema 'PUBLIC'" - "Retrieve all synonym table field definitions" task.

More interesting details:

I just installed an Oracle 10g (10.2.0.1) server on my local machine. I tried the same task against the same database using the client installed with the server installation. It worked

I uninstalled the server from my machine, reinstalled Oracle client (10.2.0.1), tried the same task, and... it did not work.

It would be very interesting to see the query the designer hangs on.

The query is on the first page of this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6614#36537

Nothing special. It's called for every synonym found.

I remember that. However, I could not identify what synonym causes the designer to hang.

I just found that it is possible to switch tracing on for the ODP.NET. So I did. This is the query the designer hangs on:

SELECT CC.OWNER, CC.TABLE_NAME, CC.COLUMN_NAME, CC.POSITION 
FROM ALL_CONS_COLUMNS CC, ALL_CONSTRAINTS C 
WHERE CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
AND CC.OWNER = C.OWNER 
AND C.CONSTRAINT_TYPE='P' 
AND EXISTS 
    (
    SELECT * 
      FROM ALL_SYNONYMS S 
      WHERE OWNER='PUBLIC' 
      AND CC.TABLE_NAME=S.TABLE_NAME 
      AND CC.OWNER = S.TABLE_OWNER
    ) 
ORDER BY CC.TABLE_NAME, CC.COLUMN_NAME

I set the timeout to 30 min. This query took 12 minutes to execute, but it did finish. So, I have to be more patient.

But 12 minutes is way too long...

That query is used to retrieve the PK fields for the synonyms. It will pull all pk fields for all synonyms in the schema into a datatable.

I have the feeling this is such a big pile of data, that it will reach the 100,000 rows or more. That amount of data in a datatable is incredibly slow.

The sad thing is that I can't do it differently: - an IN query with the synonyms found obviously runs into the limit on the # of parameters per query - per synonym a separate query is an option but this is slower for a smaller set of synonyms.

Could you please do the following for me: - download the SDK for v2 and load the OracleDBDriver10g into VS.NET 2005 - the query is located at line 968 in OracleSchemaRetriever.cs. Could you build a debug build of the driver (remove the strongname key from assemblyinfo.cs) and place the dll + pdb in the Oracle10g folder of LLBLGen Pro's Driver folder? - keep the source in vs.net 2005, run the llblgen pro designer and press cntrl-alt-P in vs.net and attach to the llblgen pro designer. - place a breakpoint at line 978, namely adapter.Fill(pkFields); - create a project on the schema which takes such a tremendous long time. When you hit the breakpoint, do a step OVER, so it will go into the Fill method. This will take a while I think, so get some coffee. - Once the routine returns, please check how many rows threre are in pkFields.

59 rows were filled into the pkFields table.

SELECT CC.OWNER, CC.TABLE_NAME, CC.COLUMN_NAME, CC.POSITION 
FROM ALL_CONS_COLUMNS CC, ALL_CONSTRAINTS C 
WHERE CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
AND CC.OWNER = C.OWNER AND C.CONSTRAINT_TYPE='P'

returns ~700 rows

SELECT * FROM ALL_SYNONYMS S WHERE OWNER='PUBLIC' 

returns ~10000 rows.

Hmm, so where is the delay? Did the Fill() call take 12 minutes? Or did that return rather quickly?

The Fill() took 12 minutes. Actually, it is the query takes that long to execute.

Hmm... I haven't found a way to speed it up though...