Hi,
My reports run so slow!
Here is what happened:
1) First source of data come from a Stored Procedure
2) Then the resultset is joined to a table inside Crystal Reports.
I think the join slow because it could not use the indexes.
So how do I retain the indexes in the RecordSet to join with other table ?
Can it be done ?
This field "t2.SCANNING_FACILITY" will be used to join to a new table inside Crystal Report.
Following is my SP:
CREATE PROCEDURE NCSS_SEL_P100N (
IN p_startdate DATE,
IN p_enddate DATE,
IN p_user VARCHAR(32)
)
LANGUAGE SQL
RESULT SETS 1
P1: BEGIN
-- Declare Variables
DECLARE v_LowerBound DATE;
DECLARE v_UpperBound DATE;
-- Declare Cursor
DECLARE c_1 CURSOR WITH RETURN FOR
SELECT
t1.FACILITY,
t1.SCANDATE,t1.SCANTIME,t1.SCANUSER,
t1.IDXDATE,t1.IDXTIME,t1.IDXUSER,
t1.COMDATE,t1.COMTIME,t1.NPAGES,
t1.BATCHID,t1.ENCNO,t1.SLEVEL,
t2.FACILITY,t2.SCANNING_FACILITY,t2.REGION
from ncssr2 t1 LEFT OUTER JOIN
ncssnc.facility_mapping t2
ON t1.facility = t2.facility
WHERE
(NOT (t1.scandate IS NULL OR t1.facility IS NULL OR t1.scanuser IS NULL OR
t1.facility = '' OR t1.scanuser = '')
AND
(t1.scandate BETWEEN p_startdate AND p_enddate)
AND
(UPPER(p_user) = 'ALL' OR UPPER(t1.scanuser) = UPPER(p_user)))
ORDER by t2.scanning_facility, t1.scandate, t1.scanuser ;
-- Procedure Logic
SET v_LowerBound = CURRENT_DATE - (5 * 365) days; --Current date - 5 years
SET v_UpperBound = CURRENT_DATE + (5 * 365) days; --Current date + 5 years
IF p_user IS NULL or p_user = '' THEN
SET p_user = 'ALL';
END IF;
IF p_startdate IS NULL then
SET p_startdate = v_LowerBound;
end if;
IF p_enddate IS NULL then
SET p_enddate = v_UpperBound;
end if;
-- Open Cursor
OPEN C_1;
END P1;
IP Logged
