I have chased this issue for several years. There is a significant difference in performance between running a WebFOCUS report against SQL Server and a stored procedure within SQL Server. I wrote a generic report in WebFOCUS using one of my large SQL Server tables. I then ran a trace to see what SQL code was being generated, captured it, and saved it as a stored procedure. The examples are shown below. The WebFOCUS report takes about 2.5 times longer to run than the stored procedure. SQL Passthru of the SQL code runs as slow as WebFOCUS. What I found was that the WebFOCUS report pegs one of the SQL Server box's CPUs, with no other CPU being used at all. Overall, the CPU utilization is 13% - one eighth of the potential CPU capacity. The stored procedure uses all eight processors. Its CPU utilization is closer to 40%. WebFOCUS and SQL Server are on different computers. I am the SQL Server administrator because I am essentially the sole Reporting/Database person at the company.

Has anyone else experienced this?

WebFOCUS

 
TABLE FILE TRACKINGTEMP_2010
SUM
     CNT.TRK_TRACKINGTYPE AS 'COUNT,TRK_TRACKINGTYPE'
BY TRK_YEAR
BY TRK_PROJECT
BY TRK_SYSTEM
BY TRK_TAGCODE
END

SQL code generated by WebFOCUS

SELECT 
T1.trk_Year,
T1.trk_Project,
T1.trk_System,
T1.trk_TagCode, 
COUNT(T1.trk_TrackingType) 
FROM GPUD2010.dbo.TrackingTemp T1 

GROUP BY 
T1.trk_Year,
T1.trk_Project,
T1.trk_System,
T1.trk_TagCode 

ORDER BY
T1.trk_Year,
T1.trk_Project,
T1.trk_System,
T1.trk_TagCode;

This message has been edited. Last edited by: M Meagher,

You've a very good job summarizing the issue. I don't think there's anything you can do in the program code. I would open a case with Information Builders Tech Support.

I will give the forum a couple of days, then open a case. Part of my reasoning is to suggest to other users they also might want to run a similar comparison. It takes a hefty pull on the data to get valid results which is why we can't use CAR. I wanted as plain vanilla a report as possible that still used a lot of CPU.

Well since you put the exact same SQL that WF sent to the server into the Stored Procedure, it's not a case of the SQL generated but maybe something about the request environment. What response do you get when you use the SQL in a vanilla interactive SQL tool (not WF).

That isn't a tool I have. I will see what is available on the net and try your suggestion.

I tried it with TOAD and all eight processors were used.

I took another look through Focal Point and found a posting from August 2009 with a similar problem. All it took was adding

 ENGINE SQLMSS SET CURSORS CLIENT 

All eight processor kicked in. I had even commented on the 2009 posting.

M Meagher,

Thank you for publishing this issue.

Your observations are very relevant and intriquing, as most of us use Relational Database engines to store data. I too had forgotten the cursor origins of the WebFOCUS data adapters. The SQL generated by WebFOCUS in your case is correct and efficient based on the case example.

There is an issue with client side cursors, as found by a colleague:
"Client cursors normally show better performance. However, MS SQL Server cannot fetch concurrently from several client cursors. It limits the use of client cursors to applications where it is known that the number of open cursors and/or executed statements will be not more than one at any moment of time."

Three other possibilities exist:
1. Investigate SQL Server DBA settings for Server side Cursors, the CPU utilization of the cursor may be configurable. I have no help on this issue, but suggest searching SQL Server, Books Online, and/or Google. My initial research suggests Microsoft strongly discourages Server Side Cursors.
http://www.sql-server-performa...tips/cursors_p1.aspx

2. Issue a command in this long running fex to execute as an uncommitted read. Since the WebFOCUS SQL Server data adapter is architected on cursors, influence the cursor behavior for speed. The fastest cursors are read only cursors, or "read uncommitted" cursors. Issuing a SQL Server command as uncommitted read stops the engine from normal record lock actions inherent to normal cursor behavior in the engine. This technique can only be used in read only operations. Details are well beyond the scope of this note. The WebFOCUS command is as follows:
ENGINE SQLMSS SET ISOLATION RU
I doubt this measure will fully close the performance gap.

3. Execute with WebFOCUS via SQL passthru. SQL passthru sends the SQL request to the server for execution which eliminates the data adapter cursor limitation, so WebFOCUS retrieval times are inline with TOAD or other direct execution methods. SQL passthru also allows virtually any supported DML select syntax supported by the RDBMS engine, which is very convenient when attempting complex retrieval statements.

SQL passthru consists of a set engine command, a "wrapper" for the SQL statement, then continuation of your FEX processing: A limited example is as follows:

ENGINE SQLMSS SET DEFAULT_CONNECTION data_adapter_name

SQL SQLMSS
any_valid_sql_statement_cut_and_pasted_from_TOAD
;
TABLE ON TABLE HOLD
END
-RUN

TABLE FILE HOLD
PRINT *
END
-RUN

Please change the lower case items to values relevant to your environment. The wrapper consists of the "SQL SQLMSS" and the semi-colon. Please also note the "TABLE ON TABLE HOLD" syntax difference for placing the data in a normal WebFOCUS hold file.

I believe the very best performance without changing database objects in a complex relational world is gained through the use of SQL passthru.


Respectfully,

Fascinating reading!

Thanks for the help.

Some of the story is missing because I opened a case with IBI and I didn't keep this thread updated.

Interestingly, running SQL passthru from within WebFOCUS also only used one processor. I had to add 'ENGINE SQLMSS SET CURSORS CLIENT' to get SQL passthru to use all eight processors. The same problem occurred when I used my development environment - Developer Studio 7.7.01, SQL Server 2008 R2 running on Windows7-64 bit.

Most of my WebFOCUS reporting is against a reporting database where no updates are required. I dug into the cursor issue and am still confused about the implications of "Client cursors normally show better performance. However, MS SQL Server cannot fetch concurrently from several client cursors. It limits the use of client cursors to applications where it is known that the number of open cursors and/or executed statements will be not more than one at any moment of time."

I think I am OK with just using 'CLIENT' for the cursors. Most of what I read on cursors was confusing and I believe not relevant to our use. Trying to get information from the SQL Server forums is bordering on futile because they can't understand why I don't write everything in stored procedures.

Once again, thank you for your help and interest.

M Meagher,

Thank you for keeping this thread alive, your keen observations make us all better service providers.

I have not noticed this issue in the past, while consulting with clients using Oracle, SQL Server, DB2 or UDB back ends, nor have I noticed significant differences in performance between a SQL query tool and WF SQL passthru at these accounts. It doesn't mean a performance difference didn't exist, just that we didn't notice. My last client utilized UDB on Windows, exclusively using SQL passthru for development and testing, then pasting successful SQL code into WF for incorporation into a FEX. Our construction included heavy metering by FEX step, which would have clearly identified performance differences between AQT (a SQL query tool) and WebFOCUS SQL passthru.

I wonder if this condition is specific to SQL Server or SQL Server 2008 data adapter?

To me the root question, "Is why is SQL Server limiting the query execution to one processor when using WebFOCUS?" Is this behavior limited to server side cursors?

All of my attempts to find SQL Server server side cursor configuration adjustments have been unsuccessful.

I too am now at an account with SQL Server 2008R2 x64 with 8 processors, so I will also attempt to find a definitive answer to this insidious issue.

Thanks again and kindest Regards,

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