Breaking the WHERE - on purpose...

[MVisconte]

Folks,

I have a problem with a badly behaving report (grin). We're trying to run the report from a far-remote SQL server. My WHERE clause runs fine in developer but won't run (at all) on the APS -- it blanks out. The server has hundreds of thousands of records, and a typical run w/out any remote criteria can take 4 hours. It takes about 30 seconds in Developer.

Can you guys help me figure out how to "break" the where to force the majority of the WEHRE to be exported to the server rather than bringing everything back and processing locally?

The where is split into main clauses:

Status condition AND
ticket type AND
Date limit AND
(multiple field test from a parameter array).

The last clause is what is causing the WHERE to fail (I believe). If I can force Crystal to pass the other three clauses to the SQL server and process the last clause locally, I think I can reduce the report time to a few minutes. My attempts at forcing the field test clause to be split off and execute locally have not been successful.

Anybody got any ideas?



--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[Turkbear]

Hi,
If you cannot rewrite the last clause ( as a series of and/or statements maybe) then try just using the 3 that do pass-through and use the last in a supression formula for the displayed details...

 

[vidru]

Can you post your actual record selection formula, as well as CR version and type of connectivity?

-dave

 

[MVisconte]

Turk - with just the first portion, we still will bring back anywhere from a couple thousand records (1 month) to tens of thousands (12 month). The first case might result in 53 records that we actually need to see. I don't know if suppression is a good way -- maybe as a last resort.

Vidru -
The query, as it sits in Crystal is:

{Tm_Action_Request.Status} = "CLOSED" and
{Tm_Action_Request.Action_Type} = "UNSCHEDULED OUTAGE" and
(
(
(
{Tm_Reason_For_Outage.RFO_2} Like split("*" & join({?Keyword}, "*,*") & "*", ",") or
{Tm_Reason_For_Outage.Service_4} Like split("*" & join({?Keyword}, "*,*") & "*", ",") or
{Tm_Action_Request.Action_Description} Like split("*" & join({?Keyword}, "*,*") & "*", ",") or
{Tm_Action_Request.Status_or_Resolution_Summary} Like split("*" & join({?Keyword}, "*,*") & "*", ",")
)
)
)
AND
{Tm_Action_Request.Zulu_Date_Time_Out} in
DateDiff ('s', #Jan 1 1970#, DateAdd ("m",-{?SelectDate},CurrentDate))
to DateDiff('s', #Jan 1 1970#, CurrentDate)

It looks worse than it is. I have also tried substituting the multi-field test into a user-formula and just referencing that:

@KeyArray
{Tm_Reason_For_Outage.RFO_2} Like
split("*" & join({?Keyword}, "*,*") & "*", ",") or
{Tm_Reason_For_Outage.Service_4} Like
split("*" & join({?Keyword}, "*,*") & "*", ",") or
{Tm_Action_Request.Action_Description} Like
split("*" & join({?Keyword}, "*,*") & "*", ",") or
{Tm_Action_Request.Status_or_Resolution_Summary} Like
split("*" & join({?Keyword}, "*,*") & "*", ",")

This makes a much more readable query, but it doesn't change the result.

There are two results. In Developer, the resulting pass-through query is:

WHERE
Tm_Action_Request."Status" = 'CLOSED' AND
Tm_Action_Request."Action_Type" = 'UNSCHEDULED OUTAGE' AND
(Tm_Reason_For_Outage."RFO_2" LIKE '%UPS%' OR
Tm_Reason_For_Outage."Service_4" LIKE '%UPS%' OR
Tm_Action_Request."Action_Description" LIKE '%UPS%' OR
Tm_Action_Request."Status_or_Resolution_Summary" LIKE '%UPS%') AND
Tm_Action_Request."Zulu_Date_Time_Out" >= 1107907200 AND
Tm_Action_Request."Zulu_Date_Time_Out" <= 1110326400

Which is what you would expect. It runs quickly and returns a one-for-one result (pulls ONLY the applicable records).

When I look at the APS, after a FOUR-HOUR RUN, the submitted query is the same as the original CR query, but there is no indication of whether it parsed and did a pass-thru (I doubt) or not.




--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[Turkbear]

Hi,
What does the query look like in the ShowQuery panel?
You can also simplify the parens like this, I think:

and
      (
        {Tm_Reason_For_Outage.RFO_2} Like split("*" & join({?Keyword}, "*,*") & "*", ",") or
        {Tm_Reason_For_Outage.Service_4} Like split("*" & join({?Keyword}, "*,*") & "*", ",") or
        {Tm_Action_Request.Action_Description} Like split("*" & join({?Keyword}, "*,*") & "*", ",") or
        {Tm_Action_Request.Status_or_Resolution_Summary} Like split("*" & join({?Keyword}, "*,*") & "*", ",")
   ) 
and...

Shouldn't make a difference in where it is parsed, however..

 

[vidru]

If the Show SQL Query window shows everything as expected, and if you copy/paste the resulting query into Query Analyzer and get the expected results, then the problem lies within Crystal trying to re-filter the data again once it's come back from the server.

I ran across this exact same issue with a multiple OR condition in a record selection formula. The workaround was to add IsNull checks at each level. Try rewriting the part where you're dealing with the multiple value parameter like this:

(
(
Not IsNull({Tm_Reason_For_Outage.RFO_2}) And
{Tm_Reason_For_Outage.RFO_2} Like split("*" & join({?Keyword}, "*,*") & "*", ",")
)
or
(
Not IsNull({Tm_Reason_For_Outage.Service_4}) And
{Tm_Reason_For_Outage.Service_4} Like split("*" & join({?Keyword}, "*,*") & "*", ",")
)
or
(
Not IsNull({Tm_Action_Request.Action_Description}) And
{Tm_Action_Request.Action_Description} Like split("*" & join({?Keyword}, "*,*") & "*", ",")
)
or
(
Not IsNull({Tm_Action_Request.Status_or_Resolution_Summary}) And
{Tm_Action_Request.Status_or_Resolution_Summary} Like split("*" & join({?Keyword}, "*,*") & "*", ",")
)
)

-dave

 

[MVisconte]

Found the reason for the horrible slowdown.

One of the things passed down to the sub-report is information used to conditionally outline fields and highlight fields. These were passed as parameters to the subreport, but for some reason they showed up in the WHERE clause. I found out by letting the report finish it's 4-hour run, then drilling down to see the details. When I got to the detail level, I watched the record count start all over again, and go through the subreport table (as large as or larger than the main table). It shouldn't have, it SHOULD have just pulled 1-5 or so records based on the parent record. Opening up the SELECT, I found the WHERE to include two complex functions, twice, and a RANGE condition on the record ID. Sheesh! So, best as I can figure, the main report went through all umpty-hundred-thousand records in the main table, and then went through an additional umpty-hundred-thousand for EACH parent record.

Manually hacked out the un-necessary FORMATTING conditionals, and converted the range condition into a simple "=" condition, and the whole thing runs in under 30 seconds.

Whew. I've been wrestling with this reoprt for months.

Oh, the whole statement parses now. Evidently the dfective coupling with the subreport was enough to royally screw up the main report WHERE clause.

Marc

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports