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.
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.
April 01, 2008, 01:30 PM
Leah
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
April 01, 2008, 02:13 PM
Michael Meagher
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.
April 02, 2008, 08:00 AM
hammo1j
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
April 02, 2008, 08:40 AM
Tom Flynn
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...
TomThis 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.
April 02, 2008, 12:32 PM
Michael Meagher
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:
CreateNATX3.bat is: C:\ibi\DevStudio76\srv76\wfs\bin\edastart -x "EX CreateNATX3"
April 02, 2008, 01:48 PM
hammo1j
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
April 02, 2008, 02:23 PM
Michael Meagher
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.
April 02, 2008, 02:45 PM
hammo1j
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
April 02, 2008, 04:11 PM
Michael Meagher
-* 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
April 03, 2008, 04:00 AM
hammo1j
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
April 03, 2008, 04:44 PM
Michael Meagher
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
April 03, 2008, 04:46 PM
Michael Meagher
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
April 04, 2008, 03:59 AM
hammo1j
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
April 04, 2008, 06:01 AM
Tom Flynn
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.
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.