Report takes forever, when linked with ms access tables

[huytonscouser]

I have a report that returns some 700k rows, but takes > 8 hours to run. It uses oracle 10g tables linked to a ms access table

without the ms access table report returns data in about 15-20 mins, but when linked to ms access > 8 hrs.

i am using 12.3.0.601 cr

This is the sql generated.


StorageScope- Phoenix
SELECT "SRMDEVICEALLOCATION"."HOSTORHBAALIAS", "SRMARRAYDEVICE"."ISSHARED", "SRMARRAYDEVICE"."ARRAYDEVICENAME", "SRMARRAYDEVICE"."ARRAYDEVTOTAL", "SRMARRAYDEVICE"."METADEVICETOTAL", "SRMARRAYDEVICE"."ISMETADEVICE", "SRMARRAYDEVICE"."SHAREDOBJECTLIST", "SRMARRAYDISK"."DISKRPM", "SRMARRAYDEVICE"."ARRAYKEY"
FROM ("EMCSTS"."SRMARRAYDISK" "SRMARRAYDISK" INNER JOIN "EMCSTS"."SRMDEVTODISK" "SRMDEVTODISK" ON "SRMARRAYDISK"."ARRAYDISKKEY"="SRMDEVTODISK"."ARRAYDISKKEY") INNER JOIN ("EMCSTS"."SRMDEVICEALLOCATION" "SRMDEVICEALLOCATION" INNER JOIN "EMCSTS"."SRMARRAYDEVICE" "SRMARRAYDEVICE" ON "SRMDEVICEALLOCATION"."ARRAYDEVICEKEY"="SRMARRAYDEVICE"."ARRAYDEVICEKEY") ON "SRMDEVTODISK"."ARRAYDEVICEKEY"="SRMDEVICEALLOCATION"."ARRAYDEVICEKEY"
EXTERNAL JOIN SRMDEVICEALLOCATION.HOSTORHBAALIAS={?WWN FOR PHOENIX: WWNNODUPS.DE_full_host_wwn_new_csv_50060482d52dcbc2} AND SRMARRAYDEVICE.ARRAYKEY={?StorageScope- Phoenix: SRMARRAY.ARRAYKEY}


WWN FOR PHOENIX
SELECT `WWNNODUPS`.`DE_full_host_wwn_new_csv_APDWD597`, `WWNNODUPS`.`DE_full_host_wwn_new_csv_50060482d52dcbc2`
FROM `WWN092911` `WWNNODUPS`
WHERE `WWNNODUPS`.`DE_full_host_wwn_new_csv_50060482d52dcbc2`={?StorageScope- Phoenix: SRMDEVICEALLOCATION.HOSTORHBAALIAS}


StorageScope- Phoenix
SELECT "SRMARRAY"."ARRAYALIAS", "SRMARRAY"."ARRAYTYPE", "SRMARRAY"."ARRAYSERIALNUMBER", "SRMARRAY"."ARRAYMODEL", "SRMARRAY"."ARRAYKEY"
FROM "EMCSTS"."SRMARRAY" "SRMARRAY"
WHERE "SRMARRAY"."ARRAYKEY"={?StorageScope- Phoenix: SRMARRAYDEVICE.ARRAYKEY}

This is the linking order :-
SRMDEVICEALLOCATION.ARRAYDEVICEKEY --> SRMARRAYDEVICE.ARRAYDEVICEKEY
SRMARRAYDISK.ARRAYDISKKEY --> SRMDEVTODISK.ARRAYDISKKEY
SRMDEVTODISK.ARRAYDEVICEKEY --> SRMDEVICEALLOCATION.ARRAYDEVICEKEY
SRMDEVICEALLOCATION.HOSTORHBAALIAS --> WWNNODUPS.DE_full_host_wwn_new_csv_50060482d52dcbc2
SRMARRAYDEVICE.ARRAYKEY --> SRMARRAY.ARRAYKEY


link option for SRMDEVICEALLOCATION.HOSTORHBAALIAS --> WWNNODUPS.DE_full_host_wwn_new_csv_50060482d52dcbc2

is left outer join

I am using the ms access table(WWNNODUPS) as a lookup table.

WWNNODUPS is setup in odbc admin as a data source using the ms
access .mdb DRIVER

Thanks in advance.

 

[hilfy]

When just using the Oracle tables, Crystal can push all of the report processing to the database. When you add the Access tables, Crystal has to do everything (joining data, filtering, etc.) in memory on the machine where the report is running. This means it will do a lot of swapping to disk which will slow it down considerably.

I assume you're joining tables together, correct? One thing you might want to try is to create a command to get the Oracle data. A command is just a SQL Select statement. Include in the command any filters that you have for that data in the main report. If you're using prompts, you'll have to delete the prompts in the main report and recreate them in the Command Editor in order for them to work. This will push all of the filtering for the Oracle data to the database so that Crystal is not doing it in memory.

Then join from the command to the Access tables. The joins and any filtering for the Access data will still happen in memory, but hopefully this will speed things up.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[huytonscouser]

Thanks, the join of the oracle tables is correct(as it only takes about 15-20 mins) when i link one of the oracle tables to the access table, then it gets really slow.

So create a sql command to get all the oracle data, then link
command.fieldname to access table.field name is something i can check.

Can i use the crystal generated sql for the command to get just the oracle data ? or do i have to generate the sql with an editor ?

 

[huytonscouser]

Thanks, the join of the oracle tables is correct(as it only takes about 15-20 mins) when i link one of the oracle tables to the access table, then it gets really slow.

So create a sql command to get all the oracle data, then link
command.fieldname to access table.field name is something i can check.

Can i use the crystal generated sql for the command to get just the oracle data ? or do i have to generate the sql with an editor ?

 

[huytonscouser]

Ok, i've managed to gather the oracle data via a sql command, and then link the resulting dataset to the ms access table.
Its like night and day. Report finishes in about 45 mins.
So all the delay was caused by crystal having to do all the upfront work.

Thank you very much.