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

 

[bborissov]

Remove all inner selects and make a CASE statement. Then table you use in these selects:

SELECT ....
       CASE WHEN FDC_Descriptions.Type = 18 AND
                 FDC_Descriptions.Code = V_CUST_LLA_Trip_Vitals.respreg
            THEN FDC_Descriptions.Descr
            ELSE '') AS respreg
....
FROM V_CUST_LLA_Trip_Vitals
[INNER|LEFT] JOIN FDC_Descriptions ON ?????
..

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Looks like the Description table contains Descriptions for each different type of code, that's why we can not convert this query to simple CASE statement.

 

[markros]

If in your view you explicitly specify all columns, would it work?

Also, may be you can do at least some of the descriptions in the view (using JOIN, not subquery, though). Say, 10 Descriptions will go to the view as JOINs and the rest (perhaps as JOINS too) in the main SP.

 

[shenay921]

Hi Markros,

Thanks for the ideas! Why would explicity specifying the columns speed up the processing? I think I can do it but was wondering what that mattered as opposed to using the * symbol.

Thanks!

Laura

 

[markros]

Hi Shenay,

I learned to always specify the columns' list (also in case we don't need every single field). Specifying lists of columns to retrieve will make the result's size smaller. I'm not sure what would be a difference performance wise using * vs. column lists in case of the need of every field - perhaps there still be slight difference that SQL Server will first need to retrieve table's schema.

However, I don't know for sure - would be nice to learn here as well.

 

[markros]

BTW, I guess I meant to say "Hi Laura" (sorry for confusion).

 

[gmmastros]

It is definitely the sub-queries that are killing performance.

Basically, you have one lookup table with many different meanings. So.... you use the lookup table for gcs Eyes, GCS Motor, Pupil Dilation, etc....

If you have a separate lookup table for each thing, and you left joined to it, this query would be extremely fast. Since you don't, you can make it appear as though you do by joining to the lookup table multiple times (once for each value you want to get out). I did the first 3 columns for you here:

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,
       [!]RespReg.Description as respreg,
       Pulsereg.Description as pulsereg,
       PulseStrength.Description 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 
[!]      Left Join FDC_Description As RespReg
        On RespReg.Code = V_CUST_LLA_Trip_Vitals.respreg
        And RespReg.Type = 18
      Left Join FDC_Description As PulseReg
        On PulseReg.Code = V_CUST_LLA_Trip_Vitals.pulsereg
        And PulseReg.Type = 18
      Left Join FDC_Description As pulsestrength
        On pulsestrength.Code = V_CUST_LLA_Trip_Vitals.pulsereg
        And PulseReg.Type = 17[/!]
where status=1

I suspect this will make a huge difference in performance. Also... you should have a good index on the lookup table (fdc_descriptions). Specifically there should be a multi-column index on Code, Type, and Description. If possible, this should be a unique index too (slightly better for performance).

If you decide to take my advice (and I do strongly encourage it), then can you please let us know the time it takes after you are done making all the changes. I am very curious to see how much of a difference this makes.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I would be interested in the test results as well. But I would probably have some of the JOINS as part of the view.

BTW, is there any limitation on the number of JOINs?

 

[gmmastros]

BTW, is there any limitation on the number of JOINs?

In earlier versions of SQL Server, I believe there is a maximum of 256 tables involved in a query. I'd hate to see the query that pushes this limit.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[shenay921]

I am converting the code right now but am confused by the line:

(SELECT descr FROM FDC_Descriptions WHERE type = 6021 and code = V_CUST_LLA_Trip_Vitals.ecgectopics) as ecgectopics, glucose

The additional glucose is throwing me off.

Am I to insert:
ecgectopics.descr as ecgectopics, glucose

and then:

Left Join FDC_Description As ecgectopics
On ecgectopics.code = V_CUST_LLA_Trip_Vitals.ecgectopics
And ecgectopics.Type = 6021

 

[gmmastros]

Ignore the glucose. I mean... still return it, but don't modify the query at all to accommodate it. The glucose value is probably stored directly in the table as a value, whereas other things are stored as lookup values.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[shenay921]

