I'm using SQL Server 2000.

I'm not a mssql expert, but I would create indexes containing your join fields and any fields you have a where clause upon, that's pretty much universal for any database.

I've tried using indexes as well and that doesn't help either. Either a whole bunch of errors occur or it doesn't help the speed.

I prefer the first answer =)

I can't understand how this topic can last so long =)

GGOFAnalyst:
1. Do you use only TABLE FILE or SQL SQLMSS ?
2. If you SQL SQLMSS - is your query runs fast from isqlw.exe and slow from WF?
3. If you TABLE FILE - have you tried to rewrite it in SQL SQLMSS? Have you tried to encapsulate it in MS stored proc?

4. In any case: WF works agains operational data or server designated for reporting?
5. In any case: which ENGINE SQLMSS in your edasprof.prf?
Have you tried ENGINE SQLMSS SET ISOLATION RU?

Anyway if you want help :
a) post structures of your tables
b) post synonyms
c) post queries you use : TABLE(F) FILE or SQL SQLMSS

Regards,
Alex

Addition:
If you TABLE FILE - run SQL Profiler and catch statements generated by report.

TABLEF is your friend! And it works every time!

One of the things that I do to speed up WebFocus reporting against SQL tables is to use TABLEF to retrieve the data, including any WHERE statements that SQL can understand to bring back fewer rows and eliminate any DEFINEs, EDIT's and anything that may turn optimization off. Keep the request simple, put the data in a hold file, then do all your fancy formating, defines, calculations, etc. against the HOLD file. If the fields your sorting BY are indexed on the SQL table, then use TABLE and let SQL do the sorting for you. It's very helpful to look at the trace and see the SQL is being generated. I'm assuming WebFocus against SQL tables is similar to Focus against DB2 tables. Someone please correct me if I'm wrong. It's possible your DBA may need to add an Index for you. Good Luck and hope this helps!

From this post;

https://forums.informationbuilders.com/eve/forums/a/tpc/...1057331/m/4551053152

Good Luck!
Carol

Carol, I agree with everything you said except,

"...then use TABLE and let SQL do the sorting for you."

One should still use TABLEF. Using TABLEF with BY phrases will pass the sorting to the database as long as you are sorting using data base fields. They don't have to be indexed. Using TABLE will cause WebFOCUS to sort already sorted data.

quote:

(FOC2519) THE JOINED SEGMENTS RESIDE IN MORE THAN ONE NODE OR SUBSYSTEM

This message from the SQL translator says it all. If you can solve that then the rest should be easy.

Ingas/Alex, I have already suggested using SQL trace and that's how GGOFAnalyst was able to capture the messages. As well, what is "ENGINE SQLMSS SET ISOLATION RU" and how is it going to help? Please give us a clue as to what that command does, it's not one that's used everyday.

Posting structures and synonyms of the tables will not help.

As far as I know, joining tables from two different "nodes or subsystems" will disable aggregation and RDBMS joins. The data from one subsystem will be downloaded to the temp area of the WebFOCUS server, then the data from the second subsystem will be downloaded to the temp area of the WebFOCUS server, then the two temp files are joined, verrrrrrrry inefficient.

In my opinion, the program should be broken into multiple steps. Extract the data from the first subsystem, aggregating the data by the required dimensions and creating a HOLD FOCUS DB. Extract the data from the second subsystem, aggregating the data by the required dimensions and creating a HOLD FOCUS DB. Joining the twp HOLD FOCUS DB's.

Sounds suspiciously like what WebFOCUS is doing automatically? Yes, but in method I suggest, you have control and generate efficient SQL.

Does anyone have a better idea?

Hi, Francis

quote:

as well, what is "ENGINE SQLMSS SET ISOLATION RU" and how is it going to help? Please give us a clue as to what that command does, it's not one that's used everyday.

RU - means TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

I know the purists will start to throw tomatoes on me, but I can tell that in many cases in MSSQL 2K/below - it's the only way to complete SQL statement.

