Hi, I manages to load all the data into ICE

Now when I tried the following sql but it si soo slow:

SELECT COUNT(*)
FROM
(SELECT PARTNER_SERVICE.PRODUCT_TYPE, CALL_USAGE.CARD_SERIAL_NUMBER,
CALL_USAGE.SERVICE_NUMBER, CALL_USAGE.DESTINATION_NUMBER,
CALL_USAGE.CALL_START_DATE, CALL_USAGE.CALL_END_DATE,
CALL_USAGE.TOTAL_DURATION_SEC, CALL_USAGE.CALL_CHARGES,
CALL_USAGE.AREA_CODE, CALL_USAGE.OLNOS_FLAG, PARTNER_SERVICE.EXCHANGE_ID,
CALL_USAGE.CALL_DATE, exchange.PTT_CODE, exchange.STATE_CODE
FROM PARTNER_SERVICE
Inner Join CALL_USAGE
On PARTNER_SERVICE.PARTNERSERVICEKEY = CALL_USAGE.INVENTORY_KEY
Left Join exchange On PARTNER_SERVICE.EXCHANGE_ID = exchange.EXCHANGE_ID)
AS EWRPT_TEMP_TABLE

The above sql is working on my XP develpment machine and reasonable fast on test data.
Any guide is welcome

Hi,

Is that any special reason why you place the main query in subquery? That makes all much less efficient. Potentially huge result set is generated which is next counted. Try just

Check also if there is any warning generated (show warnings;)

Thanks,
Piotr

The sql is auto generate by report writer.m I have no control on the sql generated.

In my report, all the report is based on view created in ICE. Is there any different in performance if I use view against direct table join??

Hi,

If you use view in a query, it’s definition is just expanded and that leads to using subqueries. That makes the query less efficient since subquery becomes a temporary table for which there is no Knowledge Grid and various optimization can’t take place. Also in this way there can be introduced various inefficiencies in overall query design. An example is the query from your previous post. Btw. can you check and compare the speed of both queries.

Piotr