I have a report with several SQL SQLORA PREPARE statements. For some reason each query takes almost exactly as long every time; about 2.016s for each query.

Initial preparing the statements is fast: 141ms for all 4 queries. I do that at the top half of the procedure. It's the querying of these prepared statements in the bottom half of my procedure that is slow.

Seeing the very constant pattern in the timings, it doesn't seem that it's the actual query that we're waiting for.
Indeed, if I put the same queries directly in an SQL script in "Oracle SQL Developer", with the parameter values substituted (Oracle SQL does not support prepared statements apparently), the whole SQL script (all 4 queries) finishes in under 0.5s!

My WebFOCUS procedure takes over 10s total, which is not really desirable and clearly unnecessary seeing the actual query times.

Does anybody have any idea what's going on here or what we could do about this?

This message has been edited. Last edited by: Wep5622,

Can you share part of your program where your SQL is started and how it is ended?

Another possible idea is your fetch size of your adapter.

I have experianced slowness in SQL performance in Webfocus where SQL Developer is considerably less. I haven't really figured it out, but my assumption is that it's a persistent connection to Oracle from the Agent and refreshing the agents helps pickup the better plan on the database.

Well, if I run this code, you can already see it happen:

-SET &&LOGGING = 1;
-SET &LOGFILE = &APPROOT || '\wep5622\test.log';
-INCLUDE GEN00201_LogDef

-* Substitute below value for an applicable Oracle DB
-SET &DB = Oracle10g;

ENGINE SQLORA SET DEFAULT_CONNECTION &DB

-REPEAT :LOOP0 FOR &I FROM 1 TO 5;
SQL SQLORA PREPARE SQL_Q&I FOR
SELECT 1 AS N FROM dual;
END
-RUN

LOG LINE=Prepare &I
-RUN
-:LOOP0

-REPEAT :LOOP1 FOR &I FROM 1 TO 5;
TABLE FILE SQL_Q&I
PRINT N
END
-RUN

LOG LINE=Execute &I
-RUN
-:LOOP1

LOG LINE=enough

The log file then contains:

2013/09/27 15:17:18.956 : -- Log started ------ _ADHOCRQ ------
2013/09/27 15:17:18.988 : Prepare 1
2013/09/27 15:17:18.988 : Prepare 2
2013/09/27 15:17:18.988 : Prepare 3
2013/09/27 15:17:19.003 : Prepare 4
2013/09/27 15:17:19.003 : Prepare 5
2013/09/27 15:17:21.050 : Execute 1
2013/09/27 15:17:23.175 : Execute 2
2013/09/27 15:17:25.878 : Execute 3
2013/09/27 15:17:27.878 : Execute 4
2013/09/27 15:17:29.878 : Execute 5
2013/09/27 15:17:29.878 : enough

As you can see, prepare is quick, but executing each query takes 2s every time.

Fetch size is set to 100 here, everything else about the adapter appears to be pretty much on default settings.

P.S. The LOG command is a procedure we EX that writes the LINE parameter and a timestamp to the specified log file. We created an alias for that with the LET command, which is why we can write LOG LINE=...

Can you try this instead

SET SQLENGINE=SQLORA
ENGINE SQLORA SET DEFAULT_CONNECTION &DB

SQL
SELECT 1 AS N FROM dual
;
TABLE
ON TABLE HOLD AS BLAHBLAGH
END
-RUN
-*

I am curios if the SQL Prepare is slowing it down. We went through this exercise years ago with using SQL PREPARE and just using SQL.


We opted to use SQL instead of SQL PREPARE.

Also depending on how much data you are pulling back, I would bump up fetch size to maybe 3000.

Oh, I didn't know that you could run SQL queries directly like that in WebFOCUS! Learned something new then.

Unfortunately, it doesn't help with the timing issues; the direct queries also take 2s each. Using direct queries is even more like running those queries in a batch in SQL Developer, so you'd expect the query durations to be somewhat comparable...

I noticed that after a direct SQL + TABLE request, &LINES = 0. What's with that? PASSRECS is on for our adapter, so that's not it.

I saw no difference in behaviour between FETCHSIZE=100 (the default) or FETCHSIZE=3000. Nevertheless, 100 is probably a bit tight for at least some of our reports. I found in our edasprof that the FETCHSIZE for MSSQL is set to as much as 10000.

I set it to 1000 in edasprof and that seems to have solved the issue! So, thanks!
I'm starting to think that the setting just didn't catch on when I edited it in the adapter configuration screen.

Excellent! I couldn't remember if it did make a performance difference. We must have chosen this way becuase it looked cleaner.

The fetch size made a dramatic difference when I was having timing issues running SQL pass-through against MS SQL Server.

We have the fetch size set to 5000 for Oracle and 4000 for SQL Server.

I'm very interested in this subject.

It's strange that the Adapter Administration manual states, for Oracle

quote:

A value higher than 100 is not recommended because the increased efficiency it would provide is generally negligible.

We have it set to 3000.