Multiple Data Sources (Speed Issue)

[baycolor]

Hi,

I'm using CR XI and deploying reports to an XI server. The database I use is Teradata.

I've created a main report that uses a view from Teradata database A and a view from Teradata database B.

The views on both systems are identical. I'm just doing this as a first time test of using multiple data sources

For reasons unkonwn to me when I connected to Database A I wasn't able to see the account view that I have access to so I used Add Command to get the data. Then I connected to Database B (where I can see the account view). I used the Database Expert links tab to join the two views together by the account_nbr field (join type: inner, enforce join: enforced both, link type: =).

Mmy output fields are
DatabaseA.account_nbr, DatabaseB.account_nbr and DatabaseB.account_name.

The tables on both systems that the views are based upon have unique primary indexes on account_nbr. Also both tables have the exact same data and 20,000 rows of it.

Problem is that when I run the report the output is returned incredibly slow.

Both databases are very fast. I know that Crystal Reports on my laptop is doing the joining but why the huge performance problem. When I run a report grabbing account_nbr and account_name against each table individually I get all data back in less than a minute.

I've seen some postings about people accessing different databases using subreports - main report hits Database 1 and the subreport hits Database 2 then parameters are passed from sub to main in order to get Database 2's values onto the main report. I don't understand how working that way would be any faster than just letting CR do the joining directly from the start.

Anyone have any performance issues when accessing two database?

Any ideas, tips, reommnedataions?

Thanks in advance!

 

[synapsevampire]

Since the data is across 2 datasources, the processing is being performed by Crystal.

I've worked with teradata in the past, but can't recall too much about it, but I'd suggest that you create a View in Teradata that does the joining of the Views, if that's possible. That way the database does the work.

An alternative would be a stored procedure.

-k

 

[baycolor]

My mistake I should have stated in my original post that that Database A and Database B are on two completely separate systems. So its really SystemA.DatabaseA.ViewA and SystemB.DatabaseB.ViewB. No chance of doing a view or a a stored procedure. At least not in my IT environment.

I was afraid that was going to be the answer though - the processing is being performed by Crystal Reports.

Back to the drawing board.

 

[synapsevampire]

Here's a potential fix, albeit also a tad fugly.

Create an MS Access database, and LINK (not import) the teradata table in there. Create a query in Access as the datasource, and use that as the Crystal datasource.

It'll be faster anyway...

-k

 

[baycolor]

Thanks!

 

[Turkbear]

Hi,
Does Terradata not have anyway of connecting 2 separate databases? ( Like Oracle's Database links)?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[baycolor]

It does via a third party product the name of which escapes me right now. In the past this type of connectivity hasn't been a priority in the Terdata world (becoming more of one recently).

I'm probably going to have to temporarily live with a workaround that will be almost impossible to automate without a lot of effort - which will blow the estimated effort for this report out of the water.

Anyway back to basking in the rays of 500 watt flourescent light bulbs in my climate controlled 68 degree 6x6 cage.

Thanks.

 

[synapsevampire]

The MS Access solution can be automated, and is simple to set up. Just make sure that you select Save Password when you LINK in the tables.

-k

 

[baycolor]

Crash and burn. Simply attempted to link tables and got the following error:

"Reserved error (-7722); there is no message for this error."

I don't even want to attempt to debug this as I'm having other ODBC issues and this is taking me into that area.

I'm about to try this... What if I created a main report that grabbed info from SystemA.DatabaseA.ViewA and a subreport that grabbed info from SystemB.DatabaseB.ViewB then linked the reports on the account_nbr field. I'll post my results but anyone have any comments on this method?

Thanks

 

[synapsevampire]

That'll be even slower.

Where di you attempt to link tables, in Crystal?

If you're having ODBC errors, that might explain part of your performance issues, I'd address that.

Of course I'd need to know what software product this error came from first...

Using Access is a common fix for this, so consider using it.

-k

 

[baycolor]

I attempted to link in MS Access so this is a MS Access error.

The "other" ODBC errors I have are related to version compatibility between the 5 different data sources that I usually connect to invividually. My PC is on version A of a Teradata database driver. And my CR enterprise server is also on version A of a Teradata database driver. Everything I develop works on the CR enterprise server. Some Teradata sources that I have connect to have some compatability issues with the version of the driver I use - so sometimes things don't work the same on those systems. Point is to the people I do reporting work for I'm not changing my ODBC version to get something to work for an older system.

Anyway when I use my ODBC driver individually against SystemA.DatabaseA.ViewA and SystemB.DatabaseB.ViewB from Crystal Reports or my query tool the results come out at lightning speed. It's when I started doing "all the join processing in CR" that things started to crawl.

This MS Access connectivity issue is completely new. Worse I just found out the server that CR Enterprise is on doesn't even have MS Access on it.

Thanks everyone for the help. I'll have to figure something out now.

 

[synapsevampire]

You don't need MS Access on the server.

I gather that you're new to MS Access, teradata and Crystal.

Create the Access database, link the teradata tables (use Sav Password), then palce the Access database on a drive where the CE server can read it.

-k

 

[baycolor]

Not new to Teradata. Fairly new to CR. Totally inexperienced with Access but I've been around IT long enough that I should have realized that I didn't need to have the Access database on the CR server. Thanks.

 

[synapsevampire]

Well I'm new to Teradata, or briefly experienced.

But this method is common to disparate databases, including mixing database types.

Give it a whirl, I think that you'll be pleased with the results, well, more pleased than with Crystal alone.

-k

 

[baycolor]

I'm taking your advice and going the Access path. But I'm stumped on the connectivity issue. I've googled for the "Reserved error (-7722); there is no message for this error." error message and there isn't a log out there. And as I can't/won't update my PC's ODBC version I'm going to have to find another machine to use and hopefully create the Access database there - then move it to a server.

Thanks for the help.

 

[synapsevampire]

Then you'll have the same concern with ODBC connectivity on the server...

-k

 

[baycolor]

If I...

1)
Get an Access dbase on a server somewhere that has an ODBC driver that allows to connect.

2)
Develop my CR on my PC so that it hits the Access datbase.

Then when I publish the CR it should pull from the Access database at its "public" location.

That works I believe.