×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Slow Performing Report
3

Slow Performing Report

Slow Performing Report

(OP)
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

RE: Slow Performing Report

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.

RE: Slow Performing Report

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
 

RE: Slow Performing Report

(OP)
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

RE: Slow Performing Report

Dell,

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

Chris,

Can you please post your final solution?

-LB

RE: Slow Performing Report

(OP)
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'
)
)))

RE: Slow Performing Report

Thanks--that looks like a good approach.

-LB

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close