Well, I changed the code and the procedure ran but it is still taking 15 minutes to pull the data into the report. This maybe a crystal issue as it is running as a subreport. Thanks for your ideas.

 

[gmmastros]

Can you show us the code you are using now? (after your modifications)



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[shenay921]

Sure!

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,
RespReg.descr as respreg,
Pulsereg.descr as pulsereg,
PulseStrength.descr as pulsestrength,
Capillaryrefill.descr as capillaryrefill,
respeffort.descr as respeffort,
lungsoundsleft.descr as lungsoundsleft,
lungsoundsright.descr as lungsoundsright,
gcseyes.descr as gcseyes,
gcsmotor.descr as gcsmotor,
gcsverbal.descr as gcsverbal,
ecg.descr as ecg,
ecgectopics.descr as ecgectopics, glucose,
skintemp.descr as skintemp,
skincolor.descr as skincolor,
skinmoisture.descr as skinmoisture,
pupilsizeleft.descr as pupilsizeleft,
pupilsizeright.descr as pupilsizeright,
pupilreactionleft.descr as pupilreactionleft,
pupilreactionright.descr as pupilreactionright,
pupildilationleft.descr as pupildilationleft,
pupildilationright.descr as pupildilationright,
LOC.descr as LOC,
armmovementleft.descr as armmovementleft,
armmovementright.descr as armmovementright,
legmovementleft.descr as legmovementleft,
legmovementright.descr as legmovementright,
bloodpressuremethod.description as bloodpressuremethod,
spo2oxygensource.description as spo2oxygensource,
etco2compensation.description as etco2compensation,
V_CUST_LLA_Trip_Vitals.status

