Hi,

I have a SAP BW request which joins with an SQL Server table. When I run this request on my Webfocus DEV environment which we setup to point to our production data, the request runs without a glitch and the execution time is acceptable at 30 minutes. But when we run the same report on the WebFocus production environment, the same report will run for many hours killing the SQL Server machine and when the job ends, it doesn't return any valid data.

I'm thinking it might be a parameter or a driver but I looked and I can't seem to find anything that would explain this difference in behaviour.

Is there anyone who has had this problem before or has an idea what I can try?

Thanks.

Dave,

You are doing a hetergeneous join (joining two different kinds of data sources together) and that is never recommended.

Your dev environment probably has a lot fewer rows in either source and that is why you were getting away with doing it there.

There are lots of posts on the forum about how to do what you want to do, but briefly, the recommended method is to extract the data from each data source separately, store the target in an indexed format focus hold file and then join them together yourself.

Essentially, that is what WebFOCUS is doing under the covers anyway which you would have discovered if you had traced your request. However, if you do what I suggested, you have more control over the process.

Trace commands:

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON
SET XRETRIEVAL=OFF  

The XRETRIEVAL command set to off just parses your request without getting any data. Check out your generated SQL from the way your program is now and you will see 2 select statements.