(2005 is different. SNAPSHOT ISOLATION - maybe the best feature of Yukon)

When working against operational data - it also ensures that reports did not block writing processes.

quote:

In my opinion, the program should be broken into multiple steps. Extract the data from the first subsystem, aggregating the data by the required dimensions and creating a HOLD FOCUS DB. Extract the data from the second subsystem, aggregating the data by the required dimensions and creating a HOLD FOCUS DB. Joining the twp HOLD FOCUS DB's.

I agree with you that data consolidation is reasonable.
I do not have much experience with FOCUS/XFOCUS files with a lot of data in them.
(In fact, I can't say that I have much experience with WebFOCUS)
I've tried to put a table with 40K of rows and I do not like size of HOLD-file.
(But I must admit there was a lot of string columns)

I absolutely agree if you can pass the sort off on a real database field let SQL do the sorting. Thanks for the correction, jgelona. I often see in our company folks trying to sort on Defined fields. (and I cut/pasted my message from an old post).

ENGINE SQLMSS SET ISOLATION RU
this is a new one for me! interesting!

quote:

TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Still don't know what this means and how it will help code a program in WebFOCUS.

According to "iWay Adapter Administration for UNIX, Windows, OpenVMS, OS/400, OS/390, and z/OS - Version 5 Release 3.3" controlling "TRANSACTION ISOLATION LEVEL" is only available for DB2 and CA-IDMS.

According to "Adapter Administration for UNIX, Windows, OpenVMS, I5/OS, and z/OS - Version 7 Release 6" this is available for more adapters, including MS SQS Server, though it still doesn't explain what the command does.

According to Microsoft MSDN SQL Server Developer Center this controls which rows will be read - "Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server" - I think leaving this at the default is fine, I don't see how changing this will improve the response of a read request.

For DB2 it used to be (a long while since I used DB2) CS for Cursor Stability and RR for Repeatable Read.

The best place to find out what these isolation levels mean is the RDBMS' manuals as WF is only allowing utilisation of these commands and not providing the implimentation.

T

Isolation level of RU for SQL Server is similar to UR for DB2. When creating a request with this level of isolation, SQL server will perform a "dirty read" of the data which can save time as the system will not be issuing or checking locks.

The downside of using this, is that you will also get any uncommited records that may exist.

The error message you are getting FOC2519 means that the two files being joined are from different relational databases or nodes of a system, so that there is no one system that can join them except WebFocus. I would check the two ACCESS files (.ACX) to see if the information is correct. Also, the SUFFIX parameter in the two MFD files.

An alternate strategy in all cases of slow JOIN is to use the MATCH process. This is now controlled by the Developer Studio GUI dialogue for MATCH. (or read the Doc)

About READ UNCOMMITTED ("dirty read"):

Gizmo is right : no issuing locks, no checking locks.
Downside - reading non-committed data.

SQL2K/below is issuing locks on read. And this is a big problem.

Reporting against production server can be dangerous - look sometimes at :

SELECT * FROM master.dbo.sysprocesses WHERE blocked NOT IN (0, blocked)

("sometimes" means: low CPU utilization, low IO utilization, but nothing works)

And in analytical report with BY YEAR BY MONTH - it does not differ much whether it's with COMMITTED or UNCOMMITTED data.

I make RU as default mode for SQLMSS adapter.
If committed data is really needed - I redefine it in report.

(SQLServer 2005 - much better in this topic)

Regards.

How do you overwrite the adapter's commitment level in the report.

SQL SQLMSS SET ISOLATION RU;

gives me

(FOC1721) WARNING: COULD NOT SET ISOLATION LEVEL FOR SQLMSS

Did you perform any other SQL commands before this command? The error message details:

(FOC1721) WARNING: COULD NOT SET ISOLATION LEVEL FOR %1%2
An active logical unit of work exists for given data source, therefore
could not set isolation level. Please make sure that all LUWs are
closed on all active connections and retry to set isolation level.