John,
I haven't looked at your statements, but wanted to comment on general
approach to this type of performance tuning.
You potentially have 3 workload environments to tune:
1) MS-SQL database
2) DB2 for i5/OS database
3) Crystal Report API
First of all, you need to isolate where the performance issue is. It could
be in either component or some sort of strange interaction of components.
If it's on the System i, use iNav's Run SQL Scripts to tune the Crystal
Reports portion of the query that runs on System i. Once you have the
statement you want and are sure proper indexes are in place AND statement
performs to your specs, move on to other parts. I say use iNav as that
gives you access to Visual Explain API, which can prove invaluable in these
types of situations.
From experience, I can tell you that Crystal Reports processing on your PC
can be a pig too if queries return large amounts of data. My solution has
always been to minimize the amount of data being returned from the System i
by providing additional selection criteria for user to specify.
Reason this approach usually works is that end-user can't nor wants to look
at 1000 pages of ANYTHING. They want meaningful summaries.
HTH, Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John McKee
Sent: Monday, February 11, 2008 10:34 AM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Using SQL to join an i5/os databasde and a Windows database
Ideally, I would create a view. But, that could also cause other issues to
surface, due to among other things, politics.
Crystal Report Writer does have the capability to convert to SQL. I
did not lwt
it complete, but retrieved the initial definition, which is below. In
looking
at that SQL, it does not contain anything about the database on the AS/400,
which is accessed, painfully slowly, by a subreport.
Is there a way to modify this SQL to access both databases at one time?
This is the main report SQL:
SELECT
POST_CASE."MEDICAL_RECORD_NO", POST_CASE."PATIENT_NAME",
POST_CASE."DELETE_FLAG",
CLINICAL."FACILITY_ACCOUNT_NO",
FACILITY_PATIENT."FMRN",
IMPLANT."CASE_NO",
DEMOGRAPHIC."PATIENT_NAME"
FROM
{ oj ((("ORSOSLIVE"."ORSOSLIVE"."POST_CASE" POST_CASE LEFT OUTER JOIN
"ORSOSLIVE"."ORSOSLIVE"."FACILITY_PATIENT" FACILITY_PATIENT ON
POST_CASE."MEDICAL_RECORD_NO" =
FACILITY_PATIENT."MEDICAL_RECORD_NO" AND
POST_CASE."FACILITY_ID" = FACILITY_PATIENT."FACILITY_ID")
LEFT OUTER JOIN "ORSOSLIVE"."ORSOSLIVE"."IMPLANT" IMPLANT ON
POST_CASE."CASE_NO" = IMPLANT."CASE_NO")
LEFT OUTER JOIN "ORSOSLIVE"."ORSOSLIVE"."CLINICAL" CLINICAL ON
POST_CASE."ACCOUNT_NO" = CLINICAL."ACCOUNT_NO")
LEFT OUTER JOIN "ORSOSLIVE"."ORSOSLIVE"."DEMOGRAPHIC" DEMOGRAPHIC ON
FACILITY_PATIENT."MEDICAL_RECORD_NO" =
DEMOGRAPHIC."MEDICAL_RECORD_NO"}
WHERE
(POST_CASE."DELETE_FLAG" IS NULL OR
(POST_CASE."DELETE_FLAG" = 'z' OR
POST_CASE."DELETE_FLAG" = ' ')) AND
IMPLANT."CASE_NO" IS NOT NULL
ORDER BY
DEMOGRAPHIC."PATIENT_NAME" ASC
And this is the subreport SQL. For purposes of getting immediate
access to the
SQL, I had to enter the subreport parameter, which is just a short string,
specifically "123456". This is not the entire key field, as the
subreport also
needs to test for BRMDRWU."RWHSP#" = 1
SELECT
BRMDRWU."RWMRC#", BRMDRWU."RWACCT"
FROM
"S1015362"."BSYDTAC"."BRMDRWU" BRMDRWU
WHERE
BRMDRWU."RWACCT" = 123456
(DUH category now)
I changed the subreport to use a hard codes record selection for
RWHSP#, so now
subreport SQL now looks like this:
SELECT
BRMDRWU."RWHSP#", BRMDRWU."RWMRC#", BRMDRWU."RWACCT"
FROM
"S1015362"."BSYDTAC"."BRMDRWU" BRMDRWU
WHERE
BRMDRWU."RWACCT" = 123456 AND
BRMDRWU."RWHSP#" = 1
I am very troubled by this. Before I added the proper second parameter, for
RWHSP#, it was agonizingly slow, but produced a report for last name
starting
with "A" that was over 80 pages long. Now, it is blazing fast, but report
is
now 8 pages?????
Since RWHSP# is key 1 and RWACCT is key 2 of the AS/400 database file, it is
obvious that both should be in the selection report. In better than 99
percent
of the time, RWHSP# will be equal to 1. But, why is report so very much
shorter? That is a question I am asking myself and also wondering if
something
else might be going on that some guru might be able to answer for me.
Thanks.
John McKee
Quoting Luis Colorado <LuisC@xxxxxxxxxxxxxxxx>:
John,
Your life could be much easier if you could create a SQL view on that
iSeries file (by the way, don't worry too much about the naming...
even the veterans lose track of what's the current name. So you can
call it anyway you want).
If you, or somebody you know is able, create a view using SQL. It
would look something like the following:
CREATE VIEW yourViewName AS
SELECT char(youNumericField) as yourNewField,
... your other fields here ...
FROM PMEMPM
With that, you get a "logical" file named "yourViewName" with a
character field instead of a numeric field.
If it is not possible to create a view, then you can probably use an
SQL query.
I hope that helps,
Luis
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John McKee
Sent: Saturday, February 09, 2008 2:22 AM
To: Midrange Systems Technical Discussion
Subject: Using SQL to join an i5/os databasde and a Windows database
I am supporting a Windows application that is built on MS-SQL
database. I write
Crystal reports, among other things.
I need to retrieve data from the i5 (forgive me, I still do not know what
the
current name is) based on data from the MS-SQL database.
Bad news is that the key field I am using on the MS-SQL database is a
character
field, which I convert to a numeric fiel to access the i5. The only
direct way
to do that with standard Crystal syntax is to use a subreport. VERY
VERY, VERY
SSSSSLLLLLLOOOOOOOOOOWWWWWWWWWWW.
In addition, the key field I am using is incomplete, as the database I am
accesing has two key fields: the one I have as second key, and a
numeric field
(fixed value of 1 in this case) as the first key.
Since Crystal does allow SQL to be used, I was wondering if that
would allow for
faster processing. Specifically, can a join be constructed between the
two
systems so that the sub report is not needed?
Specifically, on the MS-SQL database, I have a string field, length 20.
The
numeric field on the (I wish I knew the proper name. Keep wanting to say
AS/400, and I know from reading previous posts that is a dead name) i5 is
numeric, 7 packed, no decimal places. I think I might have tried, but
can't
remember, creating a numeric field on the MS-SQL database and tring to
link
that. If I recall, that fails because the field I create is not a
database
field. Could be mistaken. Been a long time ago.
Sorry for the long winded question. But, I have to do something to
improve
performance. I ran this mess for six hours today, and created a
partial report
about 180 pages. Full report would be around 1000. This can't be good
for
either MS-SQL or the i5 to do this much heavy lifting.
Thanks for suggestions.
John McKee
midrange.com
