I have a simple query which when run thru webfocus is running for 13 min where as when run directly on the sql server 2005 is taking 30 seconds. Any ideas on how to resolve or trouble shoot this issue...?


SET SQLENGINE = SQLMSS
SQL SET SERVER CON02
SQL SQLMSS PREPARE SQLOUT FOR
SELECT
T1."AgentNumberID",
T1."AgentNumber",
T1."StatCode",
T1."EffectiveDate",
T1."TerminatedDate",
T1."AnnuityCommissionLevel",
T1."LifeCommissionLevel",
T1."FinalExpenseCommissionLevel",
T1."PoliciesInForce",
T1."StatusName",
T2."TransactionDateID",
T2."PaidProduction",
T2."SubmittedProduction",
T2."PaidCount",
T2."SubmittedCount"
FROM SrsDataMart.dbo.AgentNumberDimension T1,
SrsDataMart.dbo.PolicyFact T2
WHERE (T2."AgentNumberID" =
T1."AgentNumberID") AND (T1."AgentNumber" = 'CD018');
END

TABLE FILE SQLOUT
PRINT *
END

This message has been edited. Last edited by: Kerry,

What if you just leave out the 'PREPARE SQLOUT FOR'?
I seem to remember (but correct me if I'm wrongly remembering - anyone?) that the prepare sqlout results in mssql returning the data set record by record when asked for one. That would explain the difference in duration.

Both these fexes take the same amount of time to retrieve 40,000 rows - 3 secs.

pgm1

SET SQLENGINE=DB2

SQL
SELECT
*
FROM bslc.time_d T1
-*where T1.FISCAL_YEAR = '2009'
;
TABLE ON TABLE HOLD AS SQLOUT
END
-RUN

pgm2

SET SQLENGINE=DB2

SQL PREPARE SQLOUT FOR
SELECT
*
FROM bslc.time_d T1
-*where T1.FISCAL_YEAR = '2009'
;
END
-RUN

How many rows are you retrieving?

It takes 13 min with or without PREPARE SQLOUT FOR.

I am trying to retrive around 1800 rows of data. I am really not sure where to start trouble shooting this as SQL server returns results in 30 seconds...

I'd test a request with the simplest SQL, something like this:

SELECT
*
FROM schema.table
FETCH FIRST 1 ROWS ONLY
;

If this takes longer than a second, I'd check with the WebFOCUS administrators - there could be something wrong with the connections settings of the SQL Server adapter.

This message has been edited. Last edited by: Francis Mariani,

This was solved by putting
ENGINE SQLMSS SET CURSORS CLIENT

with help from ibi tech support.

Could you post the final code you ended up using? I have a similar problem with SQL Server.

quote:

ENGINE SQLMSS SET CURSORS CLIENT

Look at the documentation: Adapter Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS > Customizing the Microsoft SQL Server Environment > How to Specify the Cursor Type