I have three data sources which are connected to a sql server database. These tables contain a boat load of data. When I go to query for data, I mostly receive an Html type of time out error. Is there any way to speed up data process time through Web Focus. I have 5.2.3. Thanks for any assistance.

Joe

Joe

Performance enhancement is such a wide subject that the only way for us to tell is for you to post your query .FEX together with the .MAS es being queried.

Regards

John

Here's the fex for one of the queries:
-* File cvtappevent.fex



JOIN
APP_EVENTS.APPLICATION IN APP_EVENTS TO
BUSSEG01.APPCRYPTIC IN BUSSEG01 AS J0
End



TABLE FILE APP_EVENTS
SUM
'CNT.APP_EVENTS.BUSSEG1/I7' AS 'Total Calls'

BY
'APP_EVENTS.BUSSEGA' NOPRINT
BY
'APP_EVENTS.BUSSEG1'
-*WHERE event_partkey EQ 2007061800
WHERE event_partkey GE 2007060100 AND event_partkey LE 2007061800
-*WHERE APP_EVENTS.APPLICATION EQ '1st_Horizon_2'
WHERE (machine_id EQ 'PPNTVA08') OR (machine_id EQ 'PPNTVA17') OR (machine_id EQ 'PPNTVA07') OR (machine_id EQ 'PPNTVA16') OR (machine_id EQ 'PPNTVA04') OR (machine_id EQ 'PPNTVA13') OR (machine_id EQ 'PPNTVA05') OR (machine_id EQ 'PPNTVA12') OR (machine_id EQ 'PPNTVA14') OR (machine_id EQ 'PPNTVA03') OR (machine_id EQ 'PPNTVA19') OR (machine_id EQ 'PPNTVA06') OR (machine_id EQ 'PPNTVA20') OR (machine_id EQ 'PPNTVA15')
WHERE event_action EQ 'CHAIN'


ON TABLE PCHOLD FORMAT EXCEL
END

.mas's that go with are:
1. app_events
FILE=app_events, SUFFIX=SQLMSS
SEGNAME=APP_EVENTS, SEGTYPE=S0, $
FIELD=INSTANCE_ID, ALIAS=instance_id, USAGE=I11, ACTUAL=I4, $
FIELD=MACHINE_ID, ALIAS=machine_id, USAGE=A32, ACTUAL=A32, $
FIELD=EVENT_ORDER, ALIAS=event_order, USAGE=I11, ACTUAL=I4, $
FIELD=EVENT_PARTKEY, ALIAS=event_partkey, USAGE=I11, ACTUAL=I4, $
FIELD=APPLICATION, ALIAS=application, USAGE=A60, ACTUAL=A60, MISSING=ON, $
FIELD=EVENT_DATE, ALIAS=event_date, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $
FIELD=EVENT_TYPE, ALIAS=event_type, USAGE=A24, ACTUAL=A24, MISSING=ON, $
FIELD=EVENT_NAME, ALIAS=event_name, USAGE=A64, ACTUAL=A64, MISSING=ON, $
FIELD=EVENT_ACTION, ALIAS=event_action, USAGE=A12, ACTUAL=A12, MISSING=ON, $
FIELD=EVENT_RESULT, ALIAS=event_result, USAGE=A64, ACTUAL=A64, MISSING=ON, $
FIELD=RESULT_VALUE, ALIAS=result_value, USAGE=TX50, ACTUAL=TX, MISSING=ON, $
FIELD=SPEECH_CLASS, ALIAS=speech_class, USAGE=A32, ACTUAL=A32, MISSING=ON, $
FIELD=TIMEOUT, ALIAS=timeout, USAGE=A1, ACTUAL=A1, MISSING=ON, $
FIELD=EVENT_DATA, ALIAS=event_data, USAGE=TX50, ACTUAL=TX, MISSING=ON, $
FIELD=IRAPT_SEQ, ALIAS=irapt_seq, USAGE=P20, ACTUAL=P10, $
FIELD=TRANSACTION_DATE, ALIAS=transaction_date, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $
DEFINE BUSSEGA/I1 = DECODE APPLICATION(Various defines);$
DEFINE BUSSEG/I1 = IF BUSSEGA NE 9 THEN BUSSEGA ELSE 10;$
DEFINE BUSSEG1/A20 = IF BUSSEG EQ 1 THEN 'Markets' ELSE IF BUSSEG EQ 2 THEN 'CCM' ELSE IF BUSSEG EQ 3 THEN 'Business Bank' ELSE IF BUSSEG EQ 4 THEN 'Specialty' ELSE IF BUSSEG EQ 5 THEN 'Market Spanish' ELSE IF BUSSEG EQ 6 THEN 'Other' ELSE IF BUSSEG EQ 7 THEN '900 Verif.' ELSE IF BUSSEG EQ 8 THEN 'BB Spanish' ELSE 'OTHER';$

