on 08-23-2011 11:55 PM
I have 3 oracle tables linked by key and my report returns 160k rows in about 20-25 seconds.
If i add a MS ACCESS table, and link it by name to one of the oracle tables(inner join) the query is still running
The MS ACCESS table is just 2 columns (lookup name/alias name)
i link the oracle table oracletable.lookupname > ms accesstable.lookup.name, and slect mstable.alias name
It does return data and it is correct, just very very slow.
thanks in advance.
Hi John,
When Crystal Reports processes a report on two (disparate) datasources the queries are individually sent to the respective datasources and the rest of the processing is done locally by Crystal Reports.
So, in this case, a query is sent out to the Oracle Server and it retrieves around 160k records which is stored in a temp file locally.
Another query is sent to the Access database which sends a few records to Crystal. Again, all the records are stored in a temp file locally.
Then, depending upon the links specified in the database expert, Crystal Reports links these two tables "locally" and applies any record selection or additional filtering.
In a normal situation, where all the links and the record selection is performed by the database server, in this case, the simplest of the jobs is passed on to the database server and CR does all the complex, CPU and memory hogging routines.
That's where latency creeps in!
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I have done this and Crystal is not always happy with mixing databases but it can be done. There maybe a problem with linking the two databases. Have you done it before? Maybe you should try a simple test report using just one table from each database as they are used in your report.
If that works, look at your data selection and start picking it apart.
Debi, i have linked Oracle/Ms Acces table together in the past. It was slow, but at least it worked.
This just will not work, i've tried just linking 1 oravle table to ms acess, it works, but very very slow.
when the 2nd and 3rd oracle tables are introduced, it simply runs forever.
i left it running last night when i left, and this morning got this error :
Database Connector Error: 'HY000:[Oracle][ODBC][ORA]ORA-01410:
invalid ROWID
[Database Vendor Code:1410 ]'
after 88009 records. Without the ms access table this query returns 160k records in about 30 seconds.
I have tried making the lookup table an xls, and a .csv. All fail.
i don't know ehere to go with this ?
thanks John
Debi, No worries on the delay.
Access table has 41k records.
What i have discovered is that if i create a report from just the 2 column access table, and then export via ODBC to a .mdb using the driver "Driver do Microsoft Access(.mdb) then link this table, the query runs a lot lot faster.
so this driver combo appears to correct the speed issue.
Now the problem i have is that the Oracle tables alone produced a report of 160k records.
When i link to the ms access table, the report produces about 270k ?
Links order is :-
SRMDEVICEALLOCATION.ARRAYDEVICEKEY --> SRMARRAYDEVICE.ARRAYDEVICEKEY
SRMDEVICEALLOCATION.ARRAYKEY --> SRMARRAY.ARRAYKEY
SRMDEVICEALLOCATION.HOSTORHBAALIAS --> CREXPORT.DE_Full_host_wwn_Field2
SRMARRAYDEVICE.ARRAYKEY --> SRMARRAY.ARRAYKEY
all links options are :-
inner join,
not enforced,
link type =
Thanks John
Your links, all inner join:
SRMDEVICEALLOCATION.ARRAYDEVICEKEY = SRMARRAYDEVICE.ARRAYDEVICEKEY
SRMDEVICEALLOCATION.ARRAYKEY = SRMARRAY.ARRAYKEY
SRMDEVICEALLOCATION.HOSTORHBAALIAS = CREXPORT.DE_Full_host_wwn_Field2
SRMARRAYDEVICE.ARRAYKEY = SRMARRAY.ARRAYKEY
I know you have been doing this awhile, so bear with me if I tell you something you already know... It is the little things we overlook that give us the most problems.
You do understand the one-to-many link restriction, which is you can only have one one-to-many link in a report... Yes? Where a record in one table may have more than one match in the linked table... If any two or more links are one to many. it will cause you to get the extra records.
Also It looks like you have a circular reference from
SRMDEVICEALLOCATION to SRMARRAYDEVICE and SRMARRAYDEVICE to SRMARRAY and SRMARRAY back to SRMDEVICEALLOCATION
So I hope that one of these tables is repeated twice in your report as circular references can also cause problems.
Thanks Debi, i did delete the additional SRMDEVICEALLOCATION.ARRAYKEY = SRMARRAY.ARRAYKEY
link
However i do not seem to be able to get the result i need.
Here's an overview of what is happening, and please don't fall asleep reading it
The original query is :-
SRMDEVICEALLOCATION.ARRAYDEVICEKEY --> SRMARRAYDEVICE.ARRAYDEVICEKEY
SRMDEVICEALLOCATION.ARRAYKEY --> SRMARRAY.ARRAYKEY
It Yields 169236 records
If i add the "CREXPORT" table with "inner join"
SRMDEVICEALLOCATION.HOSTORHBAALIAS --> CREXPORT.DE_Full_host_wwn_Field2
it yields 149798 records
and "left outer join" yields
SRMDEVICEALLOCATION.HOSTORHBAALIAS --> CREXPORT.DE_Full_host_wwn_Field2
it yields 263209 records
the srmdeviceallocation table has a field(hostorhbaalias)
which can either be :-
appmd526(8 chars)
or
c000010000003123(16 chars)
when it contains an element of 16 chars, i want to get the
host name from "CREXPORT.DE_Full_host_wwn_Field1" in the "CREXPORT" table
which has 2 fields CREXPORT.DE_Full_host_wwn_Field1 and CREXPORT.DE_Full_host_wwn_Field2
Field1 | Field2
appwd526 | c000010000003123
so if srmdeviceallocation.hostorhbaalias is c000010000003123
i want to display appwd526
If i change the link
SRMDEVICEALLOCATION.HOSTORHBAALIAS --> CREXPORT.DE_Full_host_wwn_Field1
i get > 600k records.
What is the best method(link, records selection, formula) that will return 169236 records using the CREXPORT
table. I always want to return CREXPORT.DE_Full_host_wwn_Field1
is there is a match with SRMDEVICEALLOCATION.HOSTORHBAALIAS, otherwise "not found in alias table"
formula like :-
if {SRMDEVICEALLOCATION.HOSTORHBAALIAS} = {CREXPORT.DE_Full_host_wwn_Field2}
then {CREXPORT.DE_Full_host_wwn_Field1}
else if {SRMDEVICEALLOCATION.HOSTORHBAALIAS} = {CREXPORT.DE_Full_host_wwn_Field1}
then {CREXPORT.DE_Full_host_wwn_Field1}
else "not found in alias table"
The CREXPORT table is purely a link by name not key.
it looks like you may have a one to many, or none relationship between SRMDEVICEALLOCATION.HOSTORHBAALIAS and CREXPORT.DE_Full_host_wwn_Field2 where SRMDEVICEALLOCATION.HOSTORHBAALIAS is finding more than 1 match in CREXPORT.DE_Full_host_wwn_Field2 , or no match.
look at the data in CREXPORT, and group on CREXPORT.DE_Full_host_wwn_Field2. Are you finding multiple records for this field? If so, that is your problem.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.