More Crystal Report Woes
-
Gentlemen,
I need some help again with a Crystal Report that calls several stored procedures. We are attempting to move an application and its databases from Sybase 12.0 to SQL Server 2000. This system uses automated Crystal Reports that call some stored procedures developed long before my time and we've converted about 90 of these with few problems. The procedure below is one of 3 called by this report and I've narrowed the problem down to this one.
This is giving me fits and I can't figure out why. Currently in Sybase, running the same query returns results in about 30 seconds. When I run it in SQL Server, it never stops and when I finally cancel it, the same record is returned over and over for thousands of rows. The only difference between the Sybase and SQL Server queries is that I removed some convert statements from the where clause starting at the (appt_entered_datetime...) because SQL Server was yelling about this when I moved the procedure. I placed an underscore below to help identify it quickly. Other than this change, the procedures are identical. Can anyone point me in the right direction? Any help is appreciated. Procedure is as follows:
CREATE proc proc_autoadm1
@sched_id varchar(12), @start_date datetime, @end_date datetime
as
SELECT
appt.appt_id appt_appt_id, appt.entered_datetime appt_entered_datetime, visitapptlist.visit_id, schedlog.schedlog_id schedlog_schedlog_id, schedlog.start_datetime schedlog_start_datetime, apptstatus.abbr apptstatus_abbr, res.name res_name, visit.pat_acct_num, visit.guarantor_last, visit.guarantor_first, probooking.name probooking_name, probooking.start_datetime probooking_start_datetime, probooking.duration, audittype.audittype_id, audittype.abbr audittype_abbr, pat.pat_id, pat.gender, pat.birthdate, pat.ssn, pat.mpi, pat.address, pat.city, pat.state, pat.zipcode, pat.home_phone, pat.work_phone, pat.name_display, facility.abbr facility_abbr, facility.adtsys_id, loc.name loc_name, patmrnlist.adtsys_id, patmrnlist.mrn, apptaud.old_start_datetime apptaud_old_start_datetime, probooking.pro_id,
resunit.abbr resunit_abbr, schedidadm1.*
FROM
prod.dbo.appt appt,
prod.dbo.apptaud apptaud,
prod.dbo.resunit resunit,
prod.dbo.patbooking patbooking,
prod.dbo.visitapptlist visitapptlist,
prod.dbo.schedlog schedlog,
prod.dbo.apptstatus apptstatus,
prod.dbo.res res,
prod.dbo.visit visit,
prod.dbo.probooking probooking,
prod.dbo.audittype audittype,
prod.dbo.pat pat,
prod.dbo.facility facility,
prod.dbo.loc loc,
prod.dbo.patmrnlist patmrnlist,
tempdb.dbo.schedidadm1 schedidadm1
WHERE
appt.resunit_id = resunit.resunit_id AND
appt.appt_id = patbooking.appt_id AND
appt.appt_id = visitapptlist.appt_id AND
appt.appt_id = schedlog.appt_id AND
appt.appt_id *= apptaud.appt_id AND
appt.apptstatus_id = apptstatus.apptstatus_id AND
appt.appt_id = schedidadm1.appt_id AND
patbooking.ordering_phys_id *= res.res_id AND
visitapptlist.visit_id = visit.visit_id AND
patbooking.appt_id = probooking.appt_id AND
schedlog.audittype_id = audittype.audittype_id AND
patbooking.pat_id = pat.pat_id AND
resunit.facility_id = facility.facility_id AND
facility.facility_id = loc.loc_id AND
pat.pat_id = patmrnlist.pat_id AND
pat.pat_id > 0 AND
(appt.entered_datetime between @start_date and @end_date AND
audittype.audittype_id <9) or
(schedlog.start_datetime between @start_date and @end_date AND
audittype.audittype_id <9) AND
pat.name_display >' ' AND
pat.name_display not like ('TEST%') AND
apptaud.old_start_datetime > ' ' AND
schedidadm1.user_name like @sched_id +'%'
RETURN
GO
My hovercraft is full of eels.
-
Your OR statement is the most suspicious. Try putting parentheses around the entire OR associated clauses like this:
… pat.pat_id > 0 AND
(
(appt.entered_datetime between @start_date and @end_date AND
audittype.audittype_id <9) or
(schedlog.start_datetime between @start_date and @end_date AND
audittype.audittype_id <9)
)
AND …
-
JFW - That worked perfectly. Query now returns results in 3 seconds. This for me was a perfect case of not seeing the forest for the trees. If you're ever in Ft. Lauderdale, I owe you a beer! Thanks.
My hovercraft is full of eels.
-
And BTW...I sincerely apologize for the way I opened my first post. I fully recognize that there are a lot of great resources of both sexes out there and if I offended anyone, please be assured that this was never my intent. That's what I get for doing 8 things at once.
Thanks for not flaming me, and thanks for the always expert and professional help.
My hovercraft is full of eels.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply