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.

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.

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.

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

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,

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.

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"

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.

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.

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!

-* 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

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

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

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

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

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

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.