More Crystal Report Woes

Question

Post reply

More Crystal Report Woes

sswords

SSCrazy Eights

Points: 8207
More actions
June 9, 2004 at 8:10 am

#83016

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

JFW Mr or Mrs. 500 Points: 546 More actions · Answer 1

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 …

sswords SSCrazy Eights Points: 8207 More actions · Answer 2

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.

sswords SSCrazy Eights Points: 8207 More actions · Answer 3

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.