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     Optimizing SQL Pass-Through with SQL Server 2000

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Optimizing SQL Pass-Through with SQL Server 2000
 Login/Join
 
Member
posted
I have a substantial difference in run time when I run a stored procedure in SQL Server 2000 (SQL Query Analyzer) and from WebFOCUS. Here's my focexec.

-* File CreateNATX3.fex
-TYPE CreateNATX3
-******************************************************************************
SQL SQLMSS
EXEC DevGPUD2008.dbo.mlm_CreateHoldNATX3
END
-******************************************************************************

Natively it runs in less than five seconds. It takes nearly a minute when run from WebFOCUS. I am not sure where to start to optimize this.
 
Posts: 22 | Location: Seattle, WA | Registered: March 29, 2007Report This Post
Virtuoso
posted Hide Post
I don't do pass thru, but, it would help to know what kind of SQL selection you are trying to convert to WebFOCUS. Or are you saying the pass thru takes longer when you do an SQL 'call' through WebFOCUS?

Also, please add the version you are using to your signature, Helps everyone know where you are coming from so to speak.

Good luck.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Member
posted Hide Post
The timing difference is just between the execution time of the stored procedure in WebFOCUS and SQL Server Query Analyzer.

I wonder if there is a priority setting that I need to make in SQL Server or EDASPROF. We are a small shop and I have to handle anything that has to do with databases - SQL Server and WebFOCUS.
 
Posts: 22 | Location: Seattle, WA | Registered: March 29, 2007Report This Post
Master
posted Hide Post
Mike

Run the report and ON TABLE HOLD to get a true comparison of performance.

If you PCHOLD FORMAT HTML then the minute may be the delivery and rendering of a huge HTML file.

John



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Expert
posted Hide Post
Mike,

If you are executing this via MRE, then, there is a big time difference.

What we do is in MRE create a fex with:
-MRNOEDIT -INCLUDE foldername/programname.fex
-EXIT

Then, on the reporting server, the INCLUDEd fex, which has within:

SQL SQLMSS SET SERVER serveraddress, ip, etc
SQL SQLMSS EX DevGPUD2008.dbo.mlm_CreateHoldNATX3 '&PARAMETER1', '&PARAMETER2', '&PARAMETER3', &PARAMETER4;

We do not place a -RUN here because, MS SQL will return a header record if there are really zero records.

Then we

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS WHATEVER
END
-*------------------------------------------------------------------------
-IF &LINES EQ 0 GOTO ERROR_MSG;
-*------------------------------------------------------------------------
?FF WHATEVER
-RUN

From this point, WebFOCUS takes over...

This process really speeds up data execution and retrieval.

Hope this helps and Have Fun...

Tom

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Member
posted Hide Post
The stored procedure creates a SQL Server table that is later read by the rest of my WF report. Originally I used this WF report to create the SQL Server table:
JOIN
CHAR_YEAR AND CHAR_TAGCODE IN REP_CHARACTERISTICS TO MULTIPLE
TRK_YEAR AND TRK_TAGCODE IN TRACKING AS Jtrk
END
JOIN
TRK_YEAR AND TRK_SYSTEM AND TRK_HYDROPHONE IN REP_CHARACTERISTICS TO
HYD_YEAR AND HYD_SYSTEM AND HYD_HYDROPHONENUMBER IN REP_HYDROPHONES AS Jhyd
END
TABLE FILE REP_CHARACTERISTICS
PRINT
NAT_PEAKLOCATION
Period AS 'PERIOD'
BY TRK_YEAR
BY CHAR_PROJECT
BY TRK_SYSTEM
BY TRK_TAGCODE
BY TRK_SOURCE
BY TRK_PEAKLOCATION
BY TRK_DETECTDATETIME
BY HYD_HYDROPHONENUMBER
BY HYD_HYDROPHONEORDER
BY HYD_ROUTE
BY HYD_ROUTE_ORDER
BY CHAR_SPECIES
WHERE TRK_SYSTEM NE 'TGLF'
WHERE CHAR_PROJECT EQ 'CPUD' OR 'GPUD';
WHERE CHAR_YEAR EQ '2008'
ON TABLE HOLD AS HOLDNAT FORMAT SQLMSS
END

Now it is just:
-******************************************************************************
SQL SQLMSS
EXEC DevGPUD2008.dbo.mlm_CreateHoldNATX3
END
-******************************************************************************

I found it ran considerably faster by just using a call to a stored procedure that creates the file in SQL.
It must have to do with the time it takes to handle the call to start the stored procedure. Initially I filtered the stored procedure to only create a few records, and the time difference between calling it from WF and running it natively with Query Analyzer was significant (5 seconds vs. 1 minute). Once I took the filter off and created all 23 million records, the time difference was still basically 1 minute - the execution time for the stored procedure was now nearly an hour though, so that difference was insignificant.