1. busseg01:
FILE=busseg01, SUFFIX=SQLMSS
SEGNAME=BUSSEG01, SEGTYPE=S0, $
FIELD=APPCRYPTIC, ALIAS=APPCRYPTIC, USAGE=A50, ACTUAL=A50, MISSING=ON, $
FIELD=BUSSEG1, ALIAS=BUSSEG1, USAGE=A14, ACTUAL=A14, MISSING=ON, $


Sometimes after 4 to 5 min I get some data for only one days worth, but never for multiple days.

Thanks for your help!

I'd probably go down the path of determining whether or not aggregation was done and what the SQL looks like that WebFOCUS generated.

Pay attention to the where conditions, was it passed correctly

You may also want to check your data to make sure that you are joining a smaller table to a larger table.

Last but not least if the SQL is optimized is SQL Server chugging along at 100% CPU, if so the problem could be with the system that SQL Server is on.

What took my attention is that you are joining two fields (APPLICATION and APPCRYPTIC which are both long and do not have the same picture (A60 and A50) and also are "missing=on".

I wonder if this would ever work without a problem...
Is it an outerjoin or an inner join?

Are there many records in the busseg01 table?

I also see you have a defined field in the master that has the same name but an other picture as in the other master. Rather tricky....why is this?

It looks as if the joined table does in fact the same as the defines.
So try to build the report without the join and see if that works.

Due to the fact that you are sorting BY a DEFINE field as well as trying to CNT a DEFINE field, most likely WebFOCUS is pulling all the data over and then handling the aggregation itself. This is because complicated DEFINEs will not translate into SQL. I would also try using the IN LIST selection operator for the WHERE clasuse on MACHINE_ID. It also appears that you are not referencing any fields in the JOINed file. Am I missing something? You may want to eliminate the JOIN if you are not using any fields in the joined file.

quote:

SUM
'CNT.APP_EVENTS.BUSSEG1/I7' AS 'Total Calls'

BY
'APP_EVENTS.BUSSEGA' NOPRINT
BY
'APP_EVENTS.BUSSEG1'

this basic part is interesting....

you say
Count X
By A
By X

so if this was the CAR database

DEFINE FILE CAR
CONTINENT/A10=DECODE COUNTRY('ENGLAND' 'EUROPE' 'ITALY' 'EUROPE' ELSE 'OTHER');
END


TABLE FILE CAR
COUNT COUNTRY
BY CONTINENT
BY COUNTRY
END

What will be the result.....

Try this first

ENGINE SQLMSS SET OPTIFTHENELSE ON


If that does not work This will show the SQL generated - Please can you post together with counts on the tables involved.

-* File sqltrace.fex
-* check on includes translation
SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=STMTRACE/1/CLIENT
SET TRACEON=SQLAGGR/1/CLIENT
-*SET TRACEON=WHOPT/1/CLIENT
SET TRACEON=SQLTRANS/3/CLIENT
SET TRACEON=?
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF

Thanks,

I've never had any formal FOCUS training. I have to rebuild pre-existing reports due to a new report server that include a complete overall of the data table layout. I created this specific report off of studio developer 5.2 or something like that.

I'll look into these suggestions and get back to you with the results. Thanks again for all of your comments.

Also check to see if the fields you are joining to are indexed. If they aren't, there's a performance hit. Are the fields you reference in the WHERE statements indexed? Are you referencing BUSSEG1 in the first or second table? Try using the tablename.fieldname format to clarify. Also, in many cases doing most of your logic on defined fields will just kill any efficiencies you would otherwise get from the DBMS. Getting the SQL statement as Frank suggest will probably give you the best idea of what the problem is.

One more suggestion, this link may be helpful.

https://forums.informationbuilders.com/eve/forums/a/tpc/...901067331#1901067331

Good Luck! Carol

Another thought:

One you have captured the SQL try running as native SQL against the server in SQLServer. See what happens. I have seen things that where SQL part ran in seconds, but the data transfer and formatting by WebFocus took minutes.

My preference for any SQL data source is extract the data into a hold file. I apply all the possible data selection and summarization.

I then apply any defines to the hold file. This reduces the change of the WebFocus trying to retrieve and process the entire SQL table.

Thanks, I'll try writing against the hold and I was thinking about calling a stored procedure. Can you perform Joins against a stored procedure?

Thanks,

Joe

Joe

just use the basic master app_events.
remove the join you have in that script.

I'm rather sure it will work.

Hi,

I tried building this file:

SET SQLENGINE = SQLMSS
SQL SQLMSS EX spEventTransfer2 2007060100, 2007061700;
TABLE FILE SQLOUT
PRINT *

-*ON TABLE PCHOLD FORMAT EXCEL
ON TABLE HOLD AS YOURFILE
END

JOIN evtApplication IN YOURFILE TO ALL APPCRYPTIC IN BUSSEG01 AS JO

TABLE FILE YOURFILE

SUM eCount/D13 AS 'Total Calls Received' OVER
tCount/D13 AS 'TRANSFERRED CALLS'

BY BUSSEG1 ROWS

ON TABLE PCHOLD FORMAT EXCEL

END

I keep getting an error messing that is around the line: ON TABLE PCHOLD FORMAT EXCEL

(FOC080) WORD OR SYNTAX IN 'FOR' PHRASE (FML OPTION) NOT RECOGNIZED: TABLE
BYPASSING TO END OF COMMAND

Thanks,

Joe

quote:

BY BUSSEG1 ROWS

ROWS is a reserved word used to specify a praticular sort order and usually followed by a byfield. Not quite sure why it's in there as I could find no other reference as to what ROWS would be, but take it out.

Is your first question solved?


You are now bringing up an other problem on an other master I think.

quote:

SUM eCount/D13 AS 'Total Calls Received' OVER
tCount/D13 AS 'TRANSFERRED CALLS'

why "OVER"

Does it work if you do not put it in an excel file ?

Thanks for all your responses. Nothing is working even when using hold files. Everything keeps timing out or not working. I'm going to have to rethink things and figure something out. My DBA advised using stored procedures. One did work but the other did not. It timed out. I was thinking about using a hold file when running stored procedures. Is this possible?

Thanks,

Joe

Joe

You never came back to us with what the sql generated from your original query was nor the size of the tables.



Best regards

John

if it truly needs to run that long, you may want to look into the possibility of running it deferred

how long is it taking, in minutes....
there may be something odd set within your browser.

Also, SQL optimization is usually a collaborative effort between a database developer and a DBA, it may be best to consult one of those if you have them about the issue. Indexing the tables on the joined values would be a good start, then I'd look into using an Integer format rather than an alphanumeric for numeric data like what you're joining on. Plain and simple, there's only 10 numbers but there's a few hundred alphanumeric characters (in reality, this method doesn't save as much time as properly indexing the table, or partitioning your data, or using a materialized view of the data to pre-aggregate information sans stuff like ID numbers)

DBA's are our friends.

Here's the code for the stored procedure that times out. I tried using a hold file with it and it still timed out.

SET SQLENGINE = SQLMSS

SQL SQLMSS EX GetMenuData2 2007061100, 2007061200;

TABLE ON TABLE HOLD AS SQLOUT
-*PRINT *

-*ON TABLE PCHOLD FORMAT EXCEL
-*ON TABLE HOLD AS YOURFILE
END
TABLE FILE SQLOUT

PRINT *
WHERE busseg1 EQ 'CCM'

ON TABLE PCHOLD FORMAT EXCEL

END

Try putting an END after the SQL command, see if it works. I had a similiar scenario where it took awhile to get the data but after adding the END statement, it's much faster now.

I agree about putting the 'end' statement in. I had a similar situation going against oracle engine. The 'end' seemed to help. Also indexing definitely a plus if you can sway the powers that be. Ira

JOE, if you look at the defines in the Master File Description they are based on a field called APPLICATION so to create your hold file start with this.

JOIN
APP_EVENTS.APPLICATION IN APP_EVENTS TO
BUSSEG01.APPCRYPTIC IN BUSSEG01 AS J0
END

TABLE FILE APP_EVENTS
SUM
CNT.APPLICATION 
BY APPLICATION
-*WHERE event_partkey EQ 2007061800
WHERE event_partkey GE 2007060100 AND event_partkey LE 2007061800
-*WHERE APP_EVENTS.APPLICATION EQ '1st_Horizon_2'
WHERE (machine_id EQ 'PPNTVA08') OR (machine_id EQ 'PPNTVA17') OR (machine_id EQ 'PPNTVA07') OR (machine_id EQ 'PPNTVA16') OR (machine_id EQ 'PPNTVA04') OR (machine_id EQ 'PPNTVA13') OR (machine_id EQ 'PPNTVA05') OR (machine_id EQ 'PPNTVA12') OR (machine_id EQ 'PPNTVA14') OR (machine_id EQ 'PPNTVA03') OR (machine_id EQ 'PPNTVA19') OR (machine_id EQ 'PPNTVA06') OR (machine_id EQ 'PPNTVA20') OR (machine_id EQ 'PPNTVA15')
WHERE event_action EQ 'CHAIN'
ON TABLE HOLD AS HOLD1
END

Then create your DEFINES for the DECODE and other login against the HOLD1 file.

Scott