Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQLORA prepared statement slowness

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SQLORA prepared statement slowness
 Login/Join
 
Wep5622
Virtuoso
posted
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,


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
MattC
Guru
posted Hide Post
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.


WebFOCUS 8.1.05
 
Posts: 496 | Registered: January 04, 2008Report This Post
Wep5622
Virtuoso
posted Hide Post
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=...


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
MattC
Guru
posted Hide Post
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.


WebFOCUS 8.1.05
 
Posts: 496 | Registered: January 04, 2008Report This Post
Wep5622
Virtuoso
posted Hide Post
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.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
MattC
Guru
posted Hide Post
Excellent! I couldn't remember if it did make a performance difference. We must have chosen this way becuase it looked cleaner.


WebFOCUS 8.1.05
 
Posts: 496 | Registered: January 04, 2008Report This Post
M. Meagher
Member
posted Hide Post
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.


Developer Studio 7.7.05
Application Studio 8.0.09
Windows 7
All Formats
 
Posts: 16 | Location: Seattle, WA | Registered: November 29, 2012Report This Post
Francis Mariani
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQLORA prepared statement slowness

Copyright © 1996-2020 Information Builders