from V_CUST_LLA_Trip_Vitals
Left Join FDC_Descriptions As RespReg
On RespReg.code = V_CUST_LLA_Trip_Vitals.respreg
And RespReg.type = 18
Left Join FDC_Descriptions As PulseReg
On PulseReg.code = V_CUST_LLA_Trip_Vitals.pulsereg
And PulseReg.type = 18
Left Join FDC_Descriptions As Pulsestrength
On pulsestrength.code = V_CUST_LLA_Trip_Vitals.pulsereg
And Pulsestrength.type = 17
Left Join FDC_Descriptions As Capillaryrefill
On Capillaryrefill.code = V_CUST_LLA_Trip_Vitals.capillaryrefill
And Capillaryrefill.type = 21
Left Join FDC_Descriptions As respeffort
On respeffort.code = V_CUST_LLA_Trip_Vitals.respeffort
And respeffort.type = 19
Left Join FDC_Descriptions As lungsoundsleft
On lungsoundsleft.code = V_CUST_LLA_Trip_Vitals.lungsoundsleft
And lungsoundsleft.type = 20
Left Join FDC_Descriptions As lungsoundsright
On lungsoundsright.code = V_CUST_LLA_Trip_Vitals.lungsoundsright
And lungsoundsright.type = 20
Left Join FDC_Descriptions As gcseyes
On gcseyes.code = V_CUST_LLA_Trip_Vitals.gcseyes
And gcseyes.type = 28
Left Join FDC_Descriptions As gcsmotor
On gcsmotor.code = V_CUST_LLA_Trip_Vitals.gcsmotor
And gcsmotor.type = 30
Left Join FDC_Descriptions As gcsverbal
On gcsverbal.code = V_CUST_LLA_Trip_Vitals.gcsverbal
And gcsverbal.type = 29
Left Join FDC_Descriptions As ecg
On ecg.code = V_CUST_LLA_Trip_Vitals.ecg
And ecg.type = 16
Left Join FDC_Descriptions As ecgectopics
On ecgectopics.code = V_CUST_LLA_Trip_Vitals.ecgectopics
And ecgectopics.type = 6021
Left Join FDC_Descriptions As skintemp
On skintemp.code = V_CUST_LLA_Trip_Vitals.skintemp
And skintemp.type = 22
Left Join FDC_Descriptions As skincolor
On skincolor.code = V_CUST_LLA_Trip_Vitals.skincolor
And skincolor.type = 24
Left Join FDC_Descriptions As skinmoisture
On skinmoisture.code = V_CUST_LLA_Trip_Vitals.skinmoisture
And skinmoisture.type = 23
Left Join FDC_Descriptions As pupilsizeleft
On pupilsizeleft.code = V_CUST_LLA_Trip_Vitals.pupilsizeleft
And pupilsizeleft.type = 27
Left Join FDC_Descriptions As pupilsizeright
On pupilsizeright.code = V_CUST_LLA_Trip_Vitals.pupilsizeright
And pupilsizeright.type = 27
Left Join FDC_Descriptions As pupilreactionleft
On pupilreactionleft.code = V_CUST_LLA_Trip_Vitals.pupilreactionleft
And pupilreactionleft.type = 26
Left Join FDC_Descriptions As pupilreactionright
On pupilreactionright.code = V_CUST_LLA_Trip_Vitals.pupilreactionright
And pupilreactionright.type = 26
Left Join FDC_Descriptions As pupildilationleft
On pupildilationleft.code = V_CUST_LLA_Trip_Vitals.pupildilationleft
And pupildilationleft.type = 25
Left Join FDC_Descriptions As pupildilationright
On pupildilationright.code = V_CUST_LLA_Trip_Vitals.pupildilationright
And pupildilationright.type = 25
Left Join FDC_Descriptions As LOC
On LOC.code = V_CUST_LLA_Trip_Vitals.LOC
And LOC.type = 3100
Left Join FDC_Descriptions As armmovementleft
On armmovementleft.code = V_CUST_LLA_Trip_Vitals.armmovementleft
And armmovementleft.type = 51
Left Join FDC_Descriptions As armmovementright
On armmovementright.code = V_CUST_LLA_Trip_Vitals.armmovementright
And armmovementright.type = 51
Left Join FDC_Descriptions As legmovementleft
On legmovementleft.code = V_CUST_LLA_Trip_Vitals.legmovementleft
And legmovementleft.type = 51
Left Join FDC_Descriptions As legmovementright
On legmovementright.code = V_CUST_LLA_Trip_Vitals.legmovementright
And legmovementright.type = 51
Left Join FDC_BLOOD_PRESSURE_METHODS As bloodpressuremethod
On bloodpressuremethod.Blood_Pressure_Method_ID = V_CUST_LLA_Trip_Vitals.Blood_Pressure_Method
Left Join FDC_SPO2_OXYGEN_SOURCE_TYPES As spo2oxygensource
On spo2oxygensource.SPO2_Oxygen_Source_Type_ID = V_CUST_LLA_Trip_Vitals.SPO2_Oxygen_Source_Type
Left Join FDC_ETCO2_COMPENSATION_TYPES As etco2compensation
On etco2compensation.ETCO2_Compensation_Type_ID = V_CUST_LLA_Trip_Vitals.ETCO2_Compensation_Type
where V_CUST_LLA_Trip_Vitals.status=1


GO

 

[gmmastros]

Run this (and post the output here):

sp_helpindex 'FDC_Descriptions'

If you don't have the proper indexes on that table (involved in many joins), then your performance will suffer.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[shenay921]

Index name: PK_FDC_Descriptions
Index description: clustered, unique, primary key located on PRIMARY
INdex Keys: type, code

 

[gmmastros]

try adding this index:

Create Unique Index idx_FDC_Descriptions_Covered On FDC_Descriptions(Code, Type, Descr)

This should be a covering index, so... even though it's not clustered, you will probably get better performance from it.

After creating the index, run this procedure in a query window and let me know how long it takes.

Exec CUST_LLA_TRIP_VITALS

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[shenay921]

First, if I just run the query without any additional indexing it only takes 6 seconds. Something in the report is killing it.

 

[gmmastros]

Oh. I suppose it's a crystal report issue then. Sorry. I haven't used crystal in almost 10 years, so I won't be much help in that regard.

I'm still curious to know if the index I suggested would make a difference. If you want to try it, and it doesn't make a difference, you can simply delete the index with:

Drop Index FDC_Descriptions.idx_FDC_Descriptions_Covered

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom