Focal Point
Stored Procedure runs slow when we call from webfocus

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4001012252

October 16, 2007, 04:06 PM
Kamesh
Stored Procedure runs slow when we call from webfocus
When I try to run a stored procedure from webfocus, it runs slow but when I run the stored procedure directly in sql query analyzer, it runs quick.

Anybody faced this issue or any suggestions about how to handle this issue.


WFConsultant

WF 8105M on Win7/Tomcat
October 16, 2007, 04:08 PM
dballest
Can you post your code?


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
October 16, 2007, 04:42 PM
Kamesh
SQL SQLMSS SET SERVER CONNECTIONNAME
SQL SQLMSS

EX SPNAME PARAMETERS ;

TABLE FILE SQLOUT
PRINT *
END
-EXIT


WFConsultant

WF 8105M on Win7/Tomcat
October 16, 2007, 09:17 PM
FortuneCookie
What happens when you replace

TABLE FILE SQLOUT

with

TABLEF FILE SQLOUT
October 17, 2007, 09:15 AM
Kamesh
I tried that too. No Luck.


WFConsultant

WF 8105M on Win7/Tomcat
October 17, 2007, 09:28 AM
hammo1j
Kamesh

How many records in total does the storeed procedure retrieve?

wf will expect the entire result set before it shows its first screen whereas sql server might have optimised the procedure to return the first screen's worth of data quickly in query analyzer.


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
October 17, 2007, 09:45 AM
Kamesh
It retrieve only 1000 records and for test I am displaying only 10 records.


WFConsultant

WF 8105M on Win7/Tomcat
October 17, 2007, 10:20 AM
hammo1j
Assuming they are not of a large width, 1000 records does not sound likely to cause a performance problem.

What's the difference in response?


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