Need to make SQL query in stored procedure faster

[shenay921]

Hi All,

I have a stored procedure and am having to use it in a Crystal 7.0 report on Windows XP. The report doesn't like the way the procedure pulls so I had to alter the stored procedure: I took the table that it was pulling from "Trip_Vitals" and created a view "V_CUST_LLA_Trip_Vitals" that pulls everything from a certain date forward. Prior to that date, the field causing the problem (Trip_Vitals.respreg) shows an R instead of a 1 for dates prior to 1/1/07. The R is throwing off my report. But with the new stored procedure based on the view instead of the table it takes 15 minutes to pull the report instead of 20 seconds. Any ideas on what I can do to make this run faster or should I be posting this in a crystal reports forum?

Code:
CREATE PROCEDURE CUST_LLA_TRIP_VITALS AS

SELECT tdate, job, vdate, vtime, pulse, resprate , bps, bpd, palpitation,spo2,etco2,painscale, who_text as crewname, temperature, comments, PTA,
(SELECT descr FROM FDC_Descriptions WHERE type = 18 and code = V_CUST_LLA_Trip_Vitals.respreg) as respreg,
(SELECT descr FROM FDC_Descriptions WHERE type = 18 and code = V_CUST_LLA_Trip_Vitals.pulsereg) as pulsereg,
(SELECT descr FROM FDC_Descriptions WHERE type = 17 and code = V_CUST_LLA_Trip_Vitals.pulsestrength) as pulsestrength,
(SELECT descr FROM FDC_Descriptions WHERE type = 21 and code = V_CUST_LLA_Trip_Vitals.capillaryrefill) as capillaryrefill,
(SELECT descr FROM FDC_Descriptions WHERE type = 19 and code = V_CUST_LLA_Trip_Vitals.respeffort) as respeffort,
(SELECT descr FROM FDC_Descriptions WHERE type = 20 and code = V_CUST_LLA_Trip_Vitals.lungsoundsleft) as lungsoundsleft,
(SELECT descr FROM FDC_Descriptions WHERE type = 20 and code = V_CUST_LLA_Trip_Vitals.lungsoundsright) as lungsoundsright,
(SELECT descr FROM FDC_Descriptions WHERE type = 28 and code = V_CUST_LLA_Trip_Vitals.gcseyes) as gcseyes,
(SELECT descr FROM FDC_Descriptions WHERE type = 30 and code = V_CUST_LLA_Trip_Vitals.gcsmotor) as gcsmotor,
(SELECT descr FROM FDC_Descriptions WHERE type = 29 and code = V_CUST_LLA_Trip_Vitals.gcsverbal) as gcsverbal,
(SELECT descr FROM FDC_Descriptions WHERE type = 16 and code = V_CUST_LLA_Trip_Vitals.ecg) as ecg,
(SELECT descr FROM FDC_Descriptions WHERE type = 6021 and code = V_CUST_LLA_Trip_Vitals.ecgectopics) as ecgectopics, glucose,
(SELECT descr FROM FDC_Descriptions WHERE type = 22 and code = V_CUST_LLA_Trip_Vitals.skintemp) as skintemp,
(SELECT descr FROM FDC_Descriptions WHERE type = 24 and code = V_CUST_LLA_Trip_Vitals.skincolor) as skincolor,
(SELECT descr FROM FDC_Descriptions WHERE type = 23 and code = V_CUST_LLA_Trip_Vitals.skinmoisture) as skinmoisture,
(SELECT descr FROM FDC_Descriptions WHERE type = 27 and code = V_CUST_LLA_Trip_Vitals.pupilsizeleft) as pupilsizeleft,
(SELECT descr FROM FDC_Descriptions WHERE type = 27 and code = V_CUST_LLA_Trip_Vitals.pupilsizeright) as pupilsizeright,
(SELECT descr FROM FDC_Descriptions WHERE type = 26 and code = V_CUST_LLA_Trip_Vitals.pupilreactionleft) as pupilreactionleft,
(SELECT descr FROM FDC_Descriptions WHERE type = 26 and code = V_CUST_LLA_Trip_Vitals.pupilreactionright) as pupilreactionright,
(SELECT descr FROM FDC_Descriptions WHERE type = 25 and code = V_CUST_LLA_Trip_Vitals.pupildilationleft) as pupildilationleft,
(SELECT descr FROM FDC_Descriptions WHERE type = 25 and code = V_CUST_LLA_Trip_Vitals.pupildilationright) as pupildilationright,
(SELECT descr FROM fdc_descriptions WHERE TYPE = 3100 AND code = V_CUST_LLA_Trip_Vitals.loc) as LOC,
(SELECT descr FROM fdc_descriptions WHERE type = 51 AND code = V_CUST_LLA_Trip_Vitals.armmovementleft) as armmovementleft,
(SELECT descr FROM fdc_descriptions WHERE type = 51 AND code = V_CUST_LLA_Trip_Vitals.armmovementright) as armmovementright,
(SELECT descr FROM fdc_descriptions WHERE type = 51 AND code = V_CUST_LLA_Trip_Vitals.legmovementleft) as legmovementleft,
(SELECT descr FROM fdc_descriptions WHERE type = 51 AND code = V_CUST_LLA_Trip_Vitals.legmovementright) as legmovementright,
(SELECT Description FROM FDC_BLOOD_PRESSURE_METHODS WHERE Blood_Pressure_Method_ID = V_CUST_LLA_Trip_Vitals.Blood_Pressure_Method) as bloodpressuremethod,
(SELECT Description FROM FDC_SPO2_OXYGEN_SOURCE_TYPES WHERE SPO2_Oxygen_Source_Type_ID = V_CUST_LLA_Trip_Vitals.SPO2_Oxygen_Source_Type) as spo2oxygensource,
(SELECT Description FROM FDC_ETCO2_COMPENSATION_TYPES WHERE ETCO2_Compensation_Type_ID = V_CUST_LLA_Trip_Vitals.ETCO2_Compensation_Type) as etco2compensation,
V_CUST_LLA_Trip_Vitals.status
from V_CUST_LLA_Trip_Vitals where status=1
GO


Code for the View:

CREATE VIEW dbo.V_CUST_LLA_Trip_Vitals
AS
SELECT *
FROM dbo.Trip_Vitals
where Trip_Vitals.tdate > '2007-01-01'


Thanks for the help!

Laura

 

[markros]

Have you tried testing this SP standalone or from running your report?

If you do SET PROFILE STATISTICS ON before running your SP, what would you see?

 

[shenay921]

Ok, I ran the index and it took the same amount of time. Thanks for your help!

 

[shenay921]

I tried running the SP alone -- took about 5-6 seconds and in the report. Takes about 15 minutes.

 

[markros]

Looks like you may ask this question in Crystal Report forum. Just a SWAG - could the SP somehow be cached on the Crystal side?

Perhaps you should re-try after re-starting SQL Server and your computer.

If this would not help, try to create a new CR (something very very simple, use this SP to create data) and see how long does it take.