Slow Performing Report 3

[CrystalChris]

Hi,

I've developed a report in Crystal Reports XI which will list the records in Command 1 which are not in Command 2. The problem that I'm facing is the report takes forever to load. However, if I do the same query in Microsoft Access it loads within 10 seconds.

Command 1

SELECT CPINFO.REFVAL, CPINFO.ADDRESS, CPINFO.EHAREATM, CPINFO.TRADEAS, XIREC.REFVAL as REFVAL2, XIVISIT.XIVTYPE, XIVISIT.DATEVISIT, XIVISIT.VOFF, XIACTION.XIACT
FROM ((CPINFO INNER JOIN XIREC ON CPINFO.KEYVAL = XIREC.MDKEYVAL) INNER JOIN XIVISIT ON XIREC.KEYVAL = XIVISIT.PKEYVAL) INNER JOIN XIACTION ON XIVISIT.KEYVAL = XIACTION.PKEYVAL
WHERE (((XIVISIT.XIVTYPE)='FDHYG' Or (XIVISIT.XIVTYPE)='HELA' Or (XIVISIT.XIVTYPE)='FDSTDS' Or (XIVISIT.XIVTYPE)='FDHYRV' Or (XIVISIT.XIVTYPE)='FDSTRV' Or (XIVISIT.XIVTYPE)='HASREV') AND ((XIVISIT.VOFF)='JWATT' Or (XIVISIT.VOFF)='LMCCAN' Or (XIVISIT.VOFF)='SBALLA' Or (XIVISIT.VOFF)='PCONNO' Or (XIVISIT.XIVTYPE)='SCRAWF' Or (XIVISIT.VOFF)='RGIBSO' Or (XIVISIT.VOFF)='AHULL' Or (XIVISIT.VOFF)='RH' Or (XIVISIT.VOFF)='SJOSEL' Or (XIVISIT.VOFF)='JLECYN' Or (XIVISIT.VOFF)='AL' Or (XIVISIT.VOFF)='RMANSO' Or (XIVISIT.VOFF)='BMCGLO' Or (XIVISIT.VOFF)='BMCHA' Or (XIVISIT.VOFF)='NREID' Or (XIVISIT.VOFF)='MROBER' Or (XIVISIT.VOFF)='LSTARK' Or (XIVISIT.VOFF)='DWARD' Or (XIVISIT.VOFF)='NJWILL'))

Command 2

SELECT CPINFO.REFVAL, CPINFO.TRADEAS, XIREC.REFVAL as REFVAL2, XIVISIT.XIVTYPE, XIVISIT.DATEVISIT, XIVISIT.VOFF, XIACTION.XIACT, CNOFFICER.NAME
FROM (((CPINFO INNER JOIN XIREC ON CPINFO.KEYVAL = XIREC.MDKEYVAL) INNER JOIN XIVISIT ON XIREC.KEYVAL = XIVISIT.PKEYVAL) INNER JOIN XIACTION ON XIVISIT.KEYVAL = XIACTION.PKEYVAL) INNER JOIN CNOFFICER ON XIVISIT.VOFF = CNOFFICER.OFFCODE
WHERE (((XIVISIT.XIVTYPE)='FDHYG' Or (XIVISIT.XIVTYPE)='HELA' Or (XIVISIT.XIVTYPE)='FDSTDS' Or (XIVISIT.XIVTYPE)='FDHYRV' Or (XIVISIT.XIVTYPE)='FDSTRV' Or (XIVISIT.XIVTYPE)='HASREV') AND ((XIVISIT.VOFF)='JWATT' Or (XIVISIT.VOFF)='LMCCAN' Or (XIVISIT.VOFF)='SBALLA' Or (XIVISIT.VOFF)='PCONNO' Or (XIVISIT.VOFF)='SCRAWF' Or (XIVISIT.VOFF)='RGIBSO' Or (XIVISIT.VOFF)='AHULL' Or (XIVISIT.VOFF)='RH' Or (XIVISIT.VOFF)='SJOSEL' Or (XIVISIT.VOFF)='JLECYN' Or (XIVISIT.VOFF)='AL' Or (XIVISIT.VOFF)='RMANSO' Or (XIVISIT.VOFF)='BMCGLO' Or (XIVISIT.VOFF)='BMCHA' Or (XIVISIT.VOFF)='NREID' Or (XIVISIT.VOFF)='MROBER' Or (XIVISIT.VOFF)='LSTARK' Or (XIVISIT.VOFF)='DWARD' Or (XIVISIT.VOFF)='NJWILL') AND ((XIACTION.XIACT)='LET1' Or (XIACTION.XIACT)='LETT1' Or (XIACTION.XIACT)='LET2' Or (XIACTION.XIACT)='LETT2' Or (XIACTION.XIACT)='LET3' Or (XIACTION.XIACT)='LETT3' Or (XIACTION.XIACT)='LET3A' Or (XIACTION.XIACT)='LETT3A' Or (XIACTION.XIACT)='LETTER' Or (XIACTION.XIACT)='LET4'))