Thanks you all for your help.
 
Posts: 22 | Location: Seattle, WA | Registered: March 29, 2007Report This Post
Member
posted Hide Post
I wish I was using MRE and ReportCaster! I went from being an IBI Consultant using all the cool tools to using Power Reporter (the cheapest WF product you can buy) on my desktop. I update the tables in my data warehouse every night using focexecs and stored procedures triggered from SQL Server's job scheduler.

Here is a stored procedure that runs a batch file ... that runs a focexec:

SELECT @sBatch = 'D:\Program\Applications\ibi\apps\development_2008\CreateNATX3'
PRINT @sBatch
EXEC master.dbo.xp_cmdshell @sBatch

CreateNATX3.bat is:
C:\ibi\DevStudio76\srv76\wfs\bin\edastart -x "EX CreateNATX3"
 
Posts: 22 | Location: Seattle, WA | Registered: March 29, 2007Report This Post
Master
posted Hide Post
You sure you not getting wf managed join try these prior to your focexec.


ENGINE SQLMSS SET OPTIMIZATION ON
ENGINE SQLMSS SET OPTIFTHENELSE ON
ENGINE SQLMSS SET VARCHAR OFF

-* never use this setting it causes a 2 second delay after each SQL request
-* ENGINE SQLMSS SET NONBLOCK 2

Cant see it taking 55s to translate and prepare query from wf table statements.



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Member
posted Hide Post
ENGINE SQLMSS SET OPTIMIZATION ON
ENGINE SQLMSS SET OPTIFTHENELSE ON
ENGINE SQLMSS SET VARCHAR OFF

Had no effect. It still took a bit over a minute.
 
Posts: 22 | Location: Seattle, WA | Registered: March 29, 2007Report This Post
Master
posted Hide Post
Hi Mike

Try putting this in and posting the SQL generated from your wf code if you can.

-* File sqltrace.fex
-* check on includes translation
SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=STMTRACE/1/CLIENT
SET TRACEON=SQLAGGR/1/CLIENT
-*SET TRACEON=WHOPT/1/CLIENT
SET TRACEON=SQLTRANS/3/CLIENT
SET TRACEON=?
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF

Very mysterious!



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Member
posted Hide Post
-* File CreateNATX3.fex
-TYPE CreateNATX3
-******************************************************************************
SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=STMTRACE/1/CLIENT
SET TRACEON=SQLAGGR/1/CLIENT
-*SET TRACEON=WHOPT/1/CLIENT
SET TRACEON=SQLTRANS/3/CLIENT
SET TRACEON=?
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF
-*
SQL SQLMSS
EXEC DevGPUD2008.dbo.mlm_CreateHoldNATX3
END

Here's what I get:

CreateNATX3
Use SET TRACEON=//CLIENT to send traces to the client
Name Level Description Set Comp.ID
STMTRACE 1 SQL/MDX Generated Statement Trace Y AE
SQLTRANS 3 SQLTRANS Memory Dump Y BL
SQLAGGR 1 SQL Aggregation Trace Y BR
 
Posts: 22 | Location: Seattle, WA | Registered: March 29, 2007Report This Post
Master
posted Hide Post
Sorry Mike

I mean do this

quote:

SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=STMTRACE/1/CLIENT
SET TRACEON=SQLAGGR/1/CLIENT
-*SET TRACEON=WHOPT/1/CLIENT
SET TRACEON=SQLTRANS/3/CLIENT
SET TRACEON=?
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF
-*

JOIN
CHAR_YEAR AND CHAR_TAGCODE IN REP_CHARACTERISTICS TO MULTIPLE
TRK_YEAR AND TRK_TAGCODE IN TRACKING AS Jtrk
END
JOIN
TRK_YEAR AND TRK_SYSTEM AND TRK_HYDROPHONE IN REP_CHARACTERISTICS TO
HYD_YEAR AND HYD_SYSTEM AND HYD_HYDROPHONENUMBER IN REP_HYDROPHONES AS Jhyd
END
TABLE FILE REP_CHARACTERISTICS
PRINT
NAT_PEAKLOCATION
Period AS 'PERIOD'
BY TRK_YEAR
BY CHAR_PROJECT
BY TRK_SYSTEM
BY TRK_TAGCODE
BY TRK_SOURCE
BY TRK_PEAKLOCATION
BY TRK_DETECTDATETIME
BY HYD_HYDROPHONENUMBER
BY HYD_HYDROPHONEORDER
BY HYD_ROUTE
BY HYD_ROUTE_ORDER
BY CHAR_SPECIES
WHERE TRK_SYSTEM NE 'TGLF'
WHERE CHAR_PROJECT EQ 'CPUD' OR 'GPUD';
WHERE CHAR_YEAR EQ '2008'
ON TABLE HOLD AS HOLDNAT FORMAT SQLMSS
END



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Member
posted Hide Post
Sorry for the slow response. I was at an IBI User Group meeting this morning.

