Very slow retrieving records 1

[FatEric]

Hi all,

I am writing a small report in CR9. I have two tables linked to each other with a left join. The first table has some user info in it, with some codes. Those codes you can find in the second table, with also i translated code. In my report I want those translated codes to show up.

So I linked to two tables to each other and CR9 is retrieving everything, but very slow. When I retrieve just the original codes, it will run fast, but when I retrieve the translate ones, its slow. When I look at the SQL statement in CR9, I nowhere find the join between the two tables. So my question is: does CR9 first gets all records, and later on links the two tables (within the program) rather than execute the SQL statement with the join...

And then I there a manner to get the records much faster.

If a SQL statement is necessary to be viewed here, let me know and I will post you just what I got. Also for further info, let me know.

Thanks in advance, FatEric

 

[lbass]

Are these tables from the same database? If you don't see the join in the query, then the join is occurring locally, which is why the report is so slow. Maybe you should show us the query.

-LB

 

[FatEric]

Hi lbass,

I'm referring to one database (on an AS400 system), but I'm connecting trough 2 ODBC connections (because I'm using to library's on the AS400). Maybe that's why Crystal Reports things it are two different databases...

When I look at the query trough Database -> show SQL query, I only see this one:

SELECT "BWEGPF"."BWPENR", "BWEGPF"."BWBWDT", "BWEGPF"."BWBWSL"
FROM "ADMIN"."PZW8DTA"."BWEGPF" "BWEGPF"
WHERE ("BWEGPF"."BWBWDT">={d '2008-02-01'} AND "BWEGPF"."BWBWDT"<={d '2008-02-29'})
ORDER BY "BWEGPF"."BWPENR", "BWEGPF"."BWBWDT"

In the Database -> Database expert I see 2 tables, linked together trough ONE link, but I don't see it in the SQL query. Maybe that's why it is so slow.

Hopefully this is a little bit more info. If you need some more, please let me know.

Thanks in advance, FatEric

 

[lbass]

I'm not familiar with the use of libraries, but I do think having the two connections is the problem.

-LB

 

[FatEric]

Thanks lbass for your answer. Indeed I was linking trought 2 odbc connections. I found a manner to get the library's into CR9 trough just one odbc connection. I'm going to rebuild my report with those links and see it this is working much faster. I will let you know something when I have tested the report.

Thanks very much.

FatEric

ps: a star for you !

 

[FatEric]

Hi lbass,

I now created a new report with just one odbc connection to the two library's, and you're were right, this is working much faster !!
When I had the two odbc connections, the reports ran for 17 minutes (yes that's right). No it runs in no more then 1 minute. So thank you very much for your help.

Greetz, FatEric