Report taking over 8 hours to run - help!

[kapaa]

I'm not sure if this problem can be solved via a forum but I'm willing to give it a try. Maybe someone will see something I'm doing wrong... actually a consultant group created this report 2 years ago and all was fine at first but now after collecting two years of data things are getting bad. To run this report for range of one month is taking well over 8 hours. So maybe someone will spot someting I can change to hopefully make a difference.

I'm not sure what I can include to make it easier for someone to help me so I'll start with this. This report is supposed to find the number of duplicate problems logged to a support desk.

Someone figured that we need to compare the tickets logged and if a particular customer has two or more tickets under the same category, such as Hardware\Printers\Laserjet, within a two week period, those must be duplicate calls indicating the customer did not call back on the existing ticket number but instead created a new ticket. Obviously not very accurate but apparently good enough for mgmt.

I guess the consultant that created this report was hoping it would ONLY sort through the records within the date range provided but it doesn't appear to be doing that. There also appears to a subreport called total and percent but I'm not sure how to view subreports.

Below is the record selection formula used and the SQL query if that helps:

if {call_req_1.id}<> {call_req.id} and {call_req.customer} = {call_req_1.customer}
and {call_req.category} = {call_req_1.category} and {call_req.group_id}={call_req_1.group_id}
and {call_req.group_id} = 404162 and {call_req_1.group_id} = 404162
and Abs ({call_req.open_date}-{call_req_1.open_date}) < 864000000
and Abs ({call_req.open_date}-{call_req_1.open_date}) <> 0
and (DateTimeToDate (PDMTimeToDateTime ({call_req.open_date})) >= {?StartDate})
and (DateTimeToDate (PDMTimeToDateTime ({call_req.open_date})) <= {?EndDate})
and (DateTimeToDate (PDMTimeToDateTime ({call_req_1.open_date})) >= {?StartDate})
and (DateTimeToDate (PDMTimeToDateTime ({call_req_1.open_date})) <= {?EndDate})
and UpperCase ({ctct.c_userid}) <> "UNKNOWN"
then true
else
false

*** ___________________________________ ****

SELECT
call_req."id", call_req."ref_num", call_req."open_date", call_req."group_id", call_req."customer", call_req."category",
call_req_1."id", call_req_1."open_date", call_req_1."group_id", call_req_1."customer", call_req_1."category",
ctct."c_userid"
FROM
{ oj ("AHD"."AHD"."call_req" call_req INNER JOIN "AHD"."AHD"."ctct" ctct ON
call_req."customer" = ctct."id")
INNER JOIN "AHD"."AHD"."call_req" call_req_1 ON
call_req."group_id" = call_req_1."group_id"}
WHERE
call_req."group_id" = 404162 AND
call_req_1."group_id" = 404162
ORDER BY
ctct."c_userid" ASC,
call_req."ref_num" ASC

 

[synapsevampire]

In future posts, please include your software and the database being used, it really hamstrings people to nopt provide basic essential infortmation

Anyway, I see many ways to speed this up, however if this is being used in a proprietary application there may be concerns, so make suere that you ONLY work on a copy, and keep the original.

I'll give you my blow by blow on this:


//{call_req_1.id} <> {call_req.id}
//!redundant, they later state specific values
//and
//{call_req.customer} = {call_req_1.customer}
//and
//{call_req.category} = {call_req_1.category}
//and
//{call_req.group_id}={call_req_1.group_id}
//!The above should be done with joins on the tables
//and
{call_req.group_id} = 404162
and
{call_req_1.group_id} = 404162
and
Abs({call_req.open_date}-{call_req_1.open_date}) < 864000000
// Not sure why they're using an ABS
// But you should create a SQl Expression to do this
// Then reference the SQL Expression in the record
// selection
and
{call_req.open_date}-{call_req_1.open_date} <> 0
// Fixed the above
and
(
(DateTimeToDate (PDMTimeToDateTime ({call_req.open_date})) >= {?StartDate})
)
// PDMTimeToDateTime is proprietary
and
(
(DateTimeToDate (PDMTimeToDateTime ({call_req.open_date})) <= {?EndDate})
)
and
(
(DateTimeToDate (PDMTimeToDateTime ({call_req_1.open_date})) >= {?StartDate})
)
and
(
(DateTimeToDate (PDMTimeToDateTime ({call_req_1.open_date})) <= {?EndDate})
)
and UpperCase({ctct.c_userid}) <> "UNKNOWN"
// Create a SQL Expression for the above as well and
// reference it in the record selection

the point is that everything EXCEPT the proprietary functions should show in the SQL Query, and perhaps even those will.

Again, the SQL Expressions are Crystal version and database dependent, so in the future make sure that you supply the basics.

-k

 

[kapaa]

Thank you, I'll give this a try when I'm back in the office Wed. and I appologize not including the info that I'm using crystal 8 and 8.5 against a sql db. To confirm a few things mentioned, you're saying to try "commenting" out the lines indicated above because they are redundant. You also said 'not sure why they are using an ABS as to a SQL expression' What is ABS? As for sql expressions, I see where I can create these, I'll have to look into how to this and then do like you indicate, referece the expression from the formula. Strange, in all the reports I've create, and probably rather simple reports, I have never used the sql expression option.

 

[synapsevampire]

The ABS is their code, it's absolute value, meaning it removes negatives.

I guess SQL db means SQL Server.

I'm running Oracle, SQL Server and MySQL, all of which are SQL dbs.

The SQL Expression is a powerful tool, and in all of the reports you develop you should see that what is in the record selection is passed to the database, if not, fix it or seek assistance.

-k

 

[kapaa]

I went to link those columns from call_req to call_req1 and noticed a strange link. call_req.group_id is linked to call_req_1.group_id but so is call_req.category.

If I browse the data in group_id it looks like 404162 but whereas the data in category is pcat:408390. Typically a group_id would be related to value of something like Security Team where as category would be Hardware.Printers. I'm not sure how such a relationship would work, something new to me.