This is the trace of the focexec that best replicates the stored procedure I am running. I am not running any WebFOCUS code, but rather just calling a stored procedure.

SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=STMTRACE/1/CLIENT
SET TRACEON=SQLAGGR/1/CLIENT
-*SET TRACEON=WHOPT/1/CLIENT
SET TRACEON=SQLTRANS/3/CLIENT
SET TRACEON=?
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF
-*

JOIN
CHAR_YEAR AND CHAR_PROJECT AND CHAR_TAGCODE IN REP_CHARACTERISTICS TO MULTIPLE
TRK_YEAR AND TRK_PROJECT AND TRK_TAGCODE IN TRACKING AS Jtrk
END
JOIN
TRK_YEAR AND TRK_PROJECT AND TRK_SYSTEM AND TRK_HYDROPHONE IN REP_CHARACTERISTICS TO
HYD_YEAR AND HYD_PROJECT AND HYD_SYSTEM AND HYD_HYDROPHONENUMBER IN REP_HYDROPHONES AS Jhyd
END
TABLEF FILE REP_CHARACTERISTICS
PRINT
CHAR_SPECIES
BY TRK_YEAR
BY CHAR_PROJECT
BY TRK_SYSTEM
BY TRK_TAGCODE
BY TRK_SOURCE
BY TRK_PEAKLOCATION
BY TRK_DETECTDATETIME
BY HYD_HYDROPHONENUMBER
BY HYD_HYDROPHONEORDER
BY HYD_ROUTE
BY HYD_ROUTE_ORDER
WHERE TRK_SYSTEM NE 'TGLF'
WHERE CHAR_PROJECT EQ 'CPUD' OR 'GPUD';
WHERE CHAR_YEAR EQ '2008'
ON TABLE HOLD AS HOLDNAT FORMAT SQLMSS
END
 
Posts: 22 | Location: Seattle, WA | Registered: March 29, 2007Report This Post
Member
posted Hide Post
Whoops - my paste didn't work!

SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=STMTRACE/1/CLIENT
SET TRACEON=SQLAGGR/1/CLIENT
-*SET TRACEON=WHOPT/1/CLIENT
SET TRACEON=SQLTRANS/3/CLIENT
SET TRACEON=?
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF
-*

JOIN
CHAR_YEAR AND CHAR_PROJECT AND CHAR_TAGCODE IN REP_CHARACTERISTICS TO MULTIPLE
TRK_YEAR AND TRK_PROJECT AND TRK_TAGCODE IN TRACKING AS Jtrk
END
JOIN
TRK_YEAR AND TRK_PROJECT AND TRK_SYSTEM AND TRK_HYDROPHONE IN REP_CHARACTERISTICS TO
HYD_YEAR AND HYD_PROJECT AND HYD_SYSTEM AND HYD_HYDROPHONENUMBER IN REP_HYDROPHONES AS Jhyd
END
TABLEF FILE REP_CHARACTERISTICS
PRINT
CHAR_SPECIES
BY TRK_YEAR
BY CHAR_PROJECT
BY TRK_SYSTEM
BY TRK_TAGCODE
BY TRK_SOURCE
BY TRK_PEAKLOCATION
BY TRK_DETECTDATETIME
BY HYD_HYDROPHONENUMBER
BY HYD_HYDROPHONEORDER
BY HYD_ROUTE
BY HYD_ROUTE_ORDER
WHERE TRK_SYSTEM NE 'TGLF'
WHERE CHAR_PROJECT EQ 'CPUD' OR 'GPUD';
WHERE CHAR_YEAR EQ '2008'
ON TABLE HOLD AS HOLDNAT FORMAT SQLMSS
END

SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=STMTRACE/1/CLIENT
SET TRACEON=SQLAGGR/1/CLIENT
-*SET TRACEON=WHOPT/1/CLIENT
SET TRACEON=SQLTRANS/3/CLIENT
SET TRACEON=?
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF
-*