Linking

Command1.REFVAL2 --> Command2.REFVAL2

Select Expert

Isnull({Command2.REFVAL2})

Any help would be appreciated. Thanks

 

[hilfy]

The thing you have to remember when working with multiple commands is that Crystal will NOT link them together at the database level. Instead, it will pull ALL of the data for both commands into memory and then link and filter the data in memory. This will slow down the report.

Since you're using commands anyways, if your database will allow it I would look at doing this as a single command. Set it up as if you were doing a UNION (all the fields must match between both queries) but use MINUS instead of UNION. This should give you all of the records that are in the first query that aren't in the second and the database will do all of the work for you.

-Dell

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

 

[lbass]

The problem is that if you link commands to each other or to tables, the linking occurs locally. I'm not sure whether your database supports the minus command, but try one command like the following:

SELECT
CPINFO.REFVAL,
CPINFO.ADDRESS,
CPINFO.EHAREATM,
CPINFO.TRADEAS,
XIREC.REFVAL as REFVAL2,
XIVISIT.XIVTYPE,
XIVISIT.DATEVISIT,
XIVISIT.VOFF,
XIACTION.XIACT,
Null

FROM

((CPINFO

INNER JOIN XIREC ON
CPINFO.KEYVAL = XIREC.MDKEYVAL)

INNER JOIN XIVISIT ON
XIREC.KEYVAL = XIVISIT.PKEYVAL)

INNER JOIN XIACTION ON
XIVISIT.KEYVAL = XIACTION.PKEYVAL

WHERE (
(
XIVISIT.XIVTYPE='FDHYG' Or
XIVISIT.XIVTYPE='HELA' Or
XIVISIT.XIVTYPE='FDSTDS' Or
XIVISIT.XIVTYPE='FDHYRV' Or
XIVISIT.XIVTYPE='FDSTRV' Or
XIVISIT.XIVTYPE='HASREV'
) AND
(
XIVISIT.VOFF='JWATT' Or
XIVISIT.VOFF='LMCCAN' Or
XIVISIT.VOFF='SBALLA' Or
XIVISIT.VOFF='PCONNO' Or
XIVISIT.VOFF='SCRAWF' Or
XIVISIT.VOFF='RGIBSO' Or
XIVISIT.VOFF='AHULL' Or
XIVISIT.VOFF='RH' Or
XIVISIT.VOFF='SJOSEL' Or
XIVISIT.VOFF='JLECYN' Or
XIVISIT.VOFF='AL' Or
XIVISIT.VOFF='RMANSO' Or
XIVISIT.VOFF='BMCGLO' Or
XIVISIT.VOFF='BMCHA' Or
XIVISIT.VOFF='NREID' Or
XIVISIT.VOFF='MROBER' Or
XIVISIT.VOFF='LSTARK' Or
XIVISIT.VOFF='DWARD' Or
XIVISIT.VOFF='NJWILL'
)
)

Minus

SELECT
CPINFO.REFVAL,
CPINFO.ADDRESS,
CPINFO.EHAREATM,
CPINFO.TRADEAS,
XIREC.REFVAL as REFVAL2,
XIVISIT.XIVTYPE,
XIVISIT.DATEVISIT,
XIVISIT.VOFF,
XIACTION.XIACT,
CNOFFICER.NAME

FROM (((CPINFO

INNER JOIN XIREC ON
CPINFO.KEYVAL = XIREC.MDKEYVAL)

INNER JOIN XIVISIT ON
XIREC.KEYVAL = XIVISIT.PKEYVAL)

INNER JOIN XIACTION ON
XIVISIT.KEYVAL = XIACTION.PKEYVAL)

INNER JOIN CNOFFICER ON
XIVISIT.VOFF = CNOFFICER.OFFCODE

WHERE ((XIVISIT.XIVTYPE='FDHYG' Or
XIVISIT.XIVTYPE='HELA' Or
XIVISIT.XIVTYPE='FDSTDS' Or
XIVISIT.XIVTYPE='FDHYRV' Or
XIVISIT.XIVTYPE='FDSTRV' Or
XIVISIT.XIVTYPE='HASREV'
) AND

(
XIVISIT.VOFF='JWATT' Or
XIVISIT.VOFF='LMCCAN' Or
XIVISIT.VOFF='SBALLA' Or
XIVISIT.VOFF='PCONNO' Or
XIVISIT.VOFF='SCRAWF' Or
XIVISIT.VOFF='RGIBSO' Or
XIVISIT.VOFF='AHULL' Or
XIVISIT.VOFF='RH' Or
XIVISIT.VOFF='SJOSEL' Or
XIVISIT.VOFF='JLECYN' Or
XIVISIT.VOFF='AL' Or
XIVISIT.VOFF='RMANSO' Or
XIVISIT.VOFF='BMCGLO' Or
XIVISIT.VOFF='BMCHA' Or
XIVISIT.VOFF='NREID' Or
XIVISIT.VOFF='MROBER' O
XIVISIT.VOFF='LSTARK' Or
XIVISIT.VOFF='DWARD' Or
XIVISIT.VOFF='NJWILL'
) AND
(
XIACTION.XIACT='LET1' Or
XIACTION.XIACT='LETT1' Or
XIACTION.XIACT='LET2' Or
XIACTION.XIACT='LETT2' Or
XIACTION.XIACT='LET3' Or
XIACTION.XIACT='LETT3' Or
XIACTION.XIACT='LET3A' Or
XIACTION.XIACT='LETT3A' Or
XIACTION.XIACT='LETTER' Or
XIACTION.XIACT='LET4'
)
)

I'm not sure of the impact of adding the null in the first select to correspond to the CNOfficerName field in the second. You might start by seeing if you get the desired results without the null and the name field (there must be the same number of fields, same datatype, in the same order for the comparison. Then try adding back the null and name field if that works.

PS. I also corrected what looked like an error in table name for
SCRAWF in the first command.

-LB

 

[CrystalChris]

Thanks for both your input on this. I've tried the minus command. However, it brings back records that have a letter type (XIACTION.XIACT='LETT1'). I managed to resolve the problem by using the NOT IN command.

Thanks
Chris

 

[lbass]

Dell,

I didn't see your post when I posted my response.

Chris,

Can you please post your final solution?

-LB

 

[CrystalChris]

Below is the final solution:

SELECT
CPINFO.REFVAL,
CPINFO.ADDRESS,
CPINFO.EHAREATM,
CPINFO.TRADEAS,
XIREC.REFVAL as REFVAL2,
XIVISIT.XIVTYPE,
XIVISIT.DATEVISIT,
XIVISIT.VOFF,
XIACTION.XIACT,
CNOFFICER.NAME

FROM

(((CPINFO

INNER JOIN XIREC ON
CPINFO.KEYVAL = XIREC.MDKEYVAL)

INNER JOIN XIVISIT ON
XIREC.KEYVAL = XIVISIT.PKEYVAL)

INNER JOIN XIACTION ON
XIVISIT.KEYVAL = XIACTION.PKEYVAL)

INNER JOIN CNOFFICER ON
XIVISIT.VOFF = CNOFFICER.OFFCODE


WHERE (
(
XIVISIT.XIVTYPE='FDHYG' Or
XIVISIT.XIVTYPE='HELA' Or
XIVISIT.XIVTYPE='FDSTDS' Or
XIVISIT.XIVTYPE='FDHYRV' Or
XIVISIT.XIVTYPE='FDSTRV' Or
XIVISIT.XIVTYPE='HASREV'
) AND
(
XIVISIT.VOFF='JWATT' Or
XIVISIT.VOFF='LMCCAN' Or
XIVISIT.VOFF='SBALLA' Or
XIVISIT.VOFF='PCONNO' Or
XIVISIT.VOFF='SCRAWF' Or
XIVISIT.VOFF='RGIBSO' Or
XIVISIT.VOFF='AHULL' Or
XIVISIT.VOFF='RH' Or
XIVISIT.VOFF='SJOSEL' Or
XIVISIT.VOFF='JLECYN' Or
XIVISIT.VOFF='AL' Or
XIVISIT.VOFF='RMANSO' Or
XIVISIT.VOFF='BMCGLO' Or
XIVISIT.VOFF='BMCHA' Or
XIVISIT.VOFF='NREID' Or
XIVISIT.VOFF='MROBER' Or
XIVISIT.VOFF='LSTARK' Or
XIVISIT.VOFF='DWARD' Or
XIVISIT.VOFF='NJWILL'
)
AND XIREC.REFVAL NOT IN (
SELECT
XIREC.REFVAL
FROM ((CPINFO

INNER JOIN XIREC ON
CPINFO.KEYVAL = XIREC.MDKEYVAL)

INNER JOIN XIVISIT ON
XIREC.KEYVAL = XIVISIT.PKEYVAL)

INNER JOIN XIACTION ON
XIVISIT.KEYVAL = XIACTION.PKEYVAL

WHERE
(
(
XIVISIT.XIVTYPE='FDHYG' Or
XIVISIT.XIVTYPE='HELA' Or
XIVISIT.XIVTYPE='FDSTDS' Or
XIVISIT.XIVTYPE='FDHYRV' Or
XIVISIT.XIVTYPE='FDSTRV' Or
XIVISIT.XIVTYPE='HASREV'
)


AND
(
XIACTION.XIACT='LET1' Or
XIACTION.XIACT='LETT1' Or
XIACTION.XIACT='LET2' Or
XIACTION.XIACT='LETT2' Or
XIACTION.XIACT='LET3' Or
XIACTION.XIACT='LETT3' Or
XIACTION.XIACT='LET3A' Or
XIACTION.XIACT='LETT3A' Or
XIACTION.XIACT='LETTER' Or
XIACTION.XIACT='LET4'
)

AND
(
XIVISIT.VOFF='JWATT' Or
XIVISIT.VOFF='LMCCAN' Or
XIVISIT.VOFF='SBALLA' Or
XIVISIT.VOFF='PCONNO' Or
XIVISIT.VOFF='SCRAWF' Or
XIVISIT.VOFF='RGIBSO' Or
XIVISIT.VOFF='AHULL' Or
XIVISIT.VOFF='RH' Or
XIVISIT.VOFF='SJOSEL' Or
XIVISIT.VOFF='JLECYN' Or
XIVISIT.VOFF='AL' Or
XIVISIT.VOFF='RMANSO' Or
XIVISIT.VOFF='BMCGLO' Or
XIVISIT.VOFF='BMCHA' Or
XIVISIT.VOFF='NREID' Or
XIVISIT.VOFF='MROBER' Or
XIVISIT.VOFF='LSTARK' Or
XIVISIT.VOFF='DWARD' Or
XIVISIT.VOFF='NJWILL'
)
)))

 

[lbass]

Thanks--that looks like a good approach.

-LB