Use SET TRACEON=//CLIENT to send traces to the client
Name Level Description Set Comp.ID
STMTRACE 1 SQL/MDX Generated Statement Trace Y AE
SQLTRANS 3 SQLTRANS Memory Dump Y BL
SQLAGGR 1 SQL Aggregation Trace Y BR
13.41.59 BR (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
13.41.59 BR (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
13.41.59 AE SELECT T1."char_Year",T1."char_Project",T1."char_TagCode",
13.41.59 AE T1."char_Species",T2."trk_Year",T2."trk_Project",
13.41.59 AE T2."trk_System",T2."trk_Source",T2."trk_TagCode",
13.41.59 AE T2."trk_Hydrophone",T2."trk_DetectDateTime",
13.41.59 AE T2."trk_PeakLocation",T3."hyd_HydrophoneNumber",T3."hyd_Route",
13.41.59 AE T3."hyd_Route_Order",T3."hyd_HydrophoneOrder" FROM
13.41.59 AE DevGPUD2008.dbo.Rep_Characteristics T1,DevGPUD2008.dbo.Tracking
13.41.59 AE T2,DevGPUD2008.dbo.Rep_Hydrophones T3 WHERE (T2."trk_Year" =
13.41.59 AE T1."char_Year") AND (T2."trk_Project" = T1."char_Project") AND
13.41.59 AE (T2."trk_TagCode" = T1."char_TagCode") AND (T3."hyd_Year" =
13.41.59 AE T2."trk_Year") AND (T3."hyd_Project" = T2."trk_Project") AND
13.41.59 AE (T3."hyd_System" = T2."trk_System") AND
13.41.59 AE (T3."hyd_HydrophoneNumber" = T2."trk_Hydrophone") AND
13.41.59 AE (T1."char_Year" = '2008') AND (T1."char_Project" IN('CPUD',
13.41.59 AE 'GPUD')) AND (T2."trk_System" <> 'TGLF') ORDER BY T2."trk_Year",
13.41.59 AE T1."char_Project",T2."trk_System",T2."trk_TagCode",
13.41.59 AE T2."trk_Source",T2."trk_PeakLocation",T2."trk_DetectDateTime",
13.41.59 AE T3."hyd_HydrophoneNumber",T3."hyd_HydrophoneOrder",
13.41.59 AE T3."hyd_Route",T3."hyd_Route_Order";
...RETRIEVAL KILLED
13.41.59 AE CREATE TABLE HOLDNAT( "trk_Year" VARCHAR (4) NOT NULL ,
13.41.59 AE "char_Project" VARCHAR (10) NOT NULL ,"trk_System" VARCHAR (4)
13.41.59 AE NOT NULL ,"trk_TagCode" VARCHAR (7) NOT NULL ,"trk_Source"
13.41.59 AE VARCHAR (13) NOT NULL ,"trk_PeakLocation" INTEGER NOT NULL ,
13.41.59 AE "trk_DetectDateTime" DATETIME NOT NULL ,"hyd_HydrophoneNumber"
13.41.59 AE INTEGER NOT NULL ,"hyd_HydrophoneOrder" INTEGER NULL ,
13.41.59 AE "hyd_Route" VARCHAR (10) NULL ,"hyd_Route_Order" INTEGER NULL ,
13.41.59 AE "FOCLIST" INTEGER NOT NULL ,"char_Species" VARCHAR (2) NOT
13.41.59 AE NULL ) ;
(FOC1400) SQLCODE IS 2714 (HEX: 00000A9A) XOPEN: 42S01
: Microsoft OLE DB Provider for SQL Server: [42S01] There is already an ob
: ject named 'HOLDNAT' in the database.
(FOC1414) EXECUTE IMMEDIATE ERROR.
1
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
0
 
Posts: 22 | Location: Seattle, WA | Registered: March 29, 2007Report This Post
Master
posted Hide Post
Mike

wf is generating efficient code here. Unless the code in your sp is substantially different, I cannot see why there should be a time difference in execution on a small set.

A possibility may be that the sp is optimized for fast retrieval of the first n rows whereas the wf query is optimized for optimal performance on the whole query.

If you post the code in your sp I can take a look...



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Expert
posted Hide Post
quote:
CREATE TABLE HOLDNAT


Mike,

I see John is giving you excellent advice and recourse; just wanted to throw out a thought:

You may want to DROP this table from the database DevGPUD2008 first.

DROP TABLE HOLDNAT

This may speed things up instead of an overlay...

Hope this helps...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Member
posted Hide Post
This discussion has morphed away from my initial question which was that I called a stored procedure from WebFOCUS that took more than a minute longer to get a response back than running the same stored procedure directly in SQL Server Query Analyzer. I don't run any WebFOCUS code other than the following:
-******************************************************************************
SQL SQLMSS
EXEC DevGPUD2008.dbo.mlm_CreateHoldNATX3
END
-******************************************************************************

Once that one minute of handling is complete the stored procedure runs as quickly whether called from WebFOCUS or run directly in SQL Server. So a call that creates a 5000 record table takes 5 seconds in Query Analyzer and 1:05 minutes when called from WebFOCUS, while a 23 million record table creation (which is closer to the average) takes an hour in Query Analyzer and an hour and one minute when called from WebFOCUS.

I just wondered what WebFOCUS was doing during that minute and whether I had a setting wrong.
 
Posts: 22 | Location: Seattle, WA | Registered: March 29, 2007Report 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     Optimizing SQL Pass-Through with SQL Server 2000

Copyright © 1996-2020 